StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSql Server 2005 Restore Failing
text
Body
copied!<p>Running sql server 2005 I have database A. I am trying to restore from a backup of A to database B. I want to retain the database A and create a new testing database B from a previous set of data.</p> <p>I tried to create B and restore from the .bak AND restore database to B from management studio.</p> <p>The error is...</p> <blockquote> <p>TITLE: Microsoft SQL Server Management</p> <h2>Studio</h2> <p>Restore failed for Server '195448-APP2'. (Microsoft.SqlServer.Smo)</p> <p>For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476" rel="nofollow noreferrer">http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476</a></p> <p>------------------------------ ADDITIONAL INFORMATION:</p> <p>System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'B' database. (Microsoft.SqlServer.Smo)</p> <p>For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476" rel="nofollow noreferrer">http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476</a></p> <p>------------------------------ BUTTONS:</p> <h2>OK</h2> </blockquote> <p>I found this snippet which I am hesitant to use and want to ask if it would solve my problem of changing the location of the mdf and ldf during the process of restoring the database or does it replace database A's items altogether.</p> <pre><code>ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE AdventureWorks FROM DISK = 'C\:BackupAdventureworks.bak' WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf', MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf', REPLACE </code></pre> <p>[<a href="http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/][1]" rel="nofollow noreferrer">http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/][1]</a></p> <p>and for me I would make it...</p> <pre><code>RESTORE DATABASE B FROM DISK = 'C:\backupofA.bak' WITH MOVE 'B' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B.mdf', MOVE 'B_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B_log.ldf', REPLACE </code></pre> <p>What I don't know is if it will affect database A at all. I am hoping the replace refers files associated with B.</p> <p>or if it should be</p> <pre><code>RESTORE DATABASE B FROM DISK = 'C:\backupofA.bak' WITH MOVE 'A' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B.mdf', MOVE 'A_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B_log.ldf', REPLACE </code></pre> <p>If anyone could help me with the error and/or confirm this fix I would be very grateful as it is not my database I'm playing with.</p> <p>Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload