Error restoring SQL 2000 db to SQL 2008 - The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

Question

Hi,

While trying to restore a SQL 2000 db into SQL 2008, I get this error:

------------------------------

Restore failed for Server 'S2B22347'.  (Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.mdf' is claimed by 'CCHRI_UAT_Tran'(3) and 'CCHRI_UAT_Data'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

------------------------------

The script looks something like this:

RESTORE

 

DATABASE [Build51_Testing_db] FROM DISK = N'C:\Shival\Build51_Testing_db_bkp' WITH FILE = 1, MOVE N'CCHRI_UAT_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.mdf', MOVE N'CCHRI_UAT_Tran' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.mdf', MOVE N'CCHRI_UAT_Index' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.mdf', MOVE N'CCHRI_UAT_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.LDF', MOVE N'CCHRI_UAT_Log1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.LDF', NOUNLOAD, REPLACE, STATS = 10

GO

Please help me resolve this.

Shival

Answer 1

I was able to work around it by giving a different name to each MDF and LDF. But Why do i need to do this? What is the need for this? Also, why would SQL Server not do it by itself?

MOVE N'CCHRI_UAT_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.mdf',

MOVE N'CCHRI_UAT_Tran' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db1.mdf',

MOVE N'CCHRI_UAT_Index' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db2.mdf',

MOVE N'CCHRI_UAT_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db.LDF',

MOVE N'CCHRI_UAT_Log1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db1.LDF'

Shival

Answer 2

Hello,

If you are looking for possible causes of this behavior, please see the following:

http://msdn.microsoft.com/en-us/library/bb326318(SQL.105).aspx

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/dacc12e0-746f-40b6-865a-cb753afac01b/

https://connect.microsoft.com/SQLServer/feedback/details/530983/incorrect-file-names-when-restoring-from-sql-server-2005-backup-to-sql-server-2008?wa=wsignin1.0

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 3

I was able to work around it by giving a different name to each MDF and LDF. But Why do i need to do this? What is the need for this? Also, why would SQL Server not do it by itself?

Thanks for this, it works. I also wonder why MSQLMS doesn't do this itself, it's such bad design.