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
 General SQL Server Forums
 New to SQL Server Administration
 Restore from DB to another Test DB

Author  Topic 

NeilG
Aged Yak Warrior

530 Posts

Posted - 2011-01-26 : 06:42:46
Hi, I'm trying to create an automated job which restore a back from one db to another to verfiy the backup

for example TestOne database is restore over TestTwo database

I've create the proc to gather all the require information but when I run the following command

RESTORE DATABASE TestTwo FROM DISK = 'C:\SQLBackups\TestOne.bak' WITH REPLACE - which I though over wrote the database I get the following error

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestOne.mdf' cannot be overwritten. It is being used by database 'TestOne'.
Msg 3156, Level 16, State 4, Line 1
File 'TestOne' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestOne.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestOne_log.ldf' cannot be overwritten. It is being used by database 'TestOne'.
Msg 3156, Level 16, State 4, Line 1
File 'TestOne_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestOne_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

But I'm not trying to move the database or overwrite TestOne database just trying to overwrite one database with the a backup from another database


Any advice.

-----------------------------------------------
Learning something new on SQL Server everyday.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-26 : 07:05:01
Have a look at your source database now.
In object explorer (in SSMS) right click the TestOne and choose Files.
There you will see something like this:
A logical filename TestOne pointing to the real file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestOne.mdf'
A logical filename TestOne_Log pointing to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestOne_log.ldf'

This information will also reside in the BAK-file that you have created.
Now you are trying to restore on the same server...
The restore reads the BAK-File and wants to restore with another database name BUT NOT with other filenames.

Hence you have to use the WITH MOVE when restoring.

Look in BOL for further information.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

- Advertisement -