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.
So what to do about it?
This has lead me to be an advocate for being able to restore from nothing other than the raw backup files. This was never easy using pure T-SQL, but once PowerShell and SMO came along it all became much easier.
S
o I was very happy when the team behind DBATools approached me to have a look at their restore methods as it would give me a chance to make it easier for many DBAs to test their backups.
And as a special for this T-SQL Tuesday, we’re pleased to announce some new restore features coming soon.
At it’s simplest restoring your database to the latest point in time from a folder full of backups has become as simple as:
Restore-DBABackup -SQLServer 'server1\instance1' -path \\server2\backups$\ProdDB
assuming you have windows authenticated privileges on ‘server1\instance1’ this will scan all the backup files in \\server2\backups$\ProdDB, work out the combination of Full, Differential and Transaction backups needed to restore your database to the latest point in time they cover, and then perform the restore for you!
No more having to write lots of T-SQL to restore multiple backup files or wait for SQL Server Manager Studio to work it all out for you. It is not going to get much simpler than that.
Of course, we’ve baked in lots of other goodness as well (Do you expect any less from the DBATools team?)
Perhaps you’re using Ola Hallengren’s great SQL Maintenance Solution which stores it’s different backup types in folders labelled FULL,DIFF, and LOG. In that case you’re going to want to use the -OlaStyle switch:
Restore-DBABackup -SQLServer 'server1\instance1' -path \\server2\backups$\ProdDB -OlaStyle
By default we will not overwrite an existing database with the same name on the restore target SQL instance. But if you want to, then the -ReplaceDatabase switch is your friend. Add this, and we’ll write over anything already in place:
Restore-DBABackup -SQLServer 'server1\instance1' -path \\server2\backups$\ProdDB -ReplaceDatabase
Or maybe, you’d rather be nice, and rather than removing the existing database you’d like to restore your backups under a different name. Pass the new name in using the -DBName parameter and you’re all set:
Restore-DBABackup -SQLServer 'server1\instance1' -path \\server2\backups$\ProdDB -DBName NewDB
Now, you’re probably wondering what happens if your target server doesn’t have the same file structure as the server it came from? That’s why we’ve put in a -RestoreLocation parameter. Give this a path and we’ll cope with moving all your files to the new path, and this will include any new files that appear during a restore (did you know that you can get a new data file appearing during a t-log restore? you sure can!):
Restore-DBABackup -SQLServer 'server1\instance1' -path \\server2\backups$\ProdDB -RestoreLocation c:\RestoreFolder\
Perhaps you want to see the gory details of what SQL Server’s planning to do under the hood. We’ve got that covered as well, with the -Script switch you’ll get the Restore scripts and the restore, useful if you want to keep a record of which backups were used (auditors, love that stuff!). Or you can use -ScriptOnly, and we’ll just give you the Restore scripts
Restore-DBABackup -SQLServer 'server1\instance1' -path \\server2\backups$\ProdDB -RestoreLocation c:\RestoreFolder -ScriptOnly
So now there’s now reason not to be regularly test restoring your databases and save yourself some worry when you’re in the hole with your boss breathing down your neck,
Take it for a test drive
If you want to take it for a test drive and provide some feedback, you can download it from: https://github.com/sqlcollaborative/dbatools/tree/restorework
This is still beta, so don’t install in your default $env:PSModulePath! Extract the code to a folder of your choice, and then:
Remove-Module dbatools -force Set-Location c:\path\to\extracted\files Import-Module .\
Once you’ve finished playing, to go back your normal version of DBATools:
Remove-Module dbatools -force Set-Location c:\ Import-Module dbatools
And there’s more to come
This will a first release for this functionality, and effort has gone into making the underlying functions extensible and allow us to easily drop in new functionality.
Features that we hope to include soon include:
- Point in Time restore
- SQL Version checking
- Support for encrypted backups
- Multi Database support
- File position support
And as always we’re interested in what the community wants, so please let us know what you’d like to see. Feel free to drop a comment below, or join us on the DBAtools slack channel at https://sqlcommunity.slack.com/messages/dbatools/ (If you’re not already on slack you’ll need an ‘invitation’, so pop over to https://dbatools.io/slack and we’ll sort you out
If you run into any problems then please raise an issue on Github and we’ll get right on it.
More info on the DBATools project can be found at:
Website: DBATools
Twitter: psdbatools
Github: DBATools Repository
Leave a Reply