Archive | August, 2012

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

Stuart Moore

About 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

The Internet – My saviour and My nemesis

Like any good SQL DBA these days, the internet is a godsend to my career and professional development. I can quickly and easily find articles and notes from world experts on complex topics, and even interact with those experts as well via twitter, email or skype. Compared to the bad old days of having to know someone’s phone number, or battling through tiers of support to get to someone who knew something this is absolutely amazing.

But, on the other hand…….

How many of us are now used to a user (of many levels) coming across to our desk, or dropping us an email about a great new technique they’ve just seen on a random internet search. The canonical examples are with (nolock) and the ever classic one about restoring deleted rows directly out of transaction logs.

As always these are things where the correct answer to “Is this a good idea?” is “It depends”.

As SQL Server DBAs/experts we’ve worked hard to build up a level of internal filtering that lets us look at the post on StackExchange or Twitter, step back and evaluate if it’s something that’s really a good idea. We’ll then have taken it away and played with it on development systems before we think about adding it to our toolkit so we’re fully aware of the techniques limitations. And if we ever do have to deploy it in a production environment we’re also well aware of the amount of work that’s needed, and the amount needed if it goes wrong, and we’ll know which mug will be fixing it,

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

Stuart Moore

About 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

SQL Server Resource Governor Classification function based on db role membership

Inspired by a question on #SQLHelp on twitter here’s a quick classifier function for resource governor that lets you base the resource group based on the connections db role membership.

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

Stuart Moore

About 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