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

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?

thanks,
Mark
LVL 1
rascalAsked:
Who is Participating?
 
SQL_SERVER_DBACommented:
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!
0
 
ptjcbCommented:
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?

From
http://msdn2.microsoft.com/en-us/library/ms345598.aspx

#

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.
0
 
SQL_SERVER_DBACommented:
ptjcb,
read more carefully...ACE.
0
 
rascalAuthor Commented:
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.

Thanks,
Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.