Microsoft – SQLServer – Database Backup – Maintenance Plan “Skipping some databases”

Microsoft – SQLServer – Database Backup  – Maintenance Plan “Skipping some databases”

Still reviewing database backup history log and discovered that some of our databases are not getting backed up.

Reviewing our maintenance plan and it looks clean.  It is set to backup all databases, as opposed to maintenance plans with explicitly listed databases.

Everything looks good – reviewed the job log, a bit compressed and not as easy to read.

And, so modified the plan by adding “Reporting and Logging”.

Re-ran the backup and still no backup, though, now the reports is much easier to read.

I thus set forth to determine if there was something special about that database.  Is its owner valid, is it online, etc…

Select the database and right click on it to access it’s property:

Noticed that the database status’s states “Shutdown, Normal”.

Also reviewed the database’s properties – Options Tab: And, confirmed that it is offline.

 

These two information:

  1. Database Status indicating “Shutdown\Normal”
  2. And, Database Option \ “Auto Close” stating “True”

is “telling”.  The database is not getting backed-up consistently as in some cases it might be offline.

Switching off “Auto Close” will probably lead to greener pastures….

 

 

 

11 thoughts on “Microsoft – SQLServer – Database Backup – Maintenance Plan “Skipping some databases”

  1. Hi i have this same issue with our litespeed 6.1 backup. backup will complete on 8 out of 9 databases. not sure why this keeps happening. There are no errors to indicate what may have caused the issue. Here is a post i sent to quest but did not get a concrete answer (http://communities.quest.com/thread/14196) .Do you have any ideas ?

    • Chinedu:

      Hey homey. I think it might be an identical issue to one I have.

      Please look a bit more into these areas:

      a] Per Quest Configuration, did you explicitly list each database and this one is just not part of your list
      b] Have you tried selecting for all databases to be backed-up
      c] have you reviewed whether this database is set for auto-close \ offline and was just not up
      d] Also, have you tried to turn verbose logging on and reviewed the corresponding log files

      all the best (in MS SQL Server land)

      Daniel

      • a] no we are using the sp below to take backups. i added lines (commented out) to see what the status of the cursor and error message.

        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO

        ALTER PROCEDURE [dbo].[sp_backup_databases]
        AS
        BEGIN
        SET NOCOUNT ON;
        –SELECT ‘cursor hasn”t been declared; the status is: ‘ + CAST(CURSOR_STATUS(‘global’,’db_cursor’) AS VARCHAR)

        DECLARE @dbname varchar(50)
        DECLARE @sqlstmt varchar(2000)

        BEGIN TRY
        DECLARE db_cursor
        CURSOR GLOBAL FOR
        select [name] from
        sys.sysdatabases
        where [name] not in(‘tempdb’)
        order by [name]

        –SELECT ‘cursor is declared; the status is: ‘ + CAST(CURSOR_STATUS(‘global’,’db_cursor’) AS VARCHAR)

        OPEN db_cursor

        –SELECT ‘cursor is opened; the status is: ‘ + CAST(CURSOR_STATUS(‘global’,’db_cursor’) AS VARCHAR)

        FETCH NEXT FROM db_cursor INTO @dbname

        WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @sqlstmt=’exec master.dbo.xp_backup_database @database=”’+@dbname+”’
        ,@filename=”t:\sql_backups\’+@dbname+’.bak”’+’
        ,@init=1’+’
        ,@encryptionkey=”yyyyyy”’
        PRINT @sqlstmt
        EXEC (@sqlstmt)
        FETCH NEXT FROM db_cursor INTO @dbname
        –SELECT @@FETCH_STATUS AS fetch_status
        END
        END TRY

        BEGIN CATCH
        –SELECT
        –ERROR_NUMBER() AS ErrorNumber,
        –ERROR_SEVERITY() AS ErrorSeverity,
        –ERROR_STATE() AS ErrorState,
        –ERROR_PROCEDURE() AS ErrorProcedure,
        –ERROR_LINE() AS ErrorLine,
        –ERROR_MESSAGE() AS ErrorMessage;

        END CATCH

        CLOSE db_cursor

        –SELECT ‘cursor is closed; the status is: ‘ + CAST(CURSOR_STATUS(‘global’,’db_cursor’) AS VARCHAR)

        DEALLOCATE db_cursor

        –SELECT ‘cursor is deallocated; the status is: ‘ + CAST(CURSOR_STATUS(‘global’,’db_cursor’) AS VARCHAR)

        END

        b] no may have to change the statement below to only select db’s that need to be backed up.
        select [name] from
        sys.sysdatabases
        where [name] not in(‘tempdb’)

        c] yes db is not set to autoclose and db’s are online

        d] yes, the output has not been helpful to determine the cause of the issue

        I will try ‘b’ and see if this works. Thanks for the suggestions

  2. Yes, omitting tempdb should help

    –something like the following help, as well

    select tblDatabase.name
    from sys.databases tblDatabase
    –database is online
    where tblDatabase.state_desc in (‘ONLINE’)
    –database is not a snapshot
    and tblDatabase.source_database_id is null
    –explictly list databases that can not be backed-up
    and tblDatabase.name not in (‘tempdb’)

    Also, please leave-in the exception handling error piece:

    I appears that is currently comment out, via —

    – we need it to track down hard to find errors

    SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

    • Chinedu:

      Thanks for the info. I have never had to use \ request a Static Cursor. But, I agree that it might be necessary and useful if one has a “volatile” recordset.

      Have never thought of data in sys.databases to be that volatile. And, the truth is that if one uses static cursor (where one makes a copy of the list of databases) ahead of time, one might be in trouble if existing record, in this case, databases gets removed or deleted.

      I suggest you consider using if db_id() to make sure that the database still exists before attempting to back it up.

      How many databases are you backing up? And, was it skipping the same database lists or random ones.

      Was there a special order – the ones in the top, middle, or last end of your list. Also, did you have an Order by.

      Also, consider taken a deeper look at (Fetch_Status != 0)

      Per Bob Barrows & Andrew J. Kelly:

      http://www.justskins.com/forums/why-not-simply-while-147247.html

      FETCH NEXT FROM …

      –The FETCH statement failed or the row was beyond the result set.
      WHILE (@@fetch_status -1)

      BEGIN

      — The row fetched is missing.
      IF (@@fetch_status -2)

      END

      I take it that the error handling did not “expose” any errors.

      Much thanks for the follow-up.

      Daniel

  3. Hi Daniel, Thanks for the prompt response. I did order by and db that gets skipped is msdb. How do i use db_id() in the sp i posted earlier ? You are right, the error handling did not pick up any errors, i will have a look the fetch_status.

    • Chinedu:

      Please look for me (Daniel Adeniji) on http://www.linkedin.com or facebook.com and we can exchange contact info. I will like to take another look at your backup Script and see if there is anything in there that is causing the msdb database to sometimes be skipped.

      I do not want to publish personal contact details on this Public Page. If there is another way you can reach me personally, please do so.

      Thanks,
      Daniel

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s