Admin: Generate Restore Scripts

In order to speed up the recovery process of database (and to give the unintentional DBAs in my organsiation) a helping hand, I recently started experimenting with generating restore scripts for our production databases.

As is my wont, my technology preference was again Powershell (although it did make use of T-SQL too) as looping through databases on a server and writing to file are all pretty trivial.

I wrote a simple TVF to generate the restore script for a particular database, based upon the system tables in MSDB and based closely on the script found on MSSQLTIPS.


CREATE FUNCTION [dbo].[tvf_GetRestoreCommands](@DatabaseName SYSNAME)RETURNS
    @AllCommands TABLE (
        [backup_set_id] INT NULL,
        [Command] NVARCHAR (MAX) NULL
        )AS
BEGIN

   DECLARE @backupStartDate DATETIME
   DECLARE @backup_set_id_start INT
   DECLARE @backup_set_id_end INT
   DECLARE @IncludeMoveClause BIT = 1
   DECLARE @MoveClause NVARCHAR(255) = ''

   -- get the most recent full backup
   SELECT @backup_set_id_start = MAX(backup_set_id)
   FROM  msdb.dbo.backupset
   WHERE database_name = @databaseName
       AND TYPE = 'D'

   SELECT @backup_set_id_end = MIN(backup_set_id)
   FROM  msdb.dbo.backupset
   WHERE database_name = @databaseName AND TYPE = 'D'
   AND backup_set_id > @backup_set_id_start

   IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

   -- do you want to include the move clause in case of having to go to a different server
   IF @IncludeMoveClause = 1
       SELECT @MoveClause = COALESCE(@MoveClause + ',','') + ' MOVE ' + QUOTENAME(name,'''') + ' TO ' + QUOTENAME(physical_name, '''')
       FROM sys.master_files
       WHERE database_id = DB_ID(@databaseName);

   INSERT INTO @AllCommands
       SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
              + mf.physical_device_name + ''' WITH NORECOVERY, STATS = 5 ' + @MoveClause
       FROM    msdb.dbo.backupset b
           INNER JOIN msdb.dbo.backupmediafamily mf
               ON b.media_set_id = mf.media_set_id
       WHERE b.database_name = @databaseName
                 AND b.backup_set_id = @backup_set_id_start
       UNION
       SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
              + mf.physical_device_name + ''' WITH NORECOVERY'
       FROM    msdb.dbo.backupset b,
                  msdb.dbo.backupmediafamily mf
       WHERE    b.media_set_id = mf.media_set_id
               AND b.database_name = @databaseName
               AND b.backup_set_id >= @backup_set_id_start
               AND b.backup_set_id < @backup_set_id_end
               AND b.TYPE = 'L'
       UNION
       SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
       ORDER BY backup_set_id
      
   RETURN
END

This was the sort of thing I needed and then all I was left to do was write a powershell script to execute this command for each database and persist the results to a file:

# Load the SQL Management Objects assembly (Pipe out-null supresses output)[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-nulladd-pssnapin SqlServerCmdletSnapin100

$server = hostname;$backupdir = "E:\SQLBackup\Scripts\Restore\";$sql = new-object "Microsoft.SqlServer.Management.SMO.Server" $server;# Get databases on our server
$databases = $sql.Databases | Where-object {$_.IsSystemObject -eq $false};# generate the restore commandforeach ($db in $databases){
   $filePath = $backupdir + 'Restore_' + $db.name + '.sql'
   $dbname = $db.Name
  
   $sqlresults = Invoke-Sqlcmd -Query "SELECT [Command] FROM GlobalDB.dbo.tvf_GetRestoreCommands('$dbname')" -ServerInstance $sql_server

   # nasty hack here to force the output to fit in. Hope my restore command isn't > 3000 chars!
   $sqlresults | out-file -filepath $filepath -width 3000
}

The key thing to note here is that I've created the TVF in a central database GlobalDB which I use for admin/logging tasks on the server. This saves me from having to keep a copy of the script in each database (although I could achieve the same behaviour by installing it into the Master database or MSDB database).

Also, there is reference to a hack which I talked about in a previous post.

The final part of the jigsaw was being able to schedule this to run every time there was a backup so it would pickup the latest backup sets. SQL Agent in SQL2008 has a Powershell step but this will actually execute code held within the SQL job. For me, I want to be able to manage my powershell scripts independantly (keeping them under source control etc) so really, I just want to point SQLAgent at a script and execute it. Turns out this can be achieved using the CmdExec step:

powershell "& D:\SQLAdmin\CreateDBRestoreCommands.ps1"

The main downside to this is the permissions as running this Job Step type will mean the job runs under the context of the SQL Agent and, unless I set up a proxy, I will need to grant some database permissions (SELECT on the TVF) to the SQL Agent login account. As a quick fix, I granted SELECT permissions on the TVF to the Public role and off we went.