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