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,
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.
List of headers in the SQL Server inventory
The headers in bold are included in the dr.sql script below
SQL Server Edition
Current SQL Server Version
Server Memory MB
SQL Server Memory(Max)
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
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 :
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
I run the powershell script with this command
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.
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.
If you’d like a full version of the dr.sql , contact me and I’ll forward it to you.
The output file is a delimited list of SQL Server instances.
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 referenceAuthor: Jack Vamvas (http://www.sqlserver-dba.com)