Know Backups and Know Restores – SQL Server Restores
A couple of years ago I put together a training session to demonstrate various types of backups and restores. The driving factor behind me building this session was the response I got from talking to a dozen or so DBA’s about disaster recovery. I asked how many have ever performed a restore where you had to apply transaction logs. I expected everyone to raise their hand but I only had one person. This was from a group where everyone was responsible for backup and recovery of their environment.
With something so simple I realized there is a big need for demonstrating these actions so that at least everyone is familiar with the process. I had to spice things up since performing backups and restores are not all that sexy. In my presentation I demonstrate traditional backups using full and log, then full with differential and logs, a file group level backup with piecemeal restore and finally tail log backups.
With the tail log backup I backup the tail end of the transaction log after deleting the data file as well as attaching a transaction log from a database on one virtual machine to a database on another virtual machine and still back up the tail end of the log. This is always well received.
During my presentation I demo a set of scripts that I use to generate the restore script. I share that many have adapted these scripts to their production routines to generate their restore script after every backup. This helps tremendously for restore validations as well as production restores and could also be leveraged in your DR procedures.
I have included a download of my sample databases, scripts and presentation here >>> KnowBackups
12 Comments
Nice article. Very concise scripts that demonstrate the presentation nicely.
Well done !!
I would love to receive training slides on DBA. I graduated a year ago in IT and my concentration was DB
Hi Emmanuel, a couple of the best sources for training would be http://www.sqlpass.org and http://www.pluralsight.com
What do you think about using third-party tools to make schedule database backups? Are they useful or it is better to use T-SQL commands or SSMS?
There are several third party backup tools out there and most work great. I have used Redgate SQL Backup Pro on many occasions, however most of the time Ola Hallengren’s scripts do the job just fine. https://ola.hallengren.com. Redgate was awesome back in the day with having compression and then encryption built into the tool as well as central administration, however with compression available since 2008R2 on Standard and up, and now encryption available on 2014, centralized administration is about the only big sell for a third party tool unless you need encryption on earlier versions of SQL Server.
Yes, I considered this tool, but the price is too big for me. Recently I ran into SqlBackupAndFtp, the price is suitable and the free version works well. What do you think about this tool?
Really appreciate your help.
I have recommended that tool before as a great option to get backups offsite. It works well.
The link to the demos and scripts is broken. Please fix.
This has been corrected.
Link to demo & scrpts is broken. Please fix.
This has been corrected.
@Tim
Thank you very much! I watched your recorded session on SQL Server backups/restores for PASS 2021. Very useful. We’re currently re-evaluating our backup/restore strategies and this will help tremendously.