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,
        
[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.

1 comment:

  1. Excellent function Rich! I've added a post and reference it on my blog here. http://sqldavel.blogspot.com/2013/05/better-script-to-generate-restore.html

    ReplyDelete

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