Never credit SQL Server with intelligence it doesn’t have

and that goes double for SQL Server Management Studio (SSMS)

Yet another example of someone having problems with backups and SQL Server, mostly caused by not checking exactly what’s happening and not having a full written procedure in place.

DBA was notified of a problem with a key production system. After a bit of digging they realised that a misbehaving SSIS package had truncated a table. Weighing up the chance of the package running perfectly and not causing any more problems, the DBA decided to restore the database to a temporary location from a backup and then migrate the data across. The package run at 19:30, before the full backup kicked in at 20:00. Being a good DBA they had plenty of hourly transaction log backups as well, so a few clicks in SSMS and a point in time restore was underway. Feet up, a coffee while waiting and then off to the pub having pocketed some nice overtime.

Unfortunately, when the the restore had completed our brave DBA found that the table in the restored DB was also empty. OK, maybe the table was corrupted by something else and not the SSIS package, so they setup a point in time restore for close of business @17:00, as the users hadn’t complained then. Hoping for a quick restore as last orders is fast approaching our brave DBA sets it off.

Restore finishes, and the table is still empty. Getting slightly worried by now the DBA tries the night before’s backup as a last resort, though they can’t imagine how the system could have run a day with an empty table. Guess what, still no luck.

At this point we get called in as noone can work out what’s going on. A quick look in msdb.dbo.backupfiles and we had our answer:

select 
a.backup_start_date,
b.physical_device_name
from 
backupset a
inner join 
backupmediafamily b on a.media_set_id=b.media_set_id 
where 
a.type='D' and 
a.database_name='dbname' 
order by a.last_lsn desc

Our DBA had been diligently backing up the database every day like the should, but they’d been backing it up to the same dbname.bak file every night, complete with init and noappend, so every night the same file was being overwritten. SQL Server will only do what you tell it to.

But why didn’t the point in time restores complain? Well again, SQL Server went off to look in msdb.sys.backupfile and worked out which files it needed to do the restore. Guess which file it used every time? And then guess what, as the following transaction backups were for later LSNs, SQL Server happily ignored them.

Once we’d pulled the earlier version of dbname.bak off of tape and manually built up some T-SQL statements to do the required point in time restore everything worked as expected and we could just migrate the table data across.

The first moral of the story is to be careful about what you tell SQL Server to do, as it will do exactly that. Just like the petulant 5 year old.

And the second is that SSMS can be pretty dumb about what it lets you set when building up backups and restores. If the restore here had been down with T-SQL in the first instance, then the DBA would have seen error messages as SQL Server refused to apply transaction log backups due to LSN order.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

No comments yet.

Leave a Reply