Today I'm putting up my new script that will document your SQL server so that you can recover from a disaster which in this case is a bare metal build. To rebuild everything, you need to have as much documentation about your SQL server as possible. This script will help.
There are 3 stored procedure use to generate the output which are
usp_DR_DBRestoreScript, usp_DR_LinkSrvRebuilt and sp_help_revlogin which are included at the end of the code.
The 3rd stored procedure is actually from Microsoft which will script out the logins
Some code has been placed together with snippets I have found on the internet and would the credit the authors but haven't found them. So if you see a snippets of your code here then I pass on credit to you too.
***Note*** Please test this script before using and before using it for DR proposes. I do not warrently and do not take any responsiblity for it, its misuse or output it creates. Use at your own risk.
The script produces and output from several areas which are listed here:
1) Physical Server Information - Number of CPUs, memory, IP Address, etc.
2) Hard drive space available - in Megabytes
3) SQL Server Information - Server name, version of SQL server, Patch level, etc.
4) SQL Server Port Number Used
5) SQL Server Settings - SP_CONFIGURE
6) Database and Log file Physical Locations - All the information regarding the database(s) location
7) Database Details - All database information
8) Last Backup Dates - What and when the last backups was completed
9) List of SQL Jobs - What jobs execute
10) Failed SQL Jobs - Jobs that have failed before. This is important as the new installation may have the same failures
11) Disabled Jobs - Jobs there but not set to execute
12) SQL Server Services Status - What services were installed and running
13) Link Server Details - What other servers is SQL Server linked to
14) Database Mail Details - See if it is installed and running
15) Database Mirroring Details - Databases mirrored status
16) Database Log Shipping Details
17) Cluster Details - Information on cluster configuration
18) Always On Replication Details. SQL 2012 and newer
I have tested it on SQL Server 2008, 2008R2, 2012. Haven't tested on SQL 2000
Please let me know if you think other information should be added to the script.
Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DMnRRUDJOeXIyRWs/edit?usp=sharing
About Me - Who is DR DBA?
- Who am I?
- Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: firstname.lastname@example.org