can't restore db - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

can't restore db

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-04-25 : 17:03:33

I am trying to restore a db to my local install of sql server 2005. When doing so I get this message:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'SAM-WKHNCD94333'. (Microsoft.SqlServer.Smo)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: File 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\si_training_db.mdf' is claimed by 'si_production_db_pri_2'(4) and 'si_production_db_Data'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

I tried changing the MOVE portion of the script to a new filename, but it didn't work:

(original)
MOVE N'si_production_db_Data' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\si_training_db.mdf'

(changed)
MOVE N'si_production_db_Data' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\si_training_dbNEW.mdf'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-25 : 17:40:35

Can you change location to different drives? It is not good idea to restore in C:

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-04-25 : 18:41:41

It's just on my local workstation for development purposes. I only have the one drive.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-25 : 18:53:35

Post the output of RESTORE FILELISTONLY, RESTORE HEADERONLY, and filenames of all files in 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 05:49:28

i think you forgot to move the logfile as well or there is another data file to be moved...

otherwise the database is existing and you just need to include the REPLACE option as well

HTH

--------------------
keeping it simple...

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2008-05-14 : 03:35:29

I'm having the same problem but only if I try to restore a SQL2000 database to a SQL20005 server.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-14 : 22:53:50

Above responses are still valid.

BeeMan
Starting Member

1 Post

Posted - 2008-10-22 : 11:39:03

You should check the file name suffixes in the grid for 'Restore To'. If the file number x.mdf is missing you may want to manually insert the number before attempting to restore.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources