14 Preparing for disaster
Disastrous data loss can be caused by a variety of factors, including data center fires, severe weather, human error, or even intentional sabotage. The goal of disaster recovery is to be prepared before these types of disasters strike, because your entire SQL Server instance may no longer be accessible and would need to be rebuilt and restored from the ground up.
Typically, disaster recovery for SQL Server consists of the following four parts:
Fortunately, dbatools makes this once-daunting task easy by simplifying the export routine for essential SQL Server objects such as database restore scripts, logins, credentials, Agent jobs, schedules, linked servers, availability groups, and more.
After exporting your items to files, you can easily save the files to version control and test the restoration to another SQL Server instance on a regular basis. The process is now so straightforward, we perform nightly exports and automate weekly tests.
In this chapter, we’ll be learning about how dbatools can help ease recovering your SQL Server instance from a major disaster and drastically reduce your Recovery Time Objective (RTO) (sqlps.io/drrto), or the time it takes to recover your environment. You’ve already learned how to quickly install a new instance of SQL Server in chapter 13. Now you’ll learn how to prepare to quickly restore your items to a new instance using Export-DbaInstance and Export-DbaScript, should the worst-case scenario occur and your instance becomes unavailable.
14.1 Exporting an entire instance
You may have experience with migrating SQL Server instances from one server to another. If so, you’ve considered what’s left to be migrated after the database restores and login migrations are complete. When recovering an entire SQL Server instance from disk, many of the same considerations must be made. It’s not just about restoring databases from their latest backup: DR includes all of the objects listed in table 14.1.
That’s a long list! Fortunately, dbatools enables you to export T-SQL scripts to recover these objects with just one command: Export-DbaInstance.
Tip If you think this list looks familiar, you’re right! When Export-DbaInstance was designed, we used Start-DbaMigration as the basis. One difference is that Export-DbaInstance does not back up your databases. Rather, it creates the scripts that make it easy to restore the last full, differential, and log files.
You can use this command to export all of the objects in table 14.1 into a set of T-SQL scripts that you should store safely in a place that will be accessible in the case of a disaster. We recommend that you use Azure DevOps repos so that you can store them off-site and have access to them from any internet-connected machine.
If Azure DevOps is not available to you, you need to consider another method of storing these files that will not be affected by the disaster that renders your instance unavailable. Take some time to consider the most suitable solution for your requirements. Should this scenario occur, you will want it to be as easy as possible to perform your recovery.
Using Azure DevOps, GitHub, GitLab, or a similar service will also have the benefit of using source control, enabling you to identify and audit the changes made to the instance, and, further, off-site storage will even help protect against ransomware attacks.
Exporting an entire instance requires the SQL Server to be online, so make sure to perform your exports before a disaster occurs. To export your entire instance settings, run the code in the next listing using your environment’s SQL Server instance and destination path.
Listing 14.1 Exporting the entire instance
On our test laptop, this takes about 100 seconds to run. When you look in the folder, you will see that a directory has been created with the name of the instance and a timestamp. Take a look inside that directory, and see what is created. It is a good idea to familiarize yourself with these files prior to needing them. You can use Get-ChildItem to list the files in this directory, as shown next.
Listing 14.2 The exported files
PS> Get-ChildItem .sql01-11112019080741 Directory: C:gitInstanceExportsql01-11112019080741 Mode LastWriteTime Length Name ----- ------------- ------ ----- -a---- 3/23/2021 4:15 PM 848 audits.sql -a---- 3/23/2021 4:15 PM 342 auditspecs.sql -a---- 3/23/2021 4:15 PM 304 backupdevices.sql -a---- 3/23/2021 4:15 PM 1038 credentials.sql -a---- 3/23/2021 4:15 PM 408 customererrors.sql -a---- 3/23/2021 4:15 PM 2398 databases.sql -a---- 3/23/2021 4:15 PM 2693 dbmail.sql -a---- 3/23/2021 4:15 PM 511 endpoints.sql -a---- 3/23/2021 4:15 PM 9969 extendedevents.sql -a---- 3/23/2021 4:15 PM 9468 linkedservers.sql -a---- 3/23/2021 4:15 PM 8593 logins.sql -a---- 3/23/2021 4:15 PM 3673 policymanagement.sql -a---- 3/23/2021 4:15 PM 11334 regserver.xml -a---- 3/23/2021 4:15 PM 799 resourcegov.sql -a---- 3/23/2021 4:15 PM 562 serverroles.sql -a---- 3/23/2021 4:15 PM 1216 servertriggers.sql -a---- 3/23/2021 4:15 PM 4176 sp_configure.sql -a---- 3/23/2021 4:15 PM 67317 sqlagent.sql -a---- 3/23/2021 4:15 PM 436792 userobjectsinsysdbs.sql
You can see, from the list of files created, that in 100 seconds you have created T-SQL files for 15 different types of objects for your SQL Server instance that you can use to recreate your instance in the case of disaster. Of course, as a DBA, you will want to have a look in these files and examine their contents.
Passwords are exposed It is very important to know that the passwords for the user accounts for the linked servers and the secrets for the SQL Server credentials are exposed in clear text. You need to consider this in association with your business requirements for the safe storage of secrets, such as locked-down permissions. You can see the clear-text password “dbatools.IO” for the linked server login in linkedserver.sql.
If your business prohibits storing clear-text passwords to disk, you will want to specify -Exclude LinkedServers, Credentials. If you are not allowed to store hashed passwords to disk, you will also want to exclude Logins.
If you’re looking for a lightweight, cross-platform solution for database management, we recommend Azure Data Studio (ADS) (aka.ms/azuredatastudio). Although it does not offer all of the features of SQL Server Management Studio (SSMS), it has other worthwhile benefits, including support for native PowerShell and source control support.
In Azure Data Studio, you can use CTRL+K, then CTRL+O to open a folder. Open the folder for the exported instance in Azure Data Studio and click 1-sp_configure.sql. You will see something similar to the output in figure 14.1.
You can look through all of the files in this folder and see the T-SQL that has been created for your instance. You can then add these scripts to your disaster recovery routine to enable you to recreate your instance settings.
14.1.1 Scripting options
The scripts are created using the default SQL Management Object (SMO) options chosen by Microsoft. After evaluating the SQL output, you may discover that the export didn’t perfectly suit your needs. Perhaps the SQL syntax targets the wrong version of SQL Server, or data compression objects weren’t exported. If you’re looking to customize your export scripts, you’re in luck, because you can configure a number of options—the same ones presented by SSMS, as shown in figure 14.2. To get an easy-to-explore visual of the types of changes you can make to your export scripts, open SSMS and go to Tools > Options > SQL Server Object Explorer > Scripting. You can modify these same values within dbatools using the New-DbaScriptingOption command, which generates an object that you can pass to the -ScriptingOption parameter of Export-DbaInstance.
First, you should examine the various scripting objects available to you, along with their default settings, using the same code shown in the following listing.
Listing 14.3 Scripting options
PS> $options = New-DbaScriptingOption PS> $options | Select * FileName : Encoding : System.Text.UnicodeEncoding ScriptForCreateDrop : False ScriptForAlter : False DriWithNoCheck : False IncludeFullTextCatalogRootPath : False SpatialIndexes : False ColumnStoreIndexes : False BatchSize : 1 ScriptDrops : False TargetServerVersion : Version140 TargetDatabaseEngineType : Standalone TargetDatabaseEngineEdition : Unknown ~~~~~~~~~~~~ Output Truncated ~~~~~~~~~~~~ ScriptDataCompression : True ScriptSchema : True ScriptData : False ScriptBatchTerminator : False ScriptOwner : False
14.1.2 Setting scripting options
When you are scripting out objects to create on another instance, you might not want the script to attempt to create objects that already exist. For example, say that the export generates the T-SQL code to create a sqladmin login, but the login already exists on the destination server. The execution would error out and be all ugly. When developing T-SQL, you would avoid this by using the IF NOT EXISTS syntax.
In SSMS and dbatools, the option to check for object existence is set to false by default. You can alter this setting by changing the value of the IncludeIfNotExists property of the scripting object to false, as seen next.
Listing 14.4 Setting the IncludeIfNotExists option
You will ensure that the command uses these options by passing the $options object to the -ScriptingOption parameter of Export-DbaInstance, as demonstrated in the next listing.
Listing 14.5 Exporting an instance with scripting options
This will alter the T-SQL output that is created to include the IF NOT EXISTS statement, and your code can run free of errors stating that the destination objects already exist, as shown in the following code snippet.
Listing 14.6 The server audit T-SQL with IF NOT EXISTS
IF NOT EXISTS (SELECT * FROM sys.server_audits WHERE name = N'STIG_Audit') CREATE SERVER AUDIT [STIG_Audit] TO FILE ( FILEPATH = N'S:MSSQLAUDITS' ,MAXSIZE = 100 MB ,MAX_FILES = 5 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = 'bee53171-bd32-4b4b-b442-5ec0c320e37a' ) ALTER SERVER AUDIT [STIG_Audit] WITH (STATE = ON)
14.1.3 Excluding objects
You might not want to export all of the settings for your instance. If you’re using Export-DbaInstance for dev/test restores, for instance, you may have already created all of the logins you need on the destination domain, and thus, they do not need to be exported.
Fortunately, Export-DbaInstance allows you to exclude any combination of the object types. The -Exclude parameter allows you to exclude the objects seen in table 14.2.
Let’s say that your disaster recovery server has different hardware and your Resource Governor settings would not be suitable for that server. You can exclude the Resource Governor settings by executing the code shown next.
Listing 14.7 Exporting an instance with excluded objects
PS> $splatExportInstance = @{ SqlInstance = "sql01" Path = "C:gitExportInstance" Exclude = "ResourceGovernor" } PS> Export-DbaInstance @splatExportInstance
14.2 Granular exports
Export-DbaInstance wraps a number of dbatools commands into one easy-to-use command. If you need a more granular experience, we offer that as well.
In this section, we will show you how to script out specific objects that you hand-pick yourself, basically replicating the behavior of SSMS’s “Script [object] as > CREATE To > File...” You’ll also get some insight into how we created Export-DbaInstance, because we used very similar commands.
14.2.1 Using Export-DbaScript
Export-DbaScript allows you export T-SQL from commands that output SMO objects, such as Get-DbaAgentJob or Get-DbaDbStoredProcedure. This is similar to right-clicking in SSMS and “Script [object] as,” so anytime you find yourself wanting to use SSMS’s functionality on more than one object, you’ll know how use dbatools instead. See figure 14.3.
By default, the T-SQL output is exported to a file, similar to “Script [object] as > CREATE To > File...,” and shown in the next code listing.
Listing 14.8 Using Export-DbaScript with Get-DbaAgentJob
PS> Get-DbaAgentJob -SqlInstance sql01 | Select-Object -First 1 | Export-DbaScript Directory: C:UserssqldbaDocumentsDbatoolsExport Mode LastWriteTime Length Name ----- ------------- ------ ----- -a--- 8/10/2020 10:13 PM 345 SQL01-20200810221308-s...
You can also export right to the console, similar to “Script [object] as > CREATE To > New Query Editor Window...,” as shown in the following code sample.
Listing 14.9 Exporting to console
PS> Get-DbaDbStoredProcedure -SqlInstance sql01 -Database master | Where-Object Name -eq sp_MScleanupmergepublisher | Export-DbaScript -Passthru /* Created by ADdba using dbatools Export-DbaScript for objects on WORKSTATION at 08/10/2020 22:23:33 See https://dbatools.io/Export-DbaScript for more information */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO create procedure dbo.sp_MScleanupmergepublisher as exec sys.sp_MScleanupmergepublisher_internal GO
PowerShell can even send it right to your clipboard, like “Script [object] as > CREATE To > Clipboard,” shown next. Piping right to the clipboard is one of our favorite things about PowerShell!
Listing 14.10 Sending the output of Export-DbaScript to your clipboard
You may notice we repeatedly say CREATE. Want to DROP or add additional options instead? We support that as well when Export-DbaScript is used in conjunction with New-DbaScriptingOption.
Say, for instance, you examine your disaster recovery runbook and realize that it does not include the SQL Server Audits and audit specifications that have recently been created. You can create the T-SQL files for these objects with an IF NOT EXISTS clause to avoid errors for any existing audits or specifications with New-DbaScriptingOption, as depicted in the next code listing.
Listing 14.11 Scripting SQL Server Audit and audit specification
PS> $options = New-DbaScriptingOption PS> $options.includeifnotexists = $true PS> $splatExportScript = @{ FilePath = "C:gitexportsql01audit.sql" ScriptingOptionsObject = $options } PS> Get-DbaInstanceAudit -SqlInstance sql01 | Export-DbaScript @splatExportScript Directory: C:gitsql01export Mode LastWriteTime Length Name ----- ------------- ------ ----- -a---- 16/11/2019 14:59 563 audit.sql PS> $splatExportScript = @{ FilePath = "C:gitexportsql01auditspec.sql" ScriptingOptionsObject = $options } PS> Get-DbaInstanceAuditSpecification -SqlInstance sql01 | Export-DbaScript @splatExportScript Directory: C:gitexportsql01 Mode LastWriteTime Length Name ----- ------------- ------ ----- -a---- 16/11/2019 14:59 563 auditspec.sql
You can do this with any SMO object that you get from dbatools. How can you tell whether the results you see are SMO objects? One way is to use PowerShell’s Get-Member command to see if the TypeName includes Microsoft.SqlServer.Management.Smo.
In the “Try It Now 14.2” code, you can see that Get-DbaAgentJob is an ideal command to use with Export-DbaScript, whereas Get-DbaSpConfigure is not.
14.3 Special commands
While Export-DbaScript works for a majority of SMO objects, some object exports have their own dedicated commands because they required additional considerations or programming. Exporting the Login SMO object, for example, exports fake passwords and disables the login by default. This was a design decision by Microsoft that resulted in the SQL Server team creating and sharing sp_help_revlogin in a Microsoft Support article to alleviate the burden of migrating logins. sp_help_revlogin exports a hashed password and doesn’t disable the login, but it also does not copy permissions. Export-DbaLogin, on the other hand, exports hashed passwords, permissions, and properties like language, and it doesn’t disable the login.
Other special Export-Dba commands include the following:
You can use any of these commands to export those objects to a T-SQL script. You saw examples of Export-DbaLogin in chapter 9.
14.4 Exporting server configurations (sp_configure)
We saw in the earlier “Try it now 14.2” exercise that Get-DbaSpConfigure is not a candidate to be exported to Export-DbaScript. But, in the case of a disaster, you will want to ensure that all of your sp_configures on the new instance match the settings on your old instance. Use the Export-DbaSpConfigure command to export them, as shown here.
Listing 14.12 Exporting sp_configure
PS> $splatExportSpConf = @{ SqlInstance = "sql01" FilePath = "C:gitExportInstancespconfigure.sql" } PS> Export-DbaSpConfigure @splatExportSpConf
With this file saved safely and available in the case of a disaster, you can then ensure that your new instance has the same settings by exporting the updated settings, as shown here.
Listing 14.13 Importing sp_configure
PS> $splatExportSpConf = @{ SqlInstance = "sql01,15591" SqlCredential = "sqladmin" Path = "C:gitExportInstancespconfigure.sql" } PS> Import-DbaSpConfigure @splatExportSpConf [14:31:27][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'show advanced options' , 1; RECONFIGURE WITH OVERRIDE. [14:31:27][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'recovery interval (min)' , 0;. [14:31:27][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'allow updates' , 0;. [14:31:27][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'user connections' , 0;. [14:31:27][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'locks' , 0;. ~~~~~~~~~~~~ Output Truncated ~~~~~~~~~~~~ [14:31:28][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'allow polybase export' , 0;. [14:31:28][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'show advanced options' , 0;. [14:31:28][Import-DbaSpConfigure] Successfully executed RECONFIGURE WITH OVERRIDE. WARNING: [14:31:28][Import-DbaSpConfigure] Some configuration options will be updated once SQL Server is restarted. [14:31:28][Import-DbaSpConfigure] SQL Server configuration options migration finished.
You will notice that the output includes the warning that some of these options will require SQL to be restarted.
You can import the sp_configure settings live This chapter is about disaster recovery, where you would not have access to the original or source instance. You can use the Import-DbaSpConfigure with the -Source and -Destination parameters to copy the settings from one instance to another.
In this chapter, you have learned how to use dbatools to export the configuration settings for your instances into T-SQL files, as well as any other objects that you wish, for use in a disaster recovery scenario. This will be useful for reducing the time that you would have to spend recovering from any unfortunate situation where data and configuration loss have occurred. For more information on disaster recovery, visit dbatools.io/dr. It’s an in-depth article that even includes a video presentation and a demo!
14.5 Hands-on lab
Let’s practice what you just read about in this chapter. See if you can complete the following tasks:
-
Export all of the configuration settings except for Policy-Based Management and the Resource Governor for an instance.
Unsure of the answers? You can check your work at dbatools.io/answers.