Backup

LINQPad script to Generate SQL Server Database Restore Script from Ola Hallengren’s Backup Solution

by

Unless you perform regular restores of your database backups, you don’t know that you actually have a valid backup. In a career spanning over 30 years, I’ve seen two occasions where a company was performing backups (or so they thought!) and sending tapes offsite, assuming they were good when in fact the tapes were blank!

The majority of SQL Server installations use Ola Hallengren’s maintenance solution (and certainly all the ones I’ve had anything to do with).

If you are doing regular (5 minutes or less) transaction log backups, a restore might involve applying quite a few transaction logs.

I’ve written a short LINQPad script here which will generate the TSQL to perform a database restore either from a point in time or the latest available, based upon the default locations and naming conventions used by Ola’s backups. It’s Differential backup aware, as well as creating the multiple Transaction Log restore statements. It’s also takes into account where backups are split into separate backup files (which is quite common). You specify the server name, the database name, the root folder where the backups are stored, and either a point in time or the latest.

Disclaimer: Use at your own risk AND test thoroughly!

Example output:

USE [master]

RESTORE DATABASE [AdventureWorks] FROM 
   DISK = N'C:\temp\Backup\K7\AdventureWorks\FULL\K7_AdventureWorks_FULL_20211118_151558.bak'
 WITH NORECOVERY, REPLACE

RESTORE DATABASE [AdventureWorks] FROM 
   DISK = N'C:\temp\Backup\K7\AdventureWorks\DIFF\K7_AdventureWorks_DIFF_20211118_152101.bak'
 WITH NORECOVERY

RESTORE DATABASE [AdventureWorks] FROM 
   DISK = N'C:\temp\Backup\K7\AdventureWorks\LOG\K7_AdventureWorks_LOG_20211118_152226.trn'
 WITH NORECOVERY, STOPAT = '2021-11-21 17:07:22'

RESTORE DATABASE [AdventureWorks] WITH RECOVERY

SQLFrontline Case Study: Failing Backups

by

Over the course of 20 years dealing with SQL server, I’ve come across failing backups more times than I’d like to recall. (And that’s not counting the times there were no backups setup in the first place!)

In the case of failing backups, backups were set up, checked to be working, and then at a later date subsequently failed to notify of backup failures for several reasons (non-exhaustive):

  1. Configuration on the SMTP server changed, such as the allowed IP white list for forwarding, permissions changed, or the actual SMTP server changed.
  2. Virus scanner configuration changed preventing emails to be sent.
  3. AD group permissions changed or SQL server service identities changed.

In all these cases, backups were failing but no one was being alerted and no one was periodically checking the SQL agent logs.

SQLFrontline checks for no backups in the last 7 days, backups done without compression (compressed backups take up less space obviously, but are also faster to backup and restore), backups done without verifying page checksums, and backups done without encryption (if your version of SQL Server supports it). It also checks that you are periodically running DBCC CHECKDB to maintain database integrity, and whether any data corruption has been detected (automatically repaired or otherwise).

SQLFrontline currently performs 62 Reliability checks on each SQL Server you monitor, with 300+ checks performed across the categories of Reliability, Performance, Security, Configuration and Database Design.

Side Note: Another thing to consider is, do you delete older backups BEFORE making sure the latest backup succeeded? If so, you might end up with no recent local backups at all when your backup job starts failing… If you use Ola Hallegren’s maintenance solution scripts, this check is performed correctly for you.