none
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

     

    Thursday, October 05, 2006 11:05 PM

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.
    Friday, October 06, 2006 8:55 PM
    Moderator

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.
    Friday, October 06, 2006 8:55 PM
    Moderator
  • thanks for your information. I restored the database successfully.
    Friday, October 06, 2006 9:47 PM
  • 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

    Wednesday, December 13, 2006 5:45 PM
  • 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 by Alex Schatten Sunday, January 24, 2010 10:46 PM
    Thursday, March 22, 2007 11:39 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?

    Wednesday, May 02, 2007 10:27 AM
  • 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...
    Thursday, August 02, 2007 8:39 PM
  •  

    Would you care to share what those lines are?

     

    AK.

    Wednesday, August 22, 2007 9:30 PM
  • 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.

    Thursday, September 27, 2007 3:18 PM
  •  

    ditto!  this worked for me as well.

     

    thanks

    Friday, October 12, 2007 1:09 PM
  • Thank you very much !! You'r answer is very good.

    Friday, December 14, 2007 4:08 PM
  • I had similar problem and was able to resolve successfully after seeing this post. thanks a lot
    Monday, February 04, 2008 12:10 PM
  • 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.

     

    Tuesday, February 26, 2008 12:32 PM
  •  

    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

     

    Thursday, May 01, 2008 10:58 AM
  • 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.

    Thursday, June 05, 2008 1:12 PM
  • Thanks Dave

     

    It also works fine for me.

    I have successfully restored my old databases.

    Friday, August 01, 2008 10:00 AM
  • 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


     
    Thursday, July 23, 2009 2:33 PM
  • Thanks a lot this is was my problem.
    Sunday, February 07, 2010 8:36 PM
  • Hi This is a right solution.. =), thanks
    Friday, May 28, 2010 12:32 PM

  • 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 

    Monday, May 02, 2011 6:28 AM
  • thanks a lot for giving step by step process....i was able to restore the database successfully.

     

    Tuesday, November 15, 2011 9:42 PM
  • 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!!

    Tuesday, January 31, 2012 10:02 AM