Friday, 21 October 2011

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,

@backupStartDate DATETIME
@backup_set_id_start INT
@backup_set_id_end INT
@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

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
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'
999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id

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

= hostname;$backupdir = "E:\SQLBackup\Scripts\Restore\";$sql = new-object "Microsoft.SqlServer.Management.SMO.Server" $server;# Get databases on our server
= $sql.Databases | Where-object {$_.IsSystemObject -eq $false};# generate the restore commandforeach ($db in $databases){
= $backupdir + 'Restore_' + $ + '.sql'
= $db.Name
= 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!
| 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.

1 comment:

  1. Excellent function Rich! I've added a post and reference it on my blog here.


/* add this crazy stuff in so i can use syntax highlighter