sql track: restoring databases with powershell
DESCRIPTION
Build plan of approach to structured point in time restores of databases ( e.g. from Production to QA ) using Powershell as an easy helper tool to ensure all steps are being performed.TRANSCRIPT
Restoring SQL Server databases
using Powershell
Johan Bijnens
#ITPROceed
Johan Bijnens• Who am I ?
– SQLServerCentral.com
– @alzdba
• CERA – KBC Bank (1988 – 1999)– Assembler / Cobol / APS / SAS / JCL
– VSAM / IMSdb / DB2
• ALZ – Ugine&ALZ – Arcelor – ArcelorMittal -– PLI / Cobol / VBS / VB.net
– DB2
– SQLServer (7.0 – 2000 – 2005 – 2008 R2 – 2012 - 2014)– Oracle
/ Powershell
Restoring Databases
using .• Build plan of approach to structured point
in time restores of databases ( e.g. from
Production to QA ) using Powershell as an
easy helper tool to ensure all steps are
being performed.
Think
Know
Backup• Backup:
• Minimise data loss in case of disaster
• RPO: Recovery Point Objective = Data loss
• RTO: Recovery Time Objective = Down time
• RSO: Recovery Service Objective = Disaster infrastructure needed
• SQL server database backup :– Full: Copy of all active pages in your database into a file.
– Differential: Copy of all modified pages since the last Full backup
– Log: Copy of all database transactions since the last Log backup
– Checksum ( Exec sp_configure ‘backup checksum default’,1; Reconfigure )
• What good is a backup if you cannot restore it ?
The Setup• # SQLServer instances have been set up to support the
different QA environments
• Every instance already has the databases pre-configured, SQLUser accounts in place, windows domain groups, …
• Segregation of duties: – different passwords
– Different windows domain groups
• Naming conventions help out to make it all work smooth.
• Keep It Simple & Stupid works !
The Setup
• Dev/QA teams:
– Have databases for which they are responsible
– Request ad-hoc data refresh for a given
database for which they have responsibility.
– point-in-time restore can be requested
– Approval by team leader
SQL Server and Powershell
• SQLPs:– Powershell integration provided by Microsoft SQL
Server since 2008
– SQL 2012 provides module SQLPs
– SQL 2014 enhanced / extended SQLPs
– SQLAgent integration (job step)
- http://sqlpsx.codeplex.com/
SMO vs Powershell ?• SQL Server Management Objects (SMO)
– SSMS
– object hierarchy and the relationships
– http://msdn.microsoft.com/en-us/library/ms162209.aspx
– Namespaces for different areas of functionality within SMO.
– Still needed in case SQLPs• doesn’t cover the topic
• cannot handle it
SMO vs Powershell ?
• SMO namespaces:– http://msdn.microsoft.com/en-us/library/ms162233.aspx
Class Function
Microsoft.SqlServer.Management.Smo Contains instance classes, utility classes, and enumerations
Microsoft.SqlServer.Management.Common Replication Management Objects (RMO) and SMO, such as connection classes.
Microsoft.SqlServer.Management.Smo.Agent SQL Server Agent.
Microsoft.SqlServer.Management.Smo.Wmi WMI Provider.
Microsoft.SqlServer.Management.Smo.RegisteredServers Registered Server. (SSMS)
Microsoft.SqlServer.Management.Smo.Mail Database Mail.
Microsoft.SqlServer.Management.Smo.Broker Service Broker.
SMO vs Powershell ?• SQLPs:
– 46 Cmdlets that facilitate functionality
– Powershell programmers will be able to recognize parallels
– Discovery / get-help
get-command -module sqlps | sort noun, verb | ft -autosize
get-command -module sqlps |
sort noun, verb | Select Noun, Name| ft -autosize
Noun Name
PolicyEvaluation Invoke-PolicyEvaluation
SqlAlwaysOn Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
SqlAuthenticationMode Set-SqlAuthenticationMode
SqlAvailabilityDatabase Add-SqlAvailabilityDatabase
Remove-SqlAvailabilityDatabase
Resume-SqlAvailabilityDatabase
Suspend-SqlAvailabilityDatabase
SqlAvailabilityGroup Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
Remove-SqlAvailabilityGroup
Set-SqlAvailabilityGroup
Switch-SqlAvailabilityGroup
Test-SqlAvailabilityGroup
SqlAvailabilityGroupListener New-SqlAvailabilityGroupListener
Set-SqlAvailabilityGroupListener
SqlAvailabilityGroupListenerStaticIp Add-SqlAvailabilityGroupListenerStaticIp
SqlAvailabilityReplica New-SqlAvailabilityReplica
Remove-SqlAvailabilityReplica
Set-SqlAvailabilityReplica
Test-SqlAvailabilityReplica
SqlBackupEncryptionOption New-SqlBackupEncryptionOption
Sqlcmd Invoke-Sqlcmd
Noun Name
SqlCredential Get-SqlCredential
New-SqlCredential
Remove-SqlCredential
Set-SqlCredential
SqlDatabase Backup-SqlDatabase
Get-SqlDatabase
Restore-SqlDatabase
SqlDatabaseReplicaState Test-SqlDatabaseReplicaState
SqlFirewallRule Add-SqlFirewallRule
Remove-SqlFirewallRule
SqlHADREndpoint New-SqlHADREndpoint
Set-SqlHADREndpoint
SqlInstance Get-SqlInstance
Start-SqlInstance
Stop-SqlInstance
SqlName Decode-SqlName
Encode-SqlName
SqlNetworkConfiguration Set-SqlNetworkConfiguration
SqlSmartAdmin Get-SqlSmartAdmin
Set-SqlSmartAdmin
Test-SqlSmartAdmin
UrnToPath Convert-UrnToPath
Basic functions with regards to
recovery• Get-help Restore-SQLDatabase
• Restore-SqlDatabase -ServerInstance‘server\instance’ -Database ‘Db_Restored’ -BackupFile‘J:\MSSQL12.instance\MSSQL\Backup\DbFull.bak’
• Done! Right ?
Restoring is just a part• Security
– Sqlusers / passwords
– Domain accounts
– Domain groups
• Integrity– Detect physical / logical errors ASAP
– Backup corruption
– Restore corruption
Restore: Preparation• Prevent restores
– Recovery fork• FirstLSN
• LastLSN
• DatabaseBackupLSN
– If point in time is not available
• Source check– Instance
– Database
– Backup• Instance
• Database
• Time frame
• Alternate backup file locations
Restore: Preparation
• Target check
– Instance
– Database
– Disk space
Read Backup file content
• SQLPS 2014: No Get-SQLBackupFileInfo
• Wrap it into a powershell function using …
SMO
Get-SQLBackupFileInfofunction Get-SQLBackupFileInfo {param ([string[]]$BackupFile,
[string]$SQLServer='server\missing',[string]$DbName)
$tmpout = @()$sqlsvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($SQLServer)$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::Fileforeach ($file in $BackupFile){
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($file,$devicetype)$restore.Devices.add($restoredevice)$errcnt=0try{
$Headers = $restore.ReadBackupHeader($sqlsvr) | where-object { $_.DatabaseName -eq $DbName }$Headers | Select @{n='BackupFileName';e={$file}}, *
}catch [System.Exception]{
$errcnt = 1}finally {
if ($errcnt -ne 1){$tmpout += $file
}$errcnt = 0
}$restore.Devices.remove($restoredevice) | out-nullRemove-Variable restoredevice
}return
}
Backup fork
$DBBackupInfo.FamilyGUID -eq $LogBackup.FamilyGUID
Loop over LastLSN sequences$PreviousLogLastLSN = $LogBackup.LastLSN
Check Point-In-Time
foreach ( $BU in $LogBackupInfo ) {
$counter ++$MaxBackupFinishDate = $("{0:yyyy-MM-dd HH:mm:ss.fff}" -f $BU.BackupFinishDate )if ( $BU.BackupFinishDate -ge $dtPIT -and $PITFound -eq $false ) {$MaxBackupFilePointer = $counter$PITFound = $true}
else {write-verbose $('start [{0}] - end [{1}]' -f $("{0:yyyy-MM-dd HH:mm:ss.fff}" -f
$BU.BackupStartDate ), $("{0:yyyy-MM-dd HH:mm:ss.fff}" -f $BU.BackupFinishDate ) )}
}if ( $PITFound -eq $false ) {Write-Error $('Point in Time [{0}] not found in available log backups for database
[{1}] of server [{2}]. Maximum available is [{3}]' -f $PointInTime, $SourceDb,$SourceServer, $MaxBackupFinishDate )break}
PIT
The actual restore
Full Diff Log
Process all collected backup files• Force target dabase offline
– (Get-SqlDatabase -ServerInstance ‘s\i' -Name ‘db‘).SetOffline() • will fail without notification when there are still connections
– $SMOdb.ExecuteNonQuery( $("if exists ( select 1 from sys.databaseswhere name ='{0}' and state = 0 ) Alter database [{0}] set offline with rollback immediate ; " -f $TargetDb ) )
• -NoRecovery
• -Checksum
• -RelocateFile $rfl
• -FileNumber $DBBackupInfo.Position
• -ReplaceDatabase
Post restore operations
• Send-MailMessage
• Sp_changedbowner / ALTER AUTHORIZATION
• Resync Logins and SQLUsers
– Naming conventions
– Do not grant missing accounts !
• DBCC CheckDB
Things start coming together
• Put it in a .PS1 file to be used providing
the parameters
Clear-HostSet-Location $ScriptPath ;& '.\ALZDBA Restore SQL Server database (SQLPS).ps1' -SourceServer ‘S1\I1'-SourceDb 'DB' -TargetServer ‘S2\I2' -TargetDb 'DB'-LogRestore-PointInTimeRestore -PointInTime '2014-05-21 12:00:00'
Thoughts• Individual execution of restores
• Pipe
• Parallel execution– Posh Jobs
– Posh Workflows
$DbNames | % { Set-Location $Script:ScriptPath ; & '.\ALZDBA Restore SQL Server database (SQLPS).ps1'
-SourceServer "$SourceServer" -SourceDb "$_"-TargetServer "$TargetServer" -TargetDb "$_"-LogRestore -PointInTimeRestore -PointInTime $PointInTime;
}
FF & resources
Session evaluations
Thank you
Thank you
“Education is not to fill a bucket, but lighting a fire."
William Butler Yeats
(Irish prose Writer, Dramatist and Poet. Nobel Prize for Literature in 1923. 1865-1939)
Belgium’s biggest IT PRO Conference