SQL Server Performance

Auto restore of a backup file from prod to qa

Discussion in 'ALL SQL SERVER QUESTIONS' started by RamDBA, Jan 18, 2012.

  1. RamDBA New Member

    Is it possible to copy one db backup file from PROD server to QA server and restore that file in the QA server automatically using any job scheduler ?

    I have tried to use the xcopy command ,and this is working upto copying the file, but i am not able to make it restore in the QA database .

    Can any one suggest me or provide the code for the same
  2. davidfarr Member

    The RESTORE command must be run from the master database on the QA server. I do not think you can achieve this with a Windows Task Scheduler.
    If you prefer, there is no need to copy the backup file over to the QA server before restoring. It is possible to RESTORE across a network path if the QA server service account has permissions on the backup folder of the PROD server.
    You could create a SQL Agent job on the QA server, to run a T-SQL command on the master database of the QA server.
    A suggestion for the command for the job to run can be similar to;

    RESTORE DATABASE [MyDb] FROM DISK = N'\\ProdServer\c$\backup\MyDb_backup.BAK' WITH FILE = 1, REPLACE, RECOVERY

    The tricky part here is that the name of the backup file on PROD server will change each time a new backup is made because backup files usually have a date-time appended to the file name. You might need a secondary job or process to rename the backup file to "MyDb_backup.BAK" before the restore job runs, for consistent results.

    Be sure to read up on the RESTORE command syntax (at MSDN or Books Online) to select your preferred options. Be warned that the syntax example above will overwrite MyDb on the QA server if it already exists from a previous restore. (REPLACE option)
  3. RamDBA New Member

    HI David,

    Thanks for the suggestion ,and now i got some clear idea how to move ahead to resolve it. will start working on this ,will reply you back if i got succeeded on this issue.


Share This Page