Sql-server – Re-Run Backup Job in SQL Agent

sql-server-2008-r2

My question is regarding re-running backup jobs only for those databases for which the job initially when it ran failed due to some reasons, like server reboot, path not accessible, newly created databases, etc.
In our environment, windows patching happens every month. Due to server reboot at this time, backup job fails. Also, sometimes if user creates a new database then a scheduled differential backup job fails.
Is there any way to run the same job but for only those databases which were not backed up earlier?

Please provide your suggestions

Best Answer

While it depends on how many jobs and your backup methods, one of the simplest options is to have the job use retry attempts and intervals to re-run the job.

You can set the intervals at a high amount to account for a full system reboot and use these jobs specifically during reboot windows. Just go to Job Properties -> Steps -> Advanced

enter image description here

(Job pictured is from Ola Hallengren's scripts which contain great backup jobs)

Include a cleanup step first to remove any backups from recent history (say, 1 hour) that may no longer be valid/desirable and then perform a complete backup. I think this is preferable as it gives you a set of backups all from the same time frame and not staggered depending on when the shutdown occurred.

Really though, you should be able to reliably control and schedule your reboots and thus schedule key backup jobs around that time frame. We all update our boxes (I hope) and work around a set schedule for reboots so as to not have this issue. Stronger change control management might be the true solution if this server is important enough to stay on top of backups.

Related Solutions

Sql-server – Restore bak file to smaller mdf and ldf database files

The backup contains two pieces of information:

the database files layout and metadata, ie. RESTORE FILELISTONLY ...
the actual data

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.

Sql-server – Differential backup successfully completes without taking the backup of few databases

If you are saying that some databases are being skipped, then I would bet money that you are using sp_MSforeachdb. Stop using that procedure, it is an unreliable pile of unsupported and undocumented crap that is known to skip databases in certain scenarios (usually where > 100 databases are used on a busy system).

Solution: write your own cursor with sensible options:

CREATE PROCEDURE dbo.my_own_foreachdb
  @command NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @db SYSNAME, @sql NVARCHAR(MAX);

  DECLARE c CURSOR 
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR SELECT name FROM sys.databases;

  OPEN c;

  FETCH NEXT FROM c INTO @db;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'EXEC ' + QUOTENAME(@db) + '..sp_executesql @command';

    EXEC sp_executesql @sql, N'@command NVARCHAR(MAX)', @command;

    FETCH NEXT FROM c INTO @db;
  END

  CLOSE c;
  DEALLOCATE c;
END
GO

Sorry, untested, busy day over here - but should demonstrate the approach.

Or see this and this (I wrote a more reliable and flexible replacement).

Best Answer

Related Solutions

Sql-server – Restore bak file to smaller mdf and ldf database files

Sql-server – Differential backup successfully completes without taking the backup of few databases

Related Question