Is your SQL environment prepared for a disaster? There are big and small disasters and depending on your perspective big and small can mean different things. Preparing your environment for these special events by exporting SQL Server Configurations for DR purposes is essential.
I am not going to talk about your backup/recovery, HA, RPO, or RTO strategy. My plan is to show you how to protect yourself from the smaller gremlins that might bite your backside when no one is looking.
Have you ever deleted a login by mistake from a hastily typed TSQL script or dropped a list of logins because the “Business” said they are not used anymore? Have you ever made a change to a SQL Server Agent job and then it failed on the next execution. What about that time you changed the Database Mail profile on all of your servers and left your personal account in the script instead of the DBA distribution list.
While each of these examples is not life-threatening, they will strike fear in you depending on how prepared you are to recover the items in question.
Enter the
Export-DbaInstance function from dbatools. This function will export key configuration items from your SQL Server to individual script files.
List of Configurations Exported
- All database restore scripts
- All logins
- All database mail objects
- All credentials
- All objects within the Job Server (SQL Agent)
- All linked servers
- All groups and servers within Central Management Server
- All SQL Server configuration objects (everything in sp_configure)
- All user objects in system databases
- All system triggers
- All system backup devices
- All Audits
- All Endpoints
- All Extended Events
- All Policy Management objects
- All Resource Governor objects
- All Server Audit Specifications
- All Custom Errors (User Defined Messages)
- All Server Roles
- All Availability Groups
Wouldn’t you sleep much better at night knowing that every day you were saving this information in case of a Disaster, big or small?
Let us take a quick look at how the function works.
Export-DbaInstance -SqlInstance "localhostsql2017" -Path "C:DR"
As you can see after I run the command I get a nice tidy directory of scripts. This is from a default run with no special parameters. It will go through each category listed above and if there are configurated values will generate a script file. There is also an
-exclude parameter that can be used if you want to omit a configuration from being scripted.
Here is an example of the TSQL Script that is created. You can take this script and run it on the server it was generated from or another server where you want to deploy the same configuration.
A header is added that shows you when the script was generated and from what server. Then you have all the TSQL you need to get DBMail working again.
I hope this sparks some ideas on how you can use the
Export-DbaInstance function to save your bacon in the future!!! 
Related Info
- Check out the help for Export-DbaInstance
The post Export SQL Server Configurations for DR purposes appeared first on GarryBargsley.com.