Restore from DB to another Test DB

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources