SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Backup script skipping databases – and the fix!

A real head-scratcher

I’ve been using my RYO Maintenance scripts for a little while now, and they’ve been working great. Almost. One day I noticed that the backup script hadn’t backed up all of the databases on my instance. I checked the job history, no errors. I checked the SQL Server log, no errors. I checked the Windows event logs, nothing. Odd, and a little disconcerting (to put it mildly). So, I asked the Great Gazoogle.

Apparently, I’m not the only one with this problem. Others have run into the same odd behavior, and while the fix was simple, I’ve yet to find an explanation as to the cause. There have been some hypotheses thrown out, but nothing definitive.

The common theory is that something about the backup process is changing records in the sys.databases catalog. When the cursor tries to move to the next row, it can’t. So far, due to the random nature of the problem, I’ve not been able to reproduce and debug it. If anyone out there has found the cause, I’d love to hear from you.

In the meantime, the fix: use a STATIC cursor. Like so:

    SELECT d.database_id, d.name FROM sys.databases d
    WHERE d.name <> 'tempdb'
    AND d.state = 0
    AND d.name LIKE @dbs
    AND d.database_id > @diff_dbid       --no system dbs for diff backups
    AND d.recovery_model < @trn_rm       --no simple mode dbs for log backups

This will create a temporary, static, copy of the rows your cursor will be stepping through. That way, if the data in sys.databases is changing, it won’t effect your cursor. Another workaround would be to explicitly create a temporary table for your sys.databases records and cursor through that. Potayto. Potahto.

I’ve updated the script in my original post to use the STATIC cursor, but I wanted to bring this to your attention, just in case you happened to run into the same problem.

Cleveland DBA

Colleen Morrow is a database professional living in Cleveland, OH who has been working with database systems since 1996. For more than 12 years, she was a Database Administrator at a large law firm where she developed an appreciation for auditing, automation, and performance tuning. Since that time she has worked with clients in the healthcare, manufacturing, software, and distribution/freight delivery industries. Colleen is currently a Senior Consultant at Fortified Data.


Leave a comment on the original post [colleenmorrow.com, opens in a new window]

Loading comments...