Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you have databases with names that have non-alphanumeric characters? –  Thomas Rushton Jul 3 '13 at 14:07
add comment

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

share|improve this answer
    
Alternateively, use a Maintenance Plan. –  Greenstone Walker Jul 4 '13 at 4:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.