Backup & Restore

  • Whizkid

    Right there with Babe

    Points: 778

    I have DB in the production server in the name "LMS_App" and took the backup of this DB. I would like to resore this DB in our development server. While restoring in our development server with different DB name, I'm getting an error and cause of this error is that the LDF & MDF file has exists in the development server in the same name of "LMS_App". Hence, while restoring, I've changed the location of MDF & LDF file in the other name(LMS_App_Prod) and it donesn't helping me to resolve this issue.

    Can you please help me to restore this DB in the same server with different DB name?

     

    • This topic was modified 4 weeks ago by  Whizkid. Reason: typo
  • Thom A

    SSC Guru

    Points: 98326

    What's the SQL you're running to restore the Backup? Are you using WITH MOVE ... TO syntax (as shown in the documentation here)?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Whizkid

    Right there with Babe

    Points: 778

    I'm able to achieve through below SQL Query. How do I do this in SQL Server Management Studio Wizard?

    --Create TestDB1
    CREATE DATABASE TestDB1;
    GO

    --Create TestDB2
    CREATE DATABASE TestDB2;
    GO

    --Backup of TestDB1
    BACKUP DATABASE TestDB1 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\TestDB1.bak' WITH INIT, COMPRESSION;
    GO

    --Restore TestDB1 backup into TestDB2
    RESTORE DATABASE TestDB2
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\TestDB1.bak'
    WITH REPLACE,
    MOVE 'TestDB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB2.mdf',
    MOVE 'TestDB1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB2.ldf';

     

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32375

    I think it's the second tab in the restore GUI- there's a box you can tick.

  • Whizkid

    Right there with Babe

    Points: 778

    Can you please explain the GUI box (caption) you are referring in second tab?

    • This reply was modified 3 weeks, 4 days ago by  Whizkid. Reason: typo
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715866

    In the restore dialog, there are selections on the left side, one of which is for the Files tab. This is the second one. There is a "relocate" checkbox on this tab.

    2019-09-23 09_17_30-Restore Database - Northwind

  • Whizkid

    Right there with Babe

    Points: 778

    Can't we restore the DB using "Tasks->Restore->Files and Filegroups" option? I was following below steps.

    1)      Created a new database

    2)      Navigate to Tasks->Restore->Files and Filegroups

    3)      In “General” tab,

    a.       I gave the DB name which is created in Step 1 in “To Database” dropdown under “Destination to restore” section

    b.       Click “From Device” radio button and select location of BAK file under “Source for restore” section

    c.       Select “Restore” checkbox ” in “Select the backups sets to restore” section

    4)      In “Options” tab,

    a.       Select “Overwrite the existing database (WITH REPLACE)” checkbox under “Restore Options” section

    b.      Change the location of MDF & LDF file of the databse which is created in Step 1 under “Restore As ”

    4)      Click "OK" button and ending up with below error.

    I'm ending up with an error "The backup set holds a backup of database other than the existing "TestDB9" database.

    RESTORE DATABASE is terminating abnormally(Microsoft SQL Server, Error: 3154)"

    Note:

    When I select "Tasks->Restore->Files and Filegroups" option, I'll be getting the dialog box with 2 options like "General", "Options" (ref. below screenshot) where as in "Tasks->Restore->Database" option,  we do have an additional option called "Files".

    Untitled

     

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply