SQL Server 2005 Restore fails to restore from the SQL Server 2000 backup file

Question

I installed SQL Server 2005 Dev. edition on windows 2003 server which is running on vmware.

And I tried to restore the SQL Server 2000 database backup file using Restore Files and FileGroups... in the SQL Server Management studio. It throws the following error message

The  file or filegroup "XXDB_log" cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

I went to the Options page and changed the paths in the Restore As column, still I get the same error.

 

When I executed the RESTORE DATABASE with MOVE command the database is restored successfully but I am not successful with the Management Studio.

Please could someone help me on this one

 

Answer 1

Try choosing "Restore Database" instead of "restore files and filegroups".  If this is an existing database, it will have been converted to SQL2005 format, and you can't apply SQL 2000 backups to it.  However, if you create a new database with the restore (restore database or RESTORE DATABASE WITH MOVE), you won't have that issue. The restored database will be converted as the last step of recovery as it is brought online.

Answer 2

thanks for your information. I restored the database successfully.

Answer 3

This solved my problem:

I scripted the restore operation and got the following:

RESTORE DATABASE [Michelle]
FILE = N'Midata', FILE = N'Milog'
FROM DISK = N'B:\BackupProductionForUpgrade\Michelle_db.Bkp'
WITH FILE = 1,
MOVE N'Midata' TO N'I:\MSSQLDATA\Michelle\Michelle.MDF',
MOVE N'Milog' TO N't:\MSSQLLOG\Michelle\Michelle.LDF',
NOUNLOAD, STATS = 10
GO

This fails! I erased the second line and it worked:

RESTORE DATABASE [Michelle]
--FILE = N'Midata', FILE = N'Milog'
FROM DISK = N'B:\BackupProductionForUpgrade\Michelle_db.Bkp'
WITH FILE = 1,
MOVE N'Midata' TO N'I:\MSSQLDATA\Michelle\Michelle.MDF',
MOVE N'Milog' TO N't:\MSSQLLOG\Michelle\Michelle.LDF',
NOUNLOAD, STATS = 10
GO

Answer 4

I created a new database with the appropriate name, then checked the "Overwrite the existing database" (on the Options page), this seemed to work too.

Answer 5

 

I installed SQL Server 2005 Dev. edition on windows XP SP2.

 

I backed up the database by using file or filegroup option, when I tried to restore the  database backup file using Restore Files and FileGroups... in the SQL Server Management studio. It throws the following error message

The  file or filegroup "XXDB_log" cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

 

And I tried to use T-SQL to restore, also failed.

 

Anybody can help?

Answer 6

OMG!  Could this process be any more of a pain???  I've spent the entire morning trying to get this worked out, and all it took was commenting out the two lines

 

MS, you really need to work on this to make things a little easier for us developers trying to develop while not necessarily being a DBA...

Answer 7

 

Would you care to share what those lines are?

 

AK.

Answer 8

We also have had a problem with MS' stated procedure for restoring database backups from a SQL 2000 machine to SQL 2005.  They state to select any existing database, then go to database restore and simply type a name for the new database you wish to restore into.  All well and good so far!  The problem then arises when you try and restore either using 'file' or 'backup device' - you get a failure with the (not) helpful "System error 21 (unknown error)".

 

To fix the problem was easy (when we eventually figured it out!) ... Create an empty database with the required name, then select THIS database and the option to overwrite it - works every time!

 

Enjoy.

Paul.

Answer 9

 

ditto!  this worked for me as well.

 

thanks

Answer 10

Thank you very much !! You'r answer is very good.

Answer 11

I had similar problem and was able to resolve successfully after seeing this post. thanks a lot

Answer 12

Thanks so much. I didn't have this problem the first 3 or 4 times I did this. Creating the empty database and then overwriting it worked.

 

Answer 13

 

Yes, Dave Streeter's approach works just fine - thanks.
To spell it out:
- rt-clk on Databases->New Database (any name will do)

- rt-clk on Databases->Restore Database,  select your new database

- From Device->File->Add ...and choose the 2000 backup file

- Check 'Restore' for that backup set

- Check 'Options->Overwrite the existing database'

- OK OK

 

Answer 14

Thanks.  Dave Streeter's approach worked.

 

I did a little bit different.  I did not create a new database from the beginning.
Just right-click Databases object and select 'Restore Database...  Then type
a new database name on 'To database'.    After Management Studio finishes
restoring, the new database is shown under Databases.

I have not completely examine the new database created yet; but I know, at least,
I need to re-create my SQL Server Agent jobs.

Answer 15

Thanks Dave

 

It also works fine for me.

I have successfully restored my old databases.

Answer 16

it is possible to restore database from sql server 7, sql server 2000 to sql server 2005.

using script without any External tools.

it can be achieved by using restore and with replace command

use master

restore database mydatabase from disk ='c:\mybackup.bak' with replace..

Kindly check out...

http://dynamic-coding.blogspot.com/search/label/SQL%20SERVER

Thanks


 

Answer 17

Thanks a lot this is was my problem.

Answer 18

Hi This is a right solution.. =), thanks

Answer 19

Hi

Use 2011 Best Tool easily Restore SQL Server all version supported  : http://www.fixsqlserver.com

download exe :- http://www.systoolsdl.com/get.php?id=4

Thanks & Regards 
vikki Johnson From USA 

Answer 20

thanks a lot for giving step by step process....i was able to restore the database successfully.

 

Answer 21

Try choosing "Restore Database" instead of "restore files and filegroups".  If this is an existing database, it will have been converted to SQL2005 format, and you can't apply SQL 2000 backups to it.  However, if you create a new database with the restore (restore database or RESTORE DATABASE WITH MOVE), you won't have that issue. The restored database will be converted as the last step of recovery as it is brought online.

I had the same error. I had been doing restores for long. Dont know whats happening today. How Silly!!