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.
  • Breadrumbs:

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

    • 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)
  • Article ID:
    128
  • Date Created
    Friday, April 1, 2011
  • Last Updated
    Friday, March 29, 2024
  • This Article Has been Viewed
    16817 times
  • Short Desc
    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
    ------------------------------
    [/codevalue]
  • Recreate Issue
    To recreate this issue.

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

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

    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.

    [quote]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.

    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.



     
    USE [master]
    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).

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles