Syntax for Automated Redgate Restore Jobs

Comments

• Options

  Jason Cook Posts: 185

  September 18, 2008 5:43AM

  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

  0 · Share on Twitter
• Options

  Matthew Flatt Redgate › Posts: 255 Bronze 5

  September 18, 2008 5:53AM

  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
  Redgate Foundry

  0 · Share on Twitter