Problem restoring a SQL 2005 database from a backup device

Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/ur
l]
John
"wolf.emissar@.gmail.com" wrote:

> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>I am trying to restore a database from a backup device and when I try
to add the backup device in the restore database wizard I receive the
following error message:
An exception occurred while executing a transact-sql statement or
batch.
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.
The backup device was created using SQL Server 2005 and I am trying to
restore the database to an Express version of 2005.
I can restore the database successfully if I restore it to the same
server that I created the backup device on.
For a little background, I am trying to backup a database on a server
to a laptop for business continuity purposes. The database is under 1
gig in total size.
Any insight is greatly appreciated.|||Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/ur
l]
John
"wolf.emissar@.gmail.com" wrote:

> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:[vbcol=seagreen]
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/
url]
> John
> "wolf.emissar@.gmail.com" wrote:
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:[vbcol=seagreen]
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/
url]
> John
> "wolf.emissar@.gmail.com" wrote:
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hi
Both posters were trying to restore a SQL 2005 database onto a SQL 2000
instance and they have the error message you get. Are you sure that there ar
e
no SQL 2000 instances on the machine you have? Database restores are
compatible between all versions of SQL 2005 therefore you should not have an
y
problems. Is this a beta or CTP version?
John
"wolf.emissar@.gmail.com" wrote:

> John, thanks for the response. I am trying to restore to a 2005
> instance but it is an Express version, I don't know if this is the
> source of the problem or not.
> In the second link you attached Tibor mentions logging into a 2000
> instance. This sounds very much like the error I am receiving, do you
> know what he is referring to? Can express 2005 be configured to run as
> a 2000 instance?
> John Bell wrote:
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Steen, here is the SQL that I am running for the restore:
restore database wolf_sonar
from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
and I recieve the following error message:
Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are
allowed.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
One thing that I did notice that is I don't have a compatibility level
of 90 on the local server on the laptop that the wolf_sonar database
resides on. I only have 70 and 80 as available choices.
John to answer your question, I do not have any 2000 instances on the
machine that I am trying to restore the database to. This is a fairly
new machine and I actually just installed 2005 Express on this box a
couple of days. As a further chack I only see sql server 2005 in add /
remove programs.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> Could you try to post the sql statement you are running?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||John, I have a tried a few different version of 2005 express. The
latest one that I have installed is Version 9.00.2047.00. I do not
believe that this is a beta version.
John Bell wrote:[vbcol=seagreen]
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there
are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have
any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
>

Problem referencing a column in a Table Variable in a query.

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @.THeader" . @.THeader is the name of the variable table and the error occurs with such references as @.THeader.ApplyAmt, @.THeader.TransactionHeaderID, etc.

declare @.THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @.THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @.THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

Update @.THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @.THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @.THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @.THeader.ApplyAmt
FROM @.THeader, mtblTransactionHeader
WHERE @.THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table

Thanks in advance

smHaig

Try adding an alias to @.THeader

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = t.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = t.ApplyAmt
FROM @.THeader t, mtblTransactionHeader
WHERE t.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID -- put the values back into original table

Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu

Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu

Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu