SQL Server 2005 Restore fails to restore from the SQL Server 2000 backup file
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
Answers
- 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.
All Replies
- 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.
- thanks for your information. I restored the database successfully.
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
GOThis 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- 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.
- Proposed As Answer byAlex Schatten Sunday, January 24, 2010 10:46 PM
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?
- 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... Would you care to share what those lines are?
AK.
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.
ditto! this worked for me as well.
thanks
Thank you very much !! You'r answer is very good.
- I had similar problem and was able to resolve successfully after seeing this post. thanks a lot
- 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.
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
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.Thanks Dave
It also works fine for me.
I have successfully restored my old databases.
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
- Thanks a lot this is was my problem.
