dbatools

Happy Belated World Backup Day

Chrissy LeMaire • • Updated:

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.

$options = New-DbaScriptingOption
$options.ScriptDrops = $false
$options.WithDependencies = $true
Get-DbaDbTable -SqlInstance sql2017 -Database perfstore | Export-DbaScript -ScriptingOptionsObject $options

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

Get-DbaAgentJob -SqlInstance sql2017 |
    ForEach-Object {
        Export-DbaScript -InputObject $_ -Path "C:\temp\output\sql2017-$($_.Name).sql"
    }

Other Commands to Ease Your Recovery Process

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

  • Backup-DbaDatabase
  • Backup-DbaDbMasterKey
  • Backup-DbaDbCertificate
  • Copy-DbaAgentAlert
  • Copy-DbaAgentJobCategory
  • Copy-DbaAgentJob
  • Copy-DbaAgentOperator
  • Copy-DbaAgentProxy
  • Copy-DbaAgentSchedule
  • Copy-DbaBackupDevice
  • Copy-DbaRegServer
  • Copy-DbaCredential
  • Copy-DbaCustomError
  • Copy-DbaDatabase
  • Copy-DbaDbAssembly
  • Copy-DbaDbMail
  • Copy-DbaEndpoint
  • Copy-DbaXESession
  • Copy-DbaLinkedServer
  • Copy-DbaLogin
  • Copy-DbaDbQueryStoreOption
  • Copy-DbaResourceGovernor
  • Copy-DbaInstanceAudit
  • Copy-DbaInstanceAuditSpecification
  • Copy-DbaInstanceTrigger
  • Copy-DbaSpConfigure
  • Copy-DbaDataCollector
  • Copy-DbaPolicyManagement
  • Copy-DbaAgentServer
  • Copy-DbaSsisCatalog
  • Copy-DbaSystemDbUserObject
  • Copy-DbaDbTableData
  • Copy-DbaXESessionTemplate
  • Export-DbaAvailabilityGroup
  • Export-DbaDacPackage
  • Export-DbaDiagnosticQuery
  • Export-DbaExecutionPlan
  • Export-DbaLogin
  • Export-DbaUser
  • Export-DbaXECsv
  • Export-DbaXESessionTemplate
  • Find-DbaBackup
  • Format-DbaBackupInformation
  • Get-DbaBackupDevice
  • Get-DbaDbBackupHistory
  • Get-DbaBackupInformation
  • Get-DbaDbSnapshot
  • Get-DbaLastBackup
  • Get-DbaDbLogShipError
  • Get-DbaDbRestoreHistory
  • Import-DbaCsv
  • Import-DbaPfDataCollectorSetTemplate
  • Import-DbaSpConfigure
  • Import-DbaXESessionTemplate
  • Invoke-DbaAdvancedRestore
  • Invoke-DbaDbLogShipping
  • Invoke-DbaDbLogShipRecovery
  • Measure-DbaBackupThroughput
  • New-DbaDbSnapshot
  • New-DbaLogShippingPrimaryDatabase
  • New-DbaLogShippingPrimarySecondary
  • New-DbaLogShippingSecondaryDatabase
  • New-DbaLogShippingSecondaryPrimary
  • Publish-DbaDacPackage
  • Read-DbaBackupHeader
  • Remove-DbaBackup
  • Remove-DbaDbSnapshot
  • Restore-DbaBackupFromDirectory
  • Restore-DbaDatabase
  • Restore-DbaDbCertificate
  • Restore-DbaFromDatabaseSnapshot
  • Select-DbaBackupInformation
  • Start-DbaMigration
  • Test-DbaBackupInformation
  • Test-DbaLastBackup
  • Test-DbaDbLogShipStatus

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