Scripted restore to another Db

• 
  Hi Graham
  
  Thank you for your post into the forum.
  
  What you are seeking can be achieved, please use the example script below and modify it to suit your needs:
  
  DECLARE @errorcode INT
  DECLARE @sqlerrorcode INT
  EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [<database_name>] FROM DISK = ''<path to backup file>.sqb'' WITH RECOVERY, MOVE ''<database_name>_Data'' TO ''<path to database mdf file>.MDF'', MOVE ''<database_name>_Log'' TO ''<path to database ldf file>.LDF''"'
  IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
  BEGIN
  RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
  END
  
  Further information on the Restore command can be found in the SQL Backup help information for the Restore Command using this LINK:
  
  Many Thanks
  Eddie

  Eddie D May 24, 2011 08:56

  - 0 +
• 

  eddie davis wrote:

  Hi Graham
  
  Thank you for your post into the forum.
  
  What you are seeking can be achieved, please use the example script below and modify it to suit your needs:
  
  DECLARE @errorcode INT
  DECLARE @sqlerrorcode INT
  
  ---- snip snip

  ---

  
  Many Thanks
  Eddie

  
  Hi Eddie,
  
  thanks for the script.
  
  One question, can I build this into the backup script (similar to what I do with the Enterprise Manager). Then I can schedule the backup to run, and as long as the backup is successful start the second step to restore the backup to the archive database.
  
  Regards,
  
  Graham

  doddg May 24, 2011 11:31

  - 0 +
• 
  Thank you for your reply.
  
  The choice is yours, you can create a new SQL Server Agent Job and add the scripted code into a new job step. Configure the job to start after the backup job. Or add a second job step to an existing job and perform the restore on completion of the previous step.
  
  Many Thanks
  Eddie

  Eddie D May 25, 2011 07:11

  - 0 +

Add comment

Please sign in to leave a comment.