happy belated world backup day

Happy Belated World Backup Day! I wish it was Backup and Restore Day, Test Your Backups Day, or World Recoverability Day, but alas.

I’m currently working on my portion of the effort to get dbatools to 1.0 – integration tests for our commands. And while I wait for AppVeyor to run all of our tests, I’ve got a few minutes to post.

Initially, I wanted to highlight our Backup/Restore commands but there are sooo many, it’d take a few days to write about them. So today, I’m going to focus on Export-DbaScript or I’ll never get this out the door 😉.

Export-DbaScript

Export-DbaScript exports SMO or “SQL Management Objects“. SMO is what powers SQL Server Management Studio and many of the commands in dbatools.

You know how SQL Server 2000’s underlying system tables vary drastically from SQL Server 2017? With SMO, we don’t have to care. $server.Databases is $server.Databases no matter which version we’re working with.

SMO exports do have some limitations. For instance, it doesn’t include the roles and hashed passwords from logins (use Export-DbaLogin for that). For the most part, though, it’s awesome! This is how easy it is to export all of the jobs on all of your servers across your estate:

$servers | Get-DbaAgentJob | Export-DbaScript

$servers is a variable with the servers sql2008\sql2k8, sql2012, sql2014, sql2016 and sql2017. You can specify a path but if you don’t, then we automatically generate the file name and output to the current directory.

Want to see the output? Here are the contents of sql2017-Job-Export-04012018215831.sql.

What else?

Basically, whatever you can script out in SSMS, you can script out using Export-DbaScript.

You can also add extra options using New-DbaScriptingOption, which is a wrapper for Microsoft.SqlServer.Management.Smo.ScriptingOptions.

If you’d like to script out each object to its own file, you can do the following:

Other commands to ease your recovery process

Here are nearly 80 commands that we created to ease recovery or migration.

Backup-DbaDatabase
Backup-DbaDatabaseMasterKey
Backup-DbaDbCertificate
Copy-DbaAgentAlert
Copy-DbaAgentCategory
Copy-DbaAgentJob
Copy-DbaAgentOperator
Copy-DbaAgentProxyAccount
Copy-DbaAgentSharedSchedule
Copy-DbaBackupDevice
Copy-DbaCentralManagementServer
Copy-DbaCredential
Copy-DbaCustomError
Copy-DbaDatabase
Copy-DbaDatabaseAssembly
Copy-DbaDatabaseMail
Copy-DbaEndpoint
Copy-DbaExtendedEvent
Copy-DbaLinkedServer
Copy-DbaLogin
Copy-DbaQueryStoreConfig
Copy-DbaResourceGovernor
Copy-DbaServerAudit
Copy-DbaServerAuditSpecification
Copy-DbaServerTrigger
Copy-DbaSpConfigure
Copy-DbaSqlDataCollector
Copy-DbaSqlPolicyManagement
Copy-DbaSqlServerAgent
Copy-DbaSsisCatalog
Copy-DbaSysDbUserObject
Copy-DbaTableData
Copy-DbaXESessionTemplate
Export-DbaAvailabilityGroup
Export-DbaDacpac
Export-DbaDiagnosticQuery
Export-DbaExecutionPlan
Export-DbaLogin
Export-DbaUser
Export-DbaXECsv
Export-DbaXESessionTemplate
Find-DbaBackup
Format-DbaBackupInformation
Get-DbaBackupDevice
Get-DbaBackupHistory
Get-DbaBackupInformation
Get-DbaDatabaseSnapshot
Get-DbaLastBackup
Get-DbaLogShippingError
Get-DbaRestoreHistory
Import-DbaCsvToSql
Import-DbaPfDataCollectorSetTemplate
Import-DbaSpConfigure
Import-DbaXESessionTemplate
Import-SqlSpConfigure
Invoke-DbaAdvancedRestore
Invoke-DbaLogShipping
Invoke-DbaLogShippingRecovery
Measure-DbaBackupThroughput
New-DbaDatabaseSnapshot
New-DbaLogShippingPrimaryDatabase
New-DbaLogShippingPrimarySecondary
New-DbaLogShippingSecondaryDatabase
New-DbaLogShippingSecondaryPrimary
Publish-DbaDacpac
Read-DbaBackupHeader
Remove-DbaBackup
Remove-DbaDatabaseSnapshot
Restore-DbaBackupFromDirectory
Restore-DbaDatabase
Restore-DbaDbCertificate
Restore-DbaFromDatabaseSnapshot
Select-DbaBackupInformation
Start-DbaMigration
Test-DbaBackupInformation
Test-DbaLastBackup
Test-DbaLogShippingStatus

We ❤️ tests

I’ve mentioned it before, but there are few things that are more reassuring than knowing that our commands will restore your data properly. In the post a migration with every commit, you can see that we run a ton of tests with each and every commit to our GitHub repo.

When tests pass, we can automatically merge to our master branch and the PowerShell Gallery (known as CI/CD or Continuous Integration/Continuous Delivery).

So check out this awesome video of all of the tests we run just for Restore-DbaDatabase.

Most of our commands run far fewer tests, but few commands are as important as our backup and restore commands. Thanks to Stuart Moore for making Restore-DbaDatabase so robust!

-Chrissy

5 thoughts on “happy belated world backup day

  1. Greg Norris Reply

    Wow this is brilliant Chrissie.
    Thank you so much for the tireless hours you and the team have put into DBATOOLS.

    I manage around 20 production and anoth 15 nonprod availablity groups and developed a few scripts to keep the user logins in sync across the secondaries.

    Something I havent yet done is sync the jobs. Whilt I generally create/apply the jobs there are a few that are create for and by project teams and they forget to 1. Include a test for primary instance to prevent the job trying to run against the secondary and 2. To run the create script on the secondaries. Consequently they complain if the AG has failed over and their job didn’t update/delete records as expected.

    Again in your debt.
    Greg in Oz

  2. Hiram Reply

    Is there a way to script out the object with a Create or Alter, instead of only Create?

    Thx,
    Hiram

Leave a Reply

Your email address will not be published. Required fields are marked *