asked on

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

ASKER CERTIFIED SOLUTION

SQL_SERVER_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ptjcb

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.

SQL_SERVER_DBA

ptjcb,
read more carefully...ACE.

rascal

ASKER

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