Introduction

Can you vouch for the integrity of your SQL backups? In other words, do you restore every single backup on a test server for verification?  Chances are you don’t: it’s just too much hassle and problems are rare. But can you afford not to be sure? After all, your backups do constitute your only place to turn if things go really wrong on the database front.  If you want to know, instead of simply trust, that you can rely on your  database backups the script below may be just what you need.

Solution Overview

I wrote a PowerShell script to perform automated database restores. For a given group of SQL servers, it will automatically:

• Query their MSDB tables to locate the most recent full and differential backups;
• Use these backups to restore each  database on the server that executes the script;
• Perform a short DBCC check to verify database consistency;
• Report results;
• Drop the database again.

You do not need to worry about the location of database devices; the script will adapt these to suit the situation on your server. All you need to do is start the script and check the output file or the log table after completion - and even that could be automated.

Requirements

You will need a SQL Server instance with enough disk space to hold the biggest database you want to restore and verify. From that server, you need access to the relevant backup files through a UNC path, with or without an administrative share (e.g. \\machine\e$\backup).  Also, the script requires PowerShell v 2.0 or higher and the SQLPS module (included with SQL 2012) to connect to SQL server. The restore script has been tested against SQL 2012, SQL2008 (R2) and SQL2005.

Walk-through

I have tried to provide enough documentation inside the script so you can get it set up and running without additional instructions. The script starts with a high-level explanation:

.DESCRIPTION

This script is designed to check the integrity of SQL backup files by restoring them, preferably on another server, and submitting them to a quick DBCC check. In this way, you can ensure your backups are ready to be used when you really need them.

In a nutshell, the script will query msdb backup history on a SQL Server or a number of SQL Servers and search for the most recent full and differential backup of each active database on the server(s). Next, it will attempt to restore each of these databases from the backup files found and submit it to a quick DBCC check.

Database device locations are configurable. The restored database and file devices are given a (configurable) suffix. This allows you to restore a copy on the server hosting the original database, although this is not recommended from a performance perspective. Once the results of the restore and DBCC check have been reported, the restored database is again removed.

You can run this solution manually or as a Windows Scheduled Task. Progress and any errors are reported in a log file and in a SQL log table. This script will restore from backup files accessible through a UNC path.

Next, setup is described. First, you choose a server to run the script on (called the ‘Restore Server’ below). Make sure it runs SQL Server, create a management database and do some configuration.  The script uses the SQLPS Module to connect to SQL Server. SQLPS is shipped with SQL Server 2012, so an easy way to obtain it is to download SQL Express 2012 and use that as database engine on the Restore Server.  More info on SQLPS can be found here:  http://sev17.com/2010/07/10/making-a-sqlps-module .  

SETUP INSTRUCTIONS

On the server that runs this script: 

• Login with a Windows account that has access to the MSDB databases and the backup files of the SQL servers you are going to verify.
• Set up a dedicated SQL Server instance (referred to as 'Restore Server' below) with sufficient disk space to contain a copy of your largest database (data, log and full-text indices).

  Data, log and full-text devices can be combined on one drive, or you can map each type to another drive.

  Make sure the SQL version of the Restore Server is at least identical to the highest SQL version used on the server(s) whose backups you want to check/restore.

• In the SQL instance on your Restore Server, create a 5 MB database. Name it 'SQLRestorerDB' or any other name you specify for $SQLRestorerDB in the 'User Defined Parameters' below.

  Give the Windows account running this script dbo rights in this database (the database will contain a log table).

• Copy this script to a dedicated directory on your Restore Server. For the purpose of this example the directory name 'D:\SQLRestorer' is used below.
• Create a file (e.g. 'servers.txt') in the SQLRestorer directory. This file should contain the names of the SQL instance(s) whose backups you want to verify, one per line, unquoted. (For a named instance, use a backslash between server and instance name: server01\sql01).
• If you wish to exclude certain databases, specify them in an execption file (e.g. 'exclusions.txt') in the SQLRestorer directory, one per line. Format: <SQL instance>, <database name>. Do not use quotes.
• Update the 'User Defined Parameters' in the section below to reflect the path and file names used for the server file and the optional exclusion file. Also, update the other User Defined Parameters to meet your local needs.
• Make sure you have PowerShell version 2 or higher installed on your Restore Server.
• Make sure you can load the SQLPS module in Powershell on your Restore Server.
• In PowerShell make sure you can run external scripts, e.g. by issuing the command

  'set-executionpolicy remotesigned -force', or by signing the script.

Make sure the User Defined Parameter correspond to your setup:

### =====  USER DEFINED PARAMETERS  ===== ###
    # SQL server instance name. Need not be changed if your Restore Server runs a default instance.
    # If you use a named SQL instance, supply name: '<server name>\<sql instance name>'
$SqlHost = $env:COMPUTERNAME
    # Path + name of log file. If you run the script as a Scheduled Task,
    # include the full path here or your log file will end up in the Windows System32 folder. 
$LogFile = 'D:\SQLRestorer\SQLRestorer_Log.txt'
    # Path + name of file with database exlusions. Optional, may be commented out.
    # Format of entries: <server_name>, <db_name>.
$ExclusionFile = 'D:\SQLRestorer\exclusions.txt'
    # Path + name of file with server names whose SQL backups you want to restore and verify.
$ServerFile = 'D:\SQLRestorer\servers.txt'
    # Name of database to be used by this script (will contain log table).
    # Database should exist prior to running this script.
$SQLRestorerDB = 'SQLRestorerDB'
    # A suffix added to the (original) database name + physical database files during the restore,
    # to enable the script to restore to the same server (not recommended, but possible..)
$Suffix = '_CHK'
    # Path to hold SQL data files of restored databases. Must exist when script starts.
$NewDataPath = 'D:\SQL_DBS'
    # Path to hold SQL log files of restored databases. Must exist when script starts.
$NewLogPath = 'D:\SQL_DBS'
    # Path to hold SQL full-text indices of restored databases. Must exist when script starts.
$NewFtPath = 'D:\SQL_DBS\FT'
    # Number of days for which history is retained in the log table.
$LogTableHistoryDays = 14

You are now ready to start using the script.

• From a PowerShell commmand line, change directory to the folder where the script resides:

  cd D:\SQLRestorer

• Type its name preceded by '.\' and add the parameter 'DEBUG' for an initial test:

  .\SQLRestorer.ps1 DEBUG

• This will run the script in 'DEBUG' mode: it will write to the log file and log table, but not execute  the actual restore(s) and DBCC check(s).
• In DEBUG mode, the SQL commands for RESTORE and DBCC CHECK that would execute in regular mode are captured in the log file for review.
• Check the log file (defined above in 'User Defined Parameters') and the log table [SQLRestorerLog] for any anomalies. In the log file, problems are indicated by the word ‘ERROR’ at the start of a line. In the log table, errors have a value of ‘1’ in the [Error] column.
• When all is OK, rerun the command without the 'DEBUG' parameter.

  The script will now perform the actual restores and checks.

• After initialization, you should see database devices appearing in the file location(s) defined above and you should see the log file grow.
• When the script is done, check  its log file and/or the log table.
• The log file gets overwritten each time the script runs. The SQL log table retains history for a configurable number of days.

The remainder of the script does not require configuration and is too long to be discussed in detail here. For those of you with a technical interest, the source code is shown below with relevant inline comments. You can download the code from the Resources section below, get things set up, and see how you like it. Any improvements or suggestions are welcome!

Note: If the SQL edition (e.g. Standard/Enterprise)  of your Restore Server is different from that of the other SQL Servers, restores of database that use edition-related features may fail.  In SQL 2008, for example, data compression or vardecimal storage are only supported on Enterprise Edition.