none
T-SQL for Database restore from one server to another

Answers

All replies

  • Remotely backing up a database is simple - use an Execute SQL Task to issue a BACKUP DATABASE command.

    That will make a backup somewhere on server A's filesystem.  You'll then need to get this backup over to server B's filesystem somehow.  This will take either xp_cmdshell to do the copy, or some other method.  SSIS can be used to do this using a File System Task.  The difficulty is that two processes might need to be executed.  One on server A to copy the backup to a "shared" location, and one on server B to copy from the "shared" location to a local drive.  You could make do with one process if either server A or B shares a local drive to the other server.  You can remotely start an SSIS package by constructing a SQL Agent job that runs it on the remote server, then call sp_start_job on that remote server.

    Remotely restoring a database is just as easy with the RESTORE DATABASE command. No linked servers are necessary.


    Todd McDermid's Blog Talk to me now on
    Friday, April 15, 2011 9:59 PM
    Moderator
  • Hi Todd,

    Just so that I don't get the syntax wrong where do you specify the remote sql server's name so that I don't accidently restore to the same sql server (which would be VERY bad)?

    RESTORE DATABASE YourDB
    
    FROM DISK = 'D:BackUpYourBaackUpFile.bak'
    
    WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
    
    MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
    
    
    Thanks.

    Friday, April 15, 2011 10:21 PM
  • Hi Todd,

    Just so that I don't get the syntax wrong where do you specify the remote sql server's name so that I don't accidently restore to the same sql server (which would be VERY bad)?

    RESTORE DATABASE YourDB
    
    FROM DISK = 'D:BackUpYourBaackUpFile.bak'
    
    WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
    
    MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
    
    
    Thanks.

    you should run this command in a CONNECTION TO REMOTE SQL SERVER. you can run it in a new query window in SSMS which connects to remote sql server.

    or in your application with a connection to remote sql server.


    http://www.rad.pasfu.com
    Saturday, April 16, 2011 3:57 AM
    Moderator
  • Check Backup database task in Maintenance plan tasks which can place the backups on shared locations as well provided you have the access.


    Request to please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
    Saturday, April 16, 2011 7:26 AM
  • You don't specify a server name in the statement itself - as Reza has said, you need to connect to the server you want to restore the database to, and issue the command to it.

    As I've said, the biggest part of this fairly simple problem is moving the backup file from server A to server B.  After you've done that, then you can issue the restore command.


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Trancedified Tuesday, April 19, 2011 2:56 PM
    Sunday, April 17, 2011 8:08 PM
    Moderator
  • Thanks guys.


    Todd: The part of copying the .bak file from serverA to serverB is already completed in my SSIS package with the code below using a Script Task:

     

     Private Sub CopyFiles()
     
      For Each strFile As String In My.Computer.FileSystem.GetFiles("\\serverB\somefolder\")
       My.Computer.FileSystem.DeleteFile(strFile)
      Next
    
      For Each strFile As String In My.Computer.FileSystem.GetFiles("D:\SQLBackup\serverAfolder\")
    
       If My.Computer.FileSystem.GetFileInfo(strFile).CreationTime.ToShortDateString = Now.ToShortDateString Then
         My.Computer.FileSystem.CopyFile(strFile, "\\serverB\somefolder\" & My.Computer.FileSystem.GetFileInfo(strFile).Name, True)
    
         Exit For
       End If
      Next
     End Sub
    

     

    All I have to do is create a new connection manager object then point that to a Execute SQL Task, correct?

     

     


    Monday, April 18, 2011 4:15 PM

  • All I have to do is create a new connection manager object then point that to a Execute SQL Task, correct?

     

     


    yes, and also paste restore command in the sqlstatement property of execute sql task

    let us know if you have any problem.


    http://www.rad.pasfu.com
    Monday, April 18, 2011 5:18 PM
    Moderator
  • Yes, the connection manger  should be poined to the destination server .

    Question : Can back up database task be used to copy the backup to other server ? I think yes, but just wanted to confirm. If so this task can replace the script above but only that there is only database backup that is performed. Any one can post ideas. Thanks.

     

     


    Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
    Monday, April 18, 2011 7:03 PM
  • I'm using a global variable within my package. Is there a way to check if there's a variable value prior to running the Execute SQL Task? I don't want to run the SQL Task if that variable isn't populated.

     

    Thanks.

    Monday, April 18, 2011 10:18 PM
  • you can check variable values with expression in the precedence constraint and do appropriate action based on it.

    this is a sample of how to implement IF condition based on variable values in ssis package control flow:

    http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.html


    http://www.rad.pasfu.com
    • Marked as answer by Trancedified Tuesday, April 19, 2011 2:56 PM
    Tuesday, April 19, 2011 3:16 AM
    Moderator
  • No - there is no way to back up a database to a non-local drive.  You can't back up to mapped network drives or UNC shared drives.  You can only back up to DAS drives (Direct Attached Storage).

    You also do not need the Script Task to copy the file.  You can (and should) use the File System Task to copy the file.  Doing so will make your package much more understandable to the next developer that needs to debug or modify your package.


    Todd McDermid's Blog Talk to me now on
    Tuesday, April 19, 2011 4:09 AM
    Moderator
  • Thanks for all of your advice. I was able to solve my problem.

     

    Tuesday, April 19, 2011 2:56 PM