Error during restore operation

Hi all,
I installed SQL server 2000 on my machine first.
Then i just installed SQL server 2005 tools on above it to get
management studio access.
Now i am trying to restore a database on my machine from backup which
i took from server running SQL server 2005.
i am getting the error-
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
Server, Error:3205)
Does anybody know how do i go about to resolve it.
Thanks a lot.
I did some Google which suggests you get this error if you try to restore a 2005 backup to 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<nitinluthra@.gmail.com> wrote in message
news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
> Hi all,
> I installed SQL server 2000 on my machine first.
> Then i just installed SQL server 2005 tools on above it to get
> management studio access.
> Now i am trying to restore a database on my machine from backup which
> i took from server running SQL server 2005.
> i am getting the error-
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
> Server, Error:3205)
> Does anybody know how do i go about to resolve it.
> Thanks a lot.
|||I reproduced the error, that's exactly an error message you get when you try
to restore a backup that is taken from a SQL Server 2005 instance.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzMom43nIHA.3652@.TK2MSFTNGP03.phx.gbl...
>I did some Google which suggests you get this error if you try to restore a
>2005 backup to 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <nitinluthra@.gmail.com> wrote in message
> news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
>
sql

Error during restore operation

Hi all,
I installed SQL server 2000 on my machine first.
Then i just installed SQL server 2005 tools on above it to get
management studio access.
Now i am trying to restore a database on my machine from backup which
i took from server running SQL server 2005.
i am getting the error-
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
Server, Error:3205)
Does anybody know how do i go about to resolve it.
Thanks a lot.I did some Google which suggests you get this error if you try to restore a 2005 backup to 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<nitinluthra@.gmail.com> wrote in message
news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
> Hi all,
> I installed SQL server 2000 on my machine first.
> Then i just installed SQL server 2005 tools on above it to get
> management studio access.
> Now i am trying to restore a database on my machine from backup which
> i took from server running SQL server 2005.
> i am getting the error-
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
> Server, Error:3205)
> Does anybody know how do i go about to resolve it.
> Thanks a lot.|||I reproduced the error, that's exactly an error message you get when you try
to restore a backup that is taken from a SQL Server 2005 instance.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzMom43nIHA.3652@.TK2MSFTNGP03.phx.gbl...
>I did some Google which suggests you get this error if you try to restore a
>2005 backup to 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <nitinluthra@.gmail.com> wrote in message
> news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
>> Hi all,
>> I installed SQL server 2000 on my machine first.
>> Then i just installed SQL server 2005 tools on above it to get
>> management studio access.
>> Now i am trying to restore a database on my machine from backup which
>> i took from server running SQL server 2005.
>> i am getting the error-
>> Too many backup devices specified for backup or restore; only 64 are
>> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
>> Server, Error:3205)
>> Does anybody know how do i go about to resolve it.
>> Thanks a lot.
>

Error during database restore

Hi,

I'm trying to restore a database backup but I get this error. What does it mean exactly?

Thanks!
TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'WHIDBEY1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1314&EvtSrc=MSSQLServer&EvtID=3132&LinkId=20476

BUTTONS:

OK

what this usually means is that you backed up your database to two different files, but are only supplying one file during your restore. Is this the case? Did you create a database backup to multiple files? If so, you need all of them to do the restore.

|||I have backed up the database to a file in one server and trying to restore the backup file into another database in another server. While restoring i am getting the same error. Can you tell me what could be the issue.

Error during database restore

Hi,

I'm trying to restore a database backup but I get this error. What does it mean exactly?

Thanks!
TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'WHIDBEY1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1314&EvtSrc=MSSQLServer&EvtID=3132&LinkId=20476

BUTTONS:

OK

what this usually means is that you backed up your database to two different files, but are only supplying one file during your restore. Is this the case? Did you create a database backup to multiple files? If so, you need all of them to do the restore.

|||I have backed up the database to a file in one server and trying to restore the backup file into another database in another server. While restoring i am getting the same error. Can you tell me what could be the issue.

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli
sql

Error detaching sql express database using SMO

I am using SMO to provide a backup/restore feature. The restore code looks like this:

try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;

// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);

break;
}
}

// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));

// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}

This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.

Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.

Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.

How can I avoid/ workaround this to ensure my restore functionality is full proof.

Thanks,

Nick

Hello

I had exactly the same problem. It took some time to figure out but here is the solution:

You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.

The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.

I hope this helps you.

|||

Hi,

I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.

Nick

|||

You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:

server.KillAllProcesses(db.Name);

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;

db.Alter(TerminationClause.RollbackTransactionsImmediately);

server.DetachDatabase(db.Name, true);

Hope this helps,
Steve

|||Hi,

where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.

MusiMeli

Error attempting restore of full/differential backup

I am running the following script to attempt a restore of a differential backup:

RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'
WITH
NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH RECOVERY
GO

I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:

Msg 3136, Level 16, State 1, Line 1

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")

The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'

The script for the differential backup is:

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT

All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. (Sorry, I'm primarily a programmer, not a DBA) Can anyone help?

Thanks

Are you running SQL 2000 or 2005, because you can't restore AdventureWorks on SQL 2000?|||I am running SQL 2005 -- sorry, forgot to mention.|||I discovered the problem. In my differential backup

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT

I should not be using "INIT".

I thought that since you just use the last differential backup file, it would make sense to overwrite it every time, so I put in the INIT. However, it doesn't work. You have to have all the differential files saved, even though you only use the last one.

It doesn't make sense to me, but it appears to be the answer, because when I do it that way the restore works.