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

Domain

Server

Instance Name

Cname

Reference name

SQL Server Edition

Current SQL Server Version

OS

Server Memory MB

SQL Server Memory(Max)

PAE?

Status (Dev/Test/Prod)

VMWare

Logical CPUS

Processor Speed

Collation

Default Language

Recovery Model

TCP Port

Location - Geographical

Description

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

Owner

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

Step 2- Run the Powershell script

I run the powershell script with this command

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.

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.

If you’d like a full version of the dr.sql, contact me and I’ll forward it to you.

Step 3 – The output file

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 reference

Republished from http://www.sqlserver-dba.com.