Link to home
Start Free TrialLog in
Avatar of jpguillebaud
jpguillebaud

asked on

Backing up SQL databases with retry option

We are backing up our SQL databases with the SQL "BACKUP DATABASE" command.  We also backup the resulting BAK files on to an external hard drive.  Occasionally the process that copies the BAK files on to the external hard drive clashes with the SQL backup command causing it to fail.  

Is there a way to make the SQL backup retry after a specified time interval if it fails to backup a database?
Avatar of arnold
arnold
Flag of United States of America image

The process that backs up the .bak file needs to have additional logic added to it that checks that the file was last updated 30 minutes ago, if not it enters a loop or waits five minutes before checking again.

Since you are using the backup database command, do you make sure the name of the .bak file is unique by using date database_20130716201212.bak

The backing process will then backup and manage the number of .bak files it retains.
Avatar of jpguillebaud
jpguillebaud

ASKER

Is there a way to use a BEGIN TRY CATCH in the SQL bask task to tell it to retry the backup if it fails the first time?
SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good answers.