Yesterday we compared the performance of running backups with T-SQL and with PowerShell. And the conclusion was that on small backups there’s small overhead of building up the
At the end of the day, we shouldn’t have been expecting much of a difference in performance. All PowerShell’s SQLPS module is doing is using SQL Management Objects (SMO) to create T-SQL which is then submitted to SQL Sever. So most of the small performance overhead is purely the work of creating that T-SQL and then connecting to our SQL Server instance to submit it for execution.
We can exploit this feature in 2 ways:
- Rather than executing the T-SQL we can just get PowerShell to give us the scripts so we can run them. This might be something you want to do in case of an important restore if you wanted to confirm the actions before committing.
- Fault finding. SQLPS doesn’t offer the most informative error messages (in fact it quite ofter returns the same one for any error), so by extracting the T-SQL you can quickly see if the logic was correct, or if you’ve forgotten to create part of your backup or restore.
Getting the SQL Script is very simple, it’s just a substitution at the point of restore or backup:
- Switch $backup.SQLBackup for $Backup.Script
- Switch $Restore.SQLRestore for $Restore.Script
- add the -Script swith to Restore-SQLDatabase or Backup-SQLDatabse cmdlets
With a simple example, it’s easy to see what will come out:
Import-Module "SQLPS" -DisableNameChecking
$ServerName = "Server1\SQL2012"
$SQLSvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($ServerName)
$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database
$Db = $SQLSvr.Databases.Item("psdb1")
$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$backup.BackupSetDescription = "Full Backup of "+$Db.Name
$Backup.Database = $db.Name
$BackupName = "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
$BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)
$Backup.Devices.Add($BackupDevice)
$Backup.Script($SQLSvr)
$Backup.Devices.Remove($BackupDevice)
will produce:
BACKUP DATABASE [psdb1] TO DISK = N'c:\psbackups\psdb1_20130924_181155.bak' WITH DESCRIPTION = N'Full Backup of psdb1', NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 10
But if we go for a more complex restore it provides a lot more. As an example let’s modify our Restore-SQLDatabasePointInTime function from Day 20
function Restore-SQLDatabasePointInTimeScript
{param($BackupFiles, [string]$SQLServer, [datetime]$PointInTime=(Get-Date).AddDays(1))
$ScriptOutPut = @()
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$restore.ToPointInTime = get-date($PointInTime) -format "MMM dd, yyyy hh:mm tt"
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
foreach ($backup in $BackupFiles ){
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backup.filepath,$devicetype)
$restore.devices.add($restoredevice) | Out-Null
$restore.FileNumber = $backup.position
$restore.Database = $backup.DatabaseName
$ScriptOutput += $restore.Script($RestoreServer)
if ($backup.LastLSN -eq $BackupFiles[-1].LastLSN){
$ScriptOutput += "Restore database "+$restore.Database.tostring()+" with recovery"
}
$restore.Devices.Remove($restoredevice) | Out-Null
}
return $scriptoutput
}
And then call the same sequence of functions for our point in time restore operation, except substituting our new function at the very end. Then we can see that we get a very handy amount of code built for us:
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_3_18.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_.bcf' WITH FILE = 4, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_5_19.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_5_20.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_5_21.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_5_22.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_5_23.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_0.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_1.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_2.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_3.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_4.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_5.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_6.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_7.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_8.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_9.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
RESTORE DATABASE [PointInTime] FROM DISK = N'C:\piit\PointInTime_6_10.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'Sep 20, 2013 05:32 PM'
Restore database PointInTime with recovery
It is fact fine to do the restore, and retrieve the script during the same operation, so we could modify our original function to include the .Script() method as well, or make it optional via a switch or variable. Collecting the scripts for every restore/backup and then storing it would mean that you’d have a head start when trying to debug issues, or as more information to provide for auditors.
As the PowerShell submissions are ultimately just T-SQL you can use all your normal tools to monitor or control them.
Tomorrow we’ll look at
This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.
