CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
>>Development (Web and Desktop) >> Database >> Microsoft SQL Server Management Studio

The backup set holds a backup of a database other than the existing

Article ID: 128 - Date Created Friday, April 01, 2011 - This Article Has been Viewed 13975 times.
Article was last updated on: Wednesday, April 22, 2015 - Written By:

The backup set holds a backup of a database other than the existing

CFF Knowledge Base - Share on MySpace CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
Share With Friends (Updated 6-8-2010)
Restoring your database that was located on another sql server to your existing sql server is not as difficult as one might think. Import .bak file to a database in SQL server

Details
When you backup your SQL Server database that is at another location and you need to transfer that data to the new location to restore it into its new sql server.
Using the SQL Server Management Studio to do a regular Restore will result int he following error.

 
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'your-server\InstanceName2005'. (Microsoft.SqlServer.Smo)
For help, click: 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
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'mydb' database. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------


Recreate Issue
To recreate this issue.

Open SQL Server Management Studio (2005 in this case)
From the [Object Explorer] expand the server instance.
Right Click on the [Databases] name
Choose [Restore Database]

In the [Restore Database] Dialog window.
Choose the [To database:] from the dropdown selection menu
Choose [From device:] click the ellipse button to browse to the location where you stored your .bak database.
Check the [ Restore ] option and click [OK]

This will result in the mentioned error.


Resolve Issue
To resolve this issue:

Open SQL Server Management Studio (2005 in this case)
From the [Object Explorer] expand the server instance.
Right Click on the [Databases] name
Choose [Tasks]
Choose [Restore]
Choose [Database]

In the [Restore Database] Dialog window.
Choose the [To database:] from the dropdown selection menu
Choose [From device:] click the ellipse button to open the [Specify Backup] dialog box.
Click [Add] to browse to the location where you stored your .bak database.
Once you have the .bak file loaded, put a [Check] in the [Restore] box beside its name.

Code #1
Now, from the top of the [Restore Database] dialog window, click on the [Script] button.

This will then open the main SQL Server Management Studio windows with a lot of script inside of a Query Tab.
What you want to do here is simply edit it to look like the following.


 
RESTORE DATABASE MyDB
FROM DISK = 'C:\MyDB.bak'
WITH REPLACE


Once you have the code looking exactly like the above (With your database names replacing [MyDB])
Click the [Execute] button to load the database.

If you receive the error on of the following errors, please follow the detailed instructions to resolve the issue:

Scenario #1:
 
Msg 5133, Level 16, State 1, Line 1
Stating that it cannot find the path specified, then perform the following actions.
Example
If the path in the Error is:
C:\MSSQL.1\MSSQL\DATA\MyDB.mdf
Then go into your C:\ drive, and create the folders exactly the way they show in the error, [C:\MSSQL.1\MSSQL\DATA\] once you have created the folders, [Execute] the command again, and this "should" restore the database to your current database, and add the database files into the folder that you just created.

Scenario #2:
If you receive this error:
 
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


Go into:
[Services]
Choose [SQL Server (SeverName)]
Right Click and [Restart]

Once the SQL Server is Restarted, go into [SQL Server Management Studio]
Right Click on the Database Name, and choose [Delete]
When the Dialog opens, choose to [Delete] the database.
Once the database is deleted.
Right Click on the main folder [Database]
Choose [Restore Database]
When the [Restore Database] dialog opens.
In the [To Database]: type in the database name.
Choose [From device:] click the ellipse button to open the [Specify Backup] dialog box.
Click [Add] to browse to the location where you stored your .bak database.
Once you have the .bak file loaded, put a [Check] in the [Restore] box beside its name.
(Follow Code #1 above to complete this database restore)


If all works out without error, you will receive the following message.

 
Processed 240 pages for database 'MyDB', file 'MyDB' on file 1.
Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
RESTORE DATABASE successfully processed 243 pages in 0.659 seconds (3.017 MB/sec).