Powershell and Disaster Recovery preparation – T-SQL Tuesday

14 June,2011 by Jack Vamvas

Powershell and SQL Server is a great mix for Disaster Recovery planning. There are many aspects to database server disaster recovery planning – including: backup storage  and location,  TSQL2sDay150x150
rebuild documentation, installation media, rebuild scripts, DNS and cnames , escalation process and security details

 An important part is maintaining an up to date and accurate SQL Server inventory. I’m not talking about every single detail about the server – but a summary of SQL Server management details . My list includes the items below. It’s a quick reference for disaster recovery.

 Ultimately they are stored in a spreadsheet – which I store at various locations, along with installation media, installation scripts for every SQL Server Instance and escalation process. Another option is direct output to HTML . Read more on Powershell Excel to HTML

List of headers in the SQL Server  inventory

The headers in bold are included in the dr.sql script below



Instance Name


Reference name

SQL Server Edition

Current SQL Server Version


Server Memory MB

SQL Server Memory(Max)


Status (Dev/Test/Prod)


Logical CPUS

Processor Speed


Default Language

Recovery Model

TCP Port

Location - Geographical


SQL Agent Account

SQL Server Service Account

Sys DB Data Location

Sys DB Log Location

User DB Data Location

User DB Log Location

Temp DB location

Monitoring Agent

Backup Schedule


DR documents location

 I maintain the SQL Server Inventory List with Powershell . I can’t gather all the information required from the SQL Server Instances  , as some of it is stored and maintained in asset management databases

The information sources are :

a)an asset management master list

b)the SQL Server Instances

 The asset management master list includes information such as :owner,DR documents location, Location –Geographical,backup schedule .The SQL Server Instances supplies SQL Server specific information.

 I use Powershell to extract data from both sources and create one report.

 The process for extracting the SQL Server specific information is :

 Step 1 – Collate  master list of SQL Server instances and store in a file .

I run a powershell script to query the asset management database , which overwrites and creates a txt file with a list of all known SQL Server Instances

 Dr instances

Step 2- Run the Powershell script

 I run the powershell script with this command

Dr run script 
Let’s drill-down into the  scripts.

 The  Powershell script accepts an input file – in this case “dr.sql” . The powershell script iterates through the list of SQL Server instances . As defined on the value associated with the  $instancePath variable.

 An “invoke-expression”  invokes SQLCMD to logon to every SQL Server, execute the dr.sql script,create the recordset and write out to the output file .

 Only one output file is created – and each iteration adds to the same output file , through the use of >> on line 22.

Dr powershell 
An abbreviated form of the “dr.sql”

 Note:The script uses the undocumented procedure “xp_regread” and is not something I use often . But this script runs under an account with system permissions- and is suitable.


Dr sql 

Step 3 – The output file

The output file is a delimited list of SQL Server instances.

Dr output 

Another script extracts this  information and creates the report , along with the information from the asset management database.

Creating the SQL Server Inventory regularly is part of a larger , regular Disaster Recovery planning. This includes enforcing some DR practise – to ensure all the information is accurate for quick reference

If you want a Poweshell based  range of scripts to manage post DR checkouts use:

Post disaster recovery SQL Server database checkout script

Disaster Recovery Powershell Script to check for failed Windows services



Read More on Powershell and managing SQL configurations

Powershell : SQLCMD and invoke-expression - SQL Server DBA

Find SQL Server tcp port with Powershell

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Powershell and Disaster Recovery preparation – T-SQL Tuesday

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer