Syntax for Automated Redgate Restore Jobs

• 
  The syntax for the restore command can be found here (refers to 5.2, but is the same for 5.1):
  http://help.red-gate.com/help/SQLBackup ... STORE.html
  
  Be aware that if you have encrypted the backup, the restore job will require the password to be stored in plain text.
  
  Hope that helps,
  Jason

  Jason Cook September 18, 2008 05:43

  - 0 +
• 
  Hi snapperfish,
  
  The easiest way to get the syntax is to go through the Restore Wizard and on the last step click the Script tab, the restore script used will then be displayed and you can copy/modify it.
  
  The basic syntax to restore a full backup over an existing database replacing it is :
  

  master..sqlbackup N'-SQL "RESTORE DATABASE [Northwind]  FROM DISK = ''C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Northwind.sqb'' WITH RECOVERY, REPLACE"'
  

  When using in an agent job you should add the following error handling :
  

  DECLARE @errorcode INT
  DECLARE @sqlerrorcode INT
  EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [Northwind]  FROM DISK = ''C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Northwind.sqb'' WITH RECOVERY, REPLACE"', @errorcode OUT, @sqlerrorcode OUT;
  IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
  BEGIN
  RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
  END
  

  I hope this helps.

  Matthew Flatt September 18, 2008 05:53

  - 0 +

Add comment

Please sign in to leave a comment.