Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Restore Database from one server to another

    Hello All!

    I would like you to help me with syntax to restore the database
    Here is my situation I haver ServerA and ServerB. ServerA has DB called TEST and is being backed up everyday on D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Test.Bak, now I need to restore this backup on ServerB's D-drive. Help me with the T-SQL syntax.

  2. #2
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Why TSQL?

    Why do you need to do it in T-SQL?
    Can't this be done with Enterprise manager, possibly setting up database maintenance plans using SQL Server Agent?

  3. #3
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    It is fine with me, give me the steps to do that because i didn't see on Enterprise Manager where to backup from another server

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    The best way to do this is to copy the backup from the server A to server B
    and use 'Restore database' with move option.
    for more details on restore database check BOL.

  5. #5
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Automate it

    Really you should be able to automate the whole process.
    1) Set up maintenance plan to do backup
    2) Schedule the copy the .bak file to the other server, either with a sql server agent job or a with .bat file in windows (I've never actually tried this)
    3) Schedule a task to run the 'Restore database' as mentioned before. You may need to set up a backup device that points to the copied .bak file.

    Obviously you want to make sure you schedule these things so you give them time to finish.

    Alternatively you might want to just do the restore bit manually.

  6. #6
    Join Date
    Oct 2003
    Posts
    47
    Suppose that we have only the backup (done by EM). How to restore this last in another server (and change the dbname)

  7. #7
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    I do foloowing

    1. Create db in new server winth the same name
    2. Retsore
    3. go to DATA folder and coby db files with new name
    4. exec sp_attach_db

  8. #8
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    How will I copy that .bak from one server to the other

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    RESTORE db_new_name
    FROM DISK = '\\server\share\backup_name.bak'
    WITH
    MOVE logical_data_file TO 'physical_data_file.mdf'
    MOVE logical_log_file TO 'physical_log_file.ldf'

    To get the logical file names, you can use RESTORE FILELISTONLY.

    All these commands are described with cut-and-paste examples in Books Online.

    The account that runs SQL Server on the server you're wanting to restore these files to will need to have access to the network share you're wanting to restore from.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    I get an error
    'db_new_name' is not a recognized RESTORE option.

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    RESTORE DATABASE db_new_name
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •