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