KB Issue
Restoring your Database that was located on another SQL Server to your existing SQL Server is not as difficult as one might think. Import the .bak file into a SQL Server database.
Issue Details
When you back up 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 in the 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
Open SQL Server Management Studio (2005 in this case)
  1. From the [Object Explorer] expand the server instance.
  2. Right Click on the [Databases] name
  3. Choose [Restore Database]
  4. In the [Restore Database] Dialog window.
  5. Choose the [To database:]from the dropdown selection menu
  6. Choose [From device:]click the ellipse button to browse to the location where you stored your .bak database.
  7. Check the [Restore] option and click [OK]
This will result in the mentioned Error.
Resolve Issue
  1. Open SQL Server Management Studio (2005 in this case)
  2. From the [Object Explorer] expand the server instance.
  3. Right Click on the [Databases] name
  4. Choose [Tasks]
  5. Choose [Restore]
  6. Choose [Database]
  7. In the [Restore Database] Dialog window.
  8. Choose the [To database:] from the dropdown selection menu
  9. Choose [From device:], click the ellipse button to open the [Specify Backup] dialog box.
  10. Click [Add] to browse to the location where you stored your .bak database.
  11. Once you have the .bak file loaded, put a [Check] in the [Restore] box beside its name.
  12. Code #1
  13. Now, from the top of the [Restore Database] dialog window, click on the [Script] button.
  14. Edit the Query script to look like the following.
  15. [SQK Server - Restore Backup]
    CFFCS | CarrzSynEdit: | SQL Script 
    USE [master]
RESTORE DATABASE MyDB
FROM DISK = 'C:\MyDB.bak'

WITH REPLACE

  16. 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 one of the following errors, please follow the detailed instructions to resolve the issue:
[Error #1]
If USE master is not included at the top, you may receive the following Error.
RESTORE
Cannot process database 'WCFF_Old' because it is in use by this session. It is recommended that the master database be used when performing this operation.
[Error #2:]
Msg 5133, Level 16, State 1, Line 1
If it cannot find the specified path, then perform the following actions.
Example
If the path in the Error is:
C:\MSSQL.1\MSSQL\DATA\MyDB.mdf
Then go to your C:\ drive and create the folders exactly as they appear in the Error: [C:\MSSQL.1\MSSQL\DATA\]. Once you have created the folders, execute the command again; this "should" restore the Database to your current Database and add the database files to the folder you just created.
[Error #3:]

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:
  1. [Services]
  2. Choose [SQL Server (SeverName)]
  3. Right Click and [Restart]
  4. Once the SQL Server is restarted
  5. Open [SQL Server Management Studio]
  6. Right-click on the Database Name, and choose [Delete]
  7. When the Dialog opens, select [Delete] to delete the Database.
  8. Once the Database is deleted.
    Right-click on the main folder [Database]
  9. Choose [Restore Database]
  10. When the [Restore Database] dialog opens.
  11. In the [To Database]: type in the database name.
  12. Choose [From device:], click the ellipse button to open the [Specify Backup] dialog box.
  13. Click [Add] to browse to the location where you stored your .bak database file.
  14. 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.
RESTORE DATABASE
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).