simplifying disaster recovery with dbatools
Over the weekend, I presented a session in Glasgow called doomsday prepping with dbatools at SQLGLA, a community event hosted by Craig Porteous, sql_bob and Louise Paterson.
It was a lot of fun, even though the audience put no effort into winning the MRE give away 😂
Now I've got some ready-to-eat beef tacos when the zombie apocalypse hits!
down to business
When we talk about Disaster Recovery or DR, it's often coupled with the term High Availability or HA. Here are some definitions from my graduate course on HADR.
high availability
- Deals with minor outages, and failover solutions are automated
- The goal is to restore full system functionality in a short time
disaster recovery
- Deals with major outages such as natural and man-made disasters
- Focuses on manual processes and procedures to restore systems back to their original state
- Characterized by a phased approach to restoring the primary site
In the context of SQL Server, HA would be Availability Groups (AG), Failover Clustering (FCI), Log Shipping and more. I won't be addressing High Availability in this post, however.
disaster recovery
why
There are a number of articles discussing the importance of disaster recovery. Here are the three I list in my presentation:
- Certain federal regulations require development of DR plans
- Business partners and customers often demand proof of disaster recovery plans
- Supporting ongoing availability of IT services for business continuity
While I can't find the original reference to cite, I once read in my HADR class that Cleveland State University did a study about the importance of DR, and they found that:
A company that experiences a computer outage lasting more than 10 days will never fully recover financially. Within five years, 50% of those companies will be out of business.
That's insane! And a very solid reason to have a well-tested DR plan.
who
You ever read that story on reddit about the kid who accidentally dropped the production database on his first day on the job? The CTO fired him and threatened legal action. As if.
Companies like Amazon correctly recognize that such disasters are a team effort and disaster recovery is the responsibility of the entire organization.
Here's how you can do your part.
sql server disaster recovery
Tracy Boggiano has an awesome, in-depth presentation about DR titled Disaster Recovery: Where to Begin that I recommend checking out. It was the primary source for my own research.
databases
When it comes to SQL Server and Disaster Recovery, Microsoft offers a number of options.
Backup/Restore
Replication
Log Shipping
Mirroring
Multi-site Failover Clustering
Availability Groups
Tracy's slide deck didn't mention it, but Bacpacs and Dacpacs are a potential option as well.
scenarios
the faster you want to get data back, the more you will pay
Need a quick DR solution? Use Ola Hallengren's free and open source SQL Server Maintenance Solution to schedule your backups, then use robocopy to mirror those backups to a secondary data center or the cloud.
You can reliability recover your data this way but it won't be immediate.
If you need to recover your data far faster, you can use Distributed Availability Groups. This method is faster but far more costly, because you'd potentially need:
Another data center
More SQL Server licenses
More Windows licenses
More resources & storage
More support staff
everything else
Microsoft rightly places a lot of emphasis on database DR, but what about everything else? Things like:
Logins
SQL Agent
Extended Events
Linked Servers
Credentials
Audit
sp_configure
Central Management Server
Database Mail
System Triggers
Endpoints
Custom errors
Replication
Availability Groups
How do you DR these? You can backup the required databases for some things - like msdb restores everything in Agent. Or, if it's available, you can right-click hundreds of objects, one-by-one, and export them.
introducing simplified disaster recovery
dbatools can help ease your DR, all in one convenient command. No, not good ol' Export-DbaScript which is essentially the command line equivalent of the screenshot above.
Now, dbatools offers a whole new command, written specifically for the DR presentation: Export-DbaInstance
Export-DbaInstance is a wrapper for over 50 export commands. This is similar to Start-DbaMigration which is a wrapper for a bunch of copy commands.
The databases export is an export of all the restore commands from the last log backup. So full, diff and logs.
RESTORE DATABASE [anotherdb] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\anotherdb\FULL\WORKSTATION$SQL2016_anotherdb_FULL_20180914_002533.bak' WITH FILE = 1, MOVE N'anotherdb' TO N'M:\DATA\anotherdb.mdf', MOVE N'anotherdb_log' TO N'M:\DATA\anotherdb_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [anotherdb] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\anotherdb\DIFF\WORKSTATION$SQL2016_anotherdb_DIFF_20180914_002539.bak' WITH FILE = 1, MOVE N'anotherdb' TO N'M:\DATA\anotherdb.mdf', MOVE N'anotherdb_log' TO N'M:\DATA\anotherdb_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE LOG [anotherdb] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\anotherdb\LOG\WORKSTATION$SQL2016_anotherdb_LOG_20180914_002545.trn' WITH FILE = 1, MOVE N'anotherdb' TO N'M:\DATA\anotherdb.mdf', MOVE N'anotherdb_log' TO N'M:\DATA\anotherdb_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [anotherdb] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\anotherdb\LOG\WORKSTATION$SQL2016_anotherdb_LOG_20180914_002551.trn' WITH FILE = 1, MOVE N'anotherdb' TO N'M:\DATA\anotherdb.mdf', MOVE N'anotherdb_log' TO N'M:\DATA\anotherdb_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [anotherdb] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\anotherdb\LOG\WORKSTATION$SQL2016_anotherdb_LOG_20180914_002557.trn' WITH FILE = 1, MOVE N'anotherdb' TO N'M:\DATA\anotherdb.mdf', MOVE N'anotherdb_log' TO N'M:\DATA\anotherdb_log.ldf', NOUNLOAD, STATS = 10 RESTORE DATABASE [db1] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\db1\FULL\WORKSTATION$SQL2016_db1_FULL_20180914_002533.bak' WITH FILE = 1, MOVE N'db1' TO N'M:\DATA\db1.mdf', MOVE N'db1_log' TO N'M:\DATA\db1_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [db1] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\db1\DIFF\WORKSTATION$SQL2016_db1_DIFF_20180914_002540.bak' WITH FILE = 1, MOVE N'db1' TO N'M:\DATA\db1.mdf', MOVE N'db1_log' TO N'M:\DATA\db1_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE LOG [db1] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\db1\LOG\WORKSTATION$SQL2016_db1_LOG_20180914_002545.trn' WITH FILE = 1, MOVE N'db1' TO N'M:\DATA\db1.mdf', MOVE N'db1_log' TO N'M:\DATA\db1_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [db1] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\db1\LOG\WORKSTATION$SQL2016_db1_LOG_20180914_002552.trn' WITH FILE = 1, MOVE N'db1' TO N'M:\DATA\db1.mdf', MOVE N'db1_log' TO N'M:\DATA\db1_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [db1] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\db1\LOG\WORKSTATION$SQL2016_db1_LOG_20180914_002557.trn' WITH FILE = 1, MOVE N'db1' TO N'M:\DATA\db1.mdf', MOVE N'db1_log' TO N'M:\DATA\db1_log.ldf', NOUNLOAD, STATS = 10 RESTORE DATABASE [dbwithsprocs] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\dbwithsprocs\FULL\WORKSTATION$SQL2016_dbwithsprocs_FULL_20180914_002534.bak' WITH FILE = 1, MOVE N'dbwithsprocs' TO N'M:\DATA\dbwithsprocs.mdf', MOVE N'dbwithsprocs_log' TO N'M:\DATA\dbwithsprocs_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [dbwithsprocs] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\dbwithsprocs\DIFF\WORKSTATION$SQL2016_dbwithsprocs_DIFF_20180914_002540.bak' WITH FILE = 1, MOVE N'dbwithsprocs' TO N'M:\DATA\dbwithsprocs.mdf', MOVE N'dbwithsprocs_log' TO N'M:\DATA\dbwithsprocs_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE LOG [dbwithsprocs] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\dbwithsprocs\LOG\WORKSTATION$SQL2016_dbwithsprocs_LOG_20180914_002545.trn' WITH FILE = 1, MOVE N'dbwithsprocs' TO N'M:\DATA\dbwithsprocs.mdf', MOVE N'dbwithsprocs_log' TO N'M:\DATA\dbwithsprocs_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [dbwithsprocs] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\dbwithsprocs\LOG\WORKSTATION$SQL2016_dbwithsprocs_LOG_20180914_002552.trn' WITH FILE = 1, MOVE N'dbwithsprocs' TO N'M:\DATA\dbwithsprocs.mdf', MOVE N'dbwithsprocs_log' TO N'M:\DATA\dbwithsprocs_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [dbwithsprocs] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\dbwithsprocs\LOG\WORKSTATION$SQL2016_dbwithsprocs_LOG_20180914_002557.trn' WITH FILE = 1, MOVE N'dbwithsprocs' TO N'M:\DATA\dbwithsprocs.mdf', MOVE N'dbwithsprocs_log' TO N'M:\DATA\dbwithsprocs_log.ldf', NOUNLOAD, STATS = 10 RESTORE DATABASE [distribution] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\distribution\FULL\WORKSTATION$SQL2016_distribution_FULL_20180914_002534.bak' WITH FILE = 1, MOVE N'distribution' TO N'M:\DATA\distribution.MDF', MOVE N'distribution_log' TO N'M:\DATA\distribution.LDF', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [distribution] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\distribution\DIFF\WORKSTATION$SQL2016_distribution_DIFF_20180914_002540.bak' WITH FILE = 1, MOVE N'distribution' TO N'M:\DATA\distribution.MDF', MOVE N'distribution_log' TO N'M:\DATA\distribution.LDF', NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [master] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\master\FULL\WORKSTATION$SQL2016_master_FULL_20180914_002533.bak' WITH FILE = 1, MOVE N'master' TO N'M:\DATA\master.mdf', MOVE N'mastlog' TO N'M:\DATA\mastlog.ldf', NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [model] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\model\FULL\WORKSTATION$SQL2016_model_FULL_20180914_002533.bak' WITH FILE = 1, MOVE N'modeldev' TO N'M:\DATA\model.mdf', MOVE N'modellog' TO N'M:\DATA\modellog.ldf', NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [msdb] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\msdb\FULL\WORKSTATION$SQL2016_msdb_FULL_20180914_002534.bak' WITH FILE = 1, MOVE N'MSDBData' TO N'M:\DATA\MSDBData.mdf', MOVE N'MSDBLog' TO N'M:\DATA\MSDBLog.ldf', NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [shipped] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\shipped\FULL\WORKSTATION$SQL2016_shipped_FULL_20180914_002534.bak' WITH FILE = 1, MOVE N'shipped' TO N'M:\DATA\shipped.mdf', MOVE N'shipped_log' TO N'M:\DATA\shipped_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE DATABASE [shipped] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\shipped\DIFF\WORKSTATION$SQL2016_shipped_DIFF_20180914_002540.bak' WITH FILE = 1, MOVE N'shipped' TO N'M:\DATA\shipped.mdf', MOVE N'shipped_log' TO N'M:\DATA\shipped_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 RESTORE LOG [shipped] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\shipped\LOG\WORKSTATION$SQL2016_shipped_LOG_20180914_002545.trn' WITH FILE = 1, MOVE N'shipped' TO N'M:\DATA\shipped.mdf', MOVE N'shipped_log' TO N'M:\DATA\shipped_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [shipped] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\shipped\LOG\WORKSTATION$SQL2016_shipped_LOG_20180914_002552.trn' WITH FILE = 1, MOVE N'shipped' TO N'M:\DATA\shipped.mdf', MOVE N'shipped_log' TO N'M:\DATA\shipped_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 RESTORE LOG [shipped] FROM DISK = N'\\localhost\backups\WORKSTATION$SQL2016\shipped\LOG\WORKSTATION$SQL2016_shipped_LOG_20180914_002557.trn' WITH FILE = 1, MOVE N'shipped' TO N'M:\DATA\shipped.mdf', MOVE N'shipped_log' TO N'M:\DATA\shipped_log.ldf', NOUNLOAD, STATS = 10
Looking good!
And now for a demo
Here is a slightly modified version of the demo I gave in Glasgow, commented for your enjoyment.
# Check out our export/backup commands Get-Command -Name Export-DbaScript -Module dbatools -Type Function Get-Command -Name *export* -Module dbatools -Type Function Get-Command -Name *backup* -Module dbatools -Type Function Get-Command -Name *dbadac* -Module dbatools -Type Function
Let's examine the commands a little more. First up! Export-DbaScript
Start with something simple
Get-DbaAgentJob -SqlInstance workstation\sql2016 | Select -First 1 | Export-DbaScript
Now let's look inside
Get-DbaAgentJob -SqlInstance workstation\sql2016 | Select -First 1 | Export-DbaScript | Invoke-Item
Raw output and add a batch separator
Get-DbaAgentJob -SqlInstance workstation\sql2016 | Export-DbaScript -Passthru -BatchSeparator GO
Get crazy
#Set Scripting Options $options = New-DbaScriptingOption $options.ScriptSchema = $true $options.IncludeDatabaseContext = $true $options.IncludeHeaders = $false $Options.NoCommandTerminator = $false $Options.ScriptBatchTerminator = $true $Options.AnsiFile = $true
The next command will use SQL authentication
first, pipe the password to clipboard as an example
'Zjady7$$$fxzy(&*($1' | clip Get-DbaDbMailProfile -SqlInstance workstation\sql2016 -SqlCredential sqladmin | Export-DbaScript -Path C:\temp\export.sql -ScriptingOptionsObject $options -NoPrefix | Invoke-Item
Now for a few special commands that SMO didn't quite do justice to
Export-DbaSpConfigure -SqlInstance workstation\sql2016 -Path C:\temp\sp_configure.sql
Warning, this will write clear-text passwords to disk
Export-DbaLinkedServer -SqlInstance workstation\sql2016 -Path C:\temp\linkedserver.sql | Invoke-Item
This will write hashed passwords to disk
Export-DbaLogin -SqlInstance workstation\sql2016 -Path C:\temp\logins.sql | Invoke-Item
Other specials, relative to the server itself
Backup-DbaDbMasterKey -SqlInstance workstation\sql2016 Backup-DbaDbMasterKey -SqlInstance workstation\sql2016 -Path \\localhost\backups
What if you just want to script out your restore? Invoke Backup-DbaDatabase or your Maintenance Solution job
Let's create a FULL, DIFF, LOG, LOG, LOG
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL','DatabaseBackup - USER_DATABASES - FULL' Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - DIFF' Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - LOG' Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - LOG' Get-DbaRunningJob -SqlInstance localhost\sql2016
Start-DbaAgentJob -SqlInstance localhost\sql2016 -Job 'DatabaseBackup - USER_DATABASES - LOG' Get-DbaRunningJob -SqlInstance localhost\sql2016
Now export the restores to disk
Get-ChildItem -Directory '\\localhost\backups\WORKSTATION$SQL2016' | Restore-DbaDatabase -SqlInstance localhost\sql2017 -OutputScriptOnly -WithReplace | Out-File -Filepath c:\temp\restore.sql Invoke-Item c:\temp\restore.sql
Speaking of Ola, use his backup script? We can restore an *ENTIRE INSTANCE* with just one line
Get-ChildItem -Directory \\workstation\backups\sql2012 | Restore-DbaDatabase -SqlInstance localhost\sql2017 -WithReplace
Log shipping, what's up - dbatools.io/logshipping
Also supports multiple destinations!
$params = @{ Source = 'localhost\sql2016' Destination = 'localhost\sql2017' Database = 'shipped' BackupNetworkPath= '\\localhost\backups' PrimaryMonitorServer = 'localhost\sql2017' SecondaryMonitorServer = 'localhost\sql2017' BackupScheduleFrequencyType = 'Daily' BackupScheduleFrequencyInterval = 1 CompressBackup = $true CopyScheduleFrequencyType = 'Daily' CopyScheduleFrequencyInterval = 1 GenerateFullBackup = $true Force = $true }
Invoke-DbaDbLogShipping @params
And now, failover to secondary
Invoke-DbaDbLogShipRecovery -SqlInstance localhost\sql2017 -Database shipped
Introducing Export-DbaInstance
Written for #SQLGLA!
Get Pester and drop code at sqlps.io/doomsday
Check that everything exists prior to export
Invoke-Pester C:\github\community-presentations\chrissy-lemaire\doomsday.Tests.ps1
Do it all at once
Export-DbaInstance -SqlInstance workstation\sql2016 -Path \\workstation\backups\DR Invoke-Item \\workstation\backups\DR
It ain't a DR plan without testing
Test-DbaLastBackup -SqlInstance workstation\sql2016
Now let's test the output scripts.
This will also kill SSMS so that I'm forced to refresh, and open it back up
. C:\github\community-presentations\chrissy-lemaire\doomsday-dropeverything.ps1
Check that everything has been dropped
Invoke-Pester C:\github\community-presentations\chrissy-lemaire\doomsday.Tests.ps1
Prep
Stop-DbaService -ComputerName localhost -InstanceName sql2016 -Type Agent Get-DbaProcess -SqlInstance localhost\sql2016 -Database msdb | Stop-DbaProcess
Perform restores and restart SQL Agent
$files = Get-ChildItem -Path \\workstation\backups\DR -Exclude *agent* | Sort-Object LastWriteTime $files | ForEach-Object { Write-Output "Running $psitem" Invoke-DbaQuery -File $PSItem -SqlInstance workstation\sql2016 -ErrorAction Ignore -Verbose }
Start-DbaService -ComputerName localhost -InstanceName sql2016 -Type Agent
Check if everything is back
Invoke-Pester C:\github\community-presentations\chrissy-lemaire\doomsday.Tests.ps1
Excellent! How gorgeous is that Pester test? Well, the output is hard to read, sorry. But the results are magic 🔮
And a YouTube link!
I also had a blast presenting this session virtually for the Portland PowerShell User Group if you'd like to see a recorded demo.
So check out Export-DbaInstance, let me know what you think 👍
If you'd like to see the output of these scripts, you can peruse this repo. I'm not a pro at everything I exported (like replication), so if you've got some suggestions, let me know or create a pull request on GitHub with your enhancements.
Thanks for reading, - Chrissy