Disaster Recovery Reporting scripts

Have you ever been asked a seemingly simple question about a SQL Server configuration, and realize that you do not remember (or do not know) the answer? Some information is critical to rebuilding your SQL Server machine in the event of a disaster. I’ve compiled seven questions, along with how to address them using Transact-SQL queries. You can utilize these scripts wherever you see fit (within custom reporting, stored procedures).
Some qualifications… First, these scripts are to be used with SQL Server 2000 (not 7.0 or 6.5). Also, these scripts are sprinkled with system table and undocumented stored procedure calls. I know this makes me a bad person – but I’ll gladly re-write them for future versions. In the meantime, I just want answers to critical questions. Lastly, these scripts assume you have sysadmin server role permissions.

Question 1: How was your SQL Server instance installed? What service packs have been installed?
Answer:This script will list server name, product level, version, collation, edition, instance name (if it was a named instance), whether the instance was a virtual server (clustered), if full text indexing was installed (not necessarily used), the security type, and the license type. One caveat about the “license” type… This sometimes displays a “Disabled” value. This bug is referenced in the following Microsoft Knowledge Base article: http://support.microsoft.com/default.aspx?scid=kb;en-us;291332.

SELECT SERVERPROPERTY('ServerName') as 'Server Name',
SERVERPROPERTY('ProductLevel') as 'Product Level',
SERVERPROPERTY('ProductVersion') as 'Product Version',
SERVERPROPERTY('collation') as 'Server Collation',
SERVERPROPERTY('edition') as 'SQL Server Edition',
SERVERPROPERTY('InstanceName') as 'Instance Name',
CASE SERVERPROPERTY('IsClustered')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END as 'Is it clustered?',
CASE SERVERPROPERTY('IsFullTextInstalled')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END as 'Full text engine installed?',
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'Mixed'
WHEN 1 THEN 'Integrated security only'
END as 'Security mode',
SERVERPROPERTY('LicenseType') as 'License Type'

Question 2: How is SQL Server configured? For example, is parallelism enabled? Is AWE enabled?
Answer:Run this script to show advanced options (and then disable the viewing of advanced options when finished):
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Question 3: What are the databases on your SQL Server instance, and what is their status?
Answer:Use sp_helpdb to list databases on the SQL Server instance. Check out the status column to see what database options are configured.
EXEC master..sp_helpdb

Question 4: What was the installation path where you installed SQL Server?
Answer:Use xp_regread to view the installation path of SQL Server from the registry.

---For Default Instance
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\Setup', 'SQLPath

'--- For Named Instance
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\\Setup', 'SQLPath'

Question 5: What are the drive letters on your SQL Server machine, and how much space is left?
Answer:Use xp_fixeddrives to view drive letters and their remaining MBs free. EXEC master..xp_fixeddrives

Question 6: Where are all the database files located? Answer:Always a fun question – especially when you find little surprises about bad file placement.
Use this script to identify file locations for all databases on the SQL Server Instance

CREATE TABLE #Files
(
[DatabaseNM] sysname NOT NULL,
[name] [nchar] (128) NOT NULL ,
[fileid] [smallint] NOT NULL ,
[filename] [nchar] (260) NOT NULL ,
[filegroup] [nvarchar] (128) NULL ,
[size] [nvarchar] (18) NULL ,
[maxsize] [nvarchar] (18) NULL ,
[growth] [nvarchar] (18) NULL ,
[usage] [varchar] (9) NOT NULL
)
EXEC master..sp_MSForeachdb 'USE ?
INSERT #Files
SELECT ''?'', name,
fileid,
filename,
filegroup_name(groupid),
CONVERT(nvarchar(15), size * 8) + N'' KB'',
CASE maxsize WHEN -1 THEN N''Unlimited''
else
CONVERT(nvarchar(15), maxsize * 8) + N'' KB'' end,
CASE status & 0x100000 WHEN 0x100000 THEN
CONVERT(nvarchar(3), growth) + N''%''
else
CONVERT(nvarchar(15), growth * 8) + N'' KB'' end,
CASE status & 0x40 WHEN 0x40 THEN ''log only'' else ''data only'' end
FROM sysfiles
ORDER BY fileid'
SELECT *
FROM #Files
ORDER BY DatabaseNM
DROP TABLE #Files

/*---- Status Flage ---
0x100000 = Growth is in percentage, not pages.
0x2 = Disk file.0x40 = Log device
==================================*/

Question 7: Where are the backup files located?Answer:Assuming you are backing up your databases – in a pinch – you will want to know where the backup files exist.

This script shows the location of the latest full database backup for each database being backed up.

SELECT A.database_name as 'DBName',
A.backup_finish_date as 'Backup Finished',
B.physical_device_name as 'Backup Filename'
FROM msdb.dbo.backupset A,
msdb.dbo.backupmediafamily B,
(SELECT database_name,
MAX(backup_finish_date) as 'maxfinishdate'
FROM msdb.dbo.backupset
WHERE Type = 'D' --- D for Full Disk Backup, 'L' for Log Backup
GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id AND
A.backup_finish_date = C.maxfinishdate AND
A.type = 'D'
ORDER BY DBName