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

Posted on 2007-10-04
Medium Priority
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:
System.data.SqlClient.SqlError: 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
  • 2
LVL 16

Accepted Solution

SQL_SERVER_DBA earned 1000 total points
ID: 20017360
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

ID: 20017548
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

ID: 20017619
read more carefully...ACE.

Author Comment

ID: 20017856
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.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 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