How to restore a SQL 2000 database into a SQL 2005 instance?

Posted on 2007-10-04
Last Modified: 2008-02-21
Is it possible to restore a SQL Server 2000 database backup into a SQL Server 2005 instance, thereby creating the database on the 2005 instance and having it load from the contents of the 2000 backup?

I am trying this but get the error mesage:

Restore failed for server MYSERVER
Additional Information: The operating system returned the error '32 (The process cannot access the file because it is being used by another process)' while attempting RestoreContainer::ValidateTargetForCreation on 'C:\Program Files\Microsoft SQL Server\MSSQL$mirACLECAT\data\unituscom.mdf'

The SQL 2000 database I am attempting to the 2005 instance is called unituscom and I did not pre-define it on the SQL 2005 instance, expecting that SQL 2005 would automatically do this during the restore. (I did try pre-defining it, but when I tried the restore, it told me that the backup was not from that database).

Is this even possible?

Question by:rascal
    LVL 16

    Accepted Solution

    I believe it is because the sql 2005 doesn't support the sql 2000 backup file, Im not sure! So i tried attached the database from sql server 2000 into sql server 2005 then it can already.After that i hv tried take backup from sql server 2005 itself and then restore it back ,it is can be restored successfully!
    LVL 27

    Expert Comment

    Yes, you can use a 2000 backup and restore it on a 2005 server.

    Did you set the database to single user before you did the restore?



    In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine, and then expand that instance.

    Right-click the database to change, and then click Properties.

    In the Database Properties dialog box, click the Options page.

    From the Restrict Access option, select Single.

    If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.
    LVL 16

    Expert Comment

    read more carefully...ACE.
    LVL 1

    Author Comment

    Thanks SQL_SERVER_DBA, that did it.

    I performed the following steps:

    1) detached the unituscom database from the SQL 2000 database (and just for good measure I stopped the SQL 2000 server service since it is on the same machine)

    2) from within SQL 2005, attached the unituscom database mdf file from its SQL 2000 Data directory.

    3) From there the database was usable, I may still need to work through the problem of only having a SQL 2000 .bak database backup file to use when restoring into the SQL 2005, but for now that solves my problem.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now