As part of my work on the Backup/Restore portions of DBATools I was interested to see if I could avoid the overhead of having to parse files using SQL Server to find out if they were backup files, and if they were what they contained. (I’m a DBA, ergo I’m paranoid, ergo I’m not trusting a file extension!)
Working on the assumption that SQL Server has a RESTORE HEADERONLY option, and that the speed results are returned with even from large backup files, I was hoping to find all the information I needed fairly early on in the file headers.
In this post I’m going to show how I worked through this using PowerShell (DBATools is trying to avoid using anything other than easily available PS cmdlets, so pulling dlls and other .Net objects would feel a little wrong!).
tl;dr
It sort of works. Not very useful with compressed backups, but reliable for filtering normal backups in a hurry.
So for this month’s T-SQL Tuesday Kenneth asked us to talk about backups.
If you’ve been reading my blog for a while, you’ll be aware that I have a ‘thing’ about backups and restores (31 days of posts in a row on a topic shows a slight interest!), and I’ve been speaking at SQL user groups on the subject for years as well.
Why?
During my whole DBA career I’ve been aware that the absolute worst thing that can happen to me is having to admit to my employer that I can’t recover their precious data to the point they want (RPO), or do it in a time that they’re happy with (RTO).
To me, if either of those happens it’s a Resume Generating Event! And that’s an event I’ve spent years avoiding. And having those bases covered also stops other mistakes (what, I ran that DROP TABLE in prod?) from becoming RGEs as you’ve got a safety net.
As I’ve moved into management, I’ve also come to realise it’s even worse when you hear from someone who reports to you that they aren’t 100% sure about their backups, this could be 2 RGEs for the price of one!
So I’ve always been hugely keen on backups, and more importantly on ensuring that they’ve been tested and you’ve practiced your restores as much as possible.
SQL Server makes it very easy to perform backups, plenty of options to perform and unless you’ve a fairly large or specialised database setup then there’s fairly little setup required.
Restoring a database, now thats a whole other story. If you’re like most people, you’re taking a full backup each night and then transaction log backups hourly through the day. So at worst case you’re got 25 files to restore.
On a larger system you could have a weekend full backup a twice daily diff backup and 10 minute transaction log backups. Now, these things always fail just before the next full backup so you’re looking as 1 full backup, 13 differential backups and 71 transaction backups to process and restore! Now that’s going to take a while to script out and get going!
SSMS will help you out if you have access to the server that made the backups and it can see msdb, if that whole instance is now a smoking crater, all you’re going to have is a folder full of backup files.
