Post reply

Restore Database to new SQL Server from backup

  • December 21, 2004 at 8:47 am

    #163458

    I am using the following to backup the database:

    BACKUP DATABASE [BINT_Prod] TO  DISK = N'\\ribackupsrv\BACKUP\BINT_Prod' WITH  INIT ,  NOUNLOAD ,  NAME = N'BINT_Prod backup',  SKIP ,  STATS = 10,  NOFORMAT

     

    Now I need to restore the database to a new SQL Server.

    If I use the wizard, the selection is only for the c: and d: drive;

    and not the backup server.

    Thanks for the help.

  • December 21, 2004 at 10:36 am

    #534644

    See the SQL BOL topic "How to restore a database with a new name (Transact-SQL)" for the TSQL command.

    I don't use the wizard, but the Restore GUI inteface can also be used.  The graphical directory browser shows only local drives, but you can type in a UNC path for the location of the backup file to be used.

  • December 21, 2004 at 10:46 am

    #534645

    Read up BOL for correct syntax and/or other needed arguments, but you'll need Restore with move.

     

    D. Restore a database and move files

    This example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL\Data directory.

    RESTORE DATABASE MyNwind   FROM MyNwind_1   WITH NORECOVERY,       MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',       MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'RESTORE LOG MyNwind   FROM MyNwindLog1   WITH RECOVERY

  • December 21, 2004 at 2:27 pm

    #534678

    and to add to Osoba you will need also ,REPLACE if the files exists

    RESTORE DATABASE MyNwind   FROM MyNwind_1   WITH NORECOVERY
    ,       MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf'
    ,       MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'
    , REPLACE
    RESTORE LOG MyNwind   FROM MyNwindLog1   WITH RECOVERY

     


    * Noel

  • December 21, 2004 at 3:28 pm

    #534690

    Thanks to everyone help.  Have a nice holiday!

Viewing 5 posts - 1 through 4 (of 4 total)

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