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.
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 databaseI'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 errorMsg 1834, Level 16, State 1, Line 1The 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 1File '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 1The 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 1File '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 1Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Line 1RESTORE 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 databaseAny 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. |
 |
|
|
|
|
|
|