locked
Backup/Restore sql server 2005 RRS feed

  • Question

  • hey,

    here is my situation. I have a multiple file backup on a server A (4 files) which is to be restored on to server B. It is to be restored onto four mounted volumes with a .mdf and 3 more .ndf files. Lets assume i have,

    Server A
    employees.backup.01
    employees.backup.02
    employees.backup.03
    employees.backup.04

    on Server B, i need

    c:\backup1\employees.mdf
    c:\backup2\employees.ndf
    c:\backup3\employees.ndf
    c:\backup4\employees.ndf with move option (.mdf and .ldf files are to be moved from their original location)

    would you please provide t-sql code for this task...or is there a GUI way to do it
    Friday, September 18, 2009 1:02 AM

Answers

  • You cant restore the data and logfiles straight away from instances.
    Take a backup using this comand ,
    backup database employee to disk ='c:\temp\emp.bak'

    and now you will ahve to move this backup file to B server and restore it there ,for Restoring the file use this command

    RESTORE DATABASE employee FROM disk = 'c:\temp\emp.bak' (In case if you dont have employee db in server B)

     

     


    If you wish to move your mdf and ndf files , please read about attach and detach db at http://msdn.microsoft.com/en-us/library/aa259611(SQL.80).aspx.

     

     


    Thanks, Leks

     

     

    Friday, September 18, 2009 3:27 AM
    Answerer
  • If your servers are in a network you can restore the files with out copying them to server B. It requires your sql service accounts to have neccessary permission.



    SqlFrenzy
    • Marked as answer by club140 Thursday, October 8, 2009 9:48 PM
    Friday, September 18, 2009 4:05 AM

All replies