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:

  • Exporting and backing up all required items to disk
  • Moving export files and backups off-site
  • Testing imports and restores on a secondary server
  • Importing and restoring all the required items from disk in the event that a disaster occurs

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.

14.1 Exporting an entire instance

14.1.1 Scripting options

14.1.2 Setting scripting options

14.1.3 Excluding objects

14.2 Granular exports

14.2.1 Using Export-DbaScript

14.3 Special commands

14.4 Exporting server configurations (sp_configure)

14.5 Hands-on lab