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

sql serversql-server-2008-r2

I have differential backups for multiple databases configured to run through a stored procedure. Sometimes one or more databases have not been backed up, even though the job was "successful."

I'm calling the backup through a stored procedure that runs scheduled in a job.

I have created the output file in the job, but it is not saying anything specific.

I've checked the SQL Server logs, event logs, changed the timings of the job, however I'm still not able to get the desired result nor any specific reason.

No recovery model change has happened.

The log backup job is running fine without any issues.

SQL Server version is SQL Server 2008 R2 + SP1 on Windows Server 2008 R2.

Best Answer

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).

Related Question