Hi All,
I'm sure most of you who play with SharePoint also wear the DBA hat from time to time (or the other way around). I have been working with a customer who has hundreds of instances scattered across the enterprise. This script customized to answer several questions, but mainly to report on the patching level of each instance. Note that this is not a discovery tool; you have to know the instance names ahead of time, and have to have access to them.
Anyway, I find it comes in handy when you have lots of instances to manage.
SQL Server Auditing Tool
This is a custom VBScript I developed to quickly audit a number of SQL Server instances at once. The script uses SQL DMO libraries to connect to SQL 2000 and 2005 instances on the network, using Windows authentication to connect and gather properties of each instance to include:
- Instances:
- Name and SQL version (also indicating service pack)
- When the instance was installedo The version and service pack of the Windows OS
- Databases:
- Name, file location, file size
- Tally of disk space consumed by all databases in instance
Paste the following code into a text file and save it as DBChecker.VBS. You can then call it from a command line using CSCRIPT. Don't just double-click on the VBS file because it will output the results in a series of Windows popups, which is annoying as hell.
You can “pipe” the output to a text file, e.g. CSCRIPT DBChecker.vbs sql01sharepoint sql01 igerpaw sql01 igerpawtest > dbcheck.txt
You can then paste the comma-delimited text into word and convert it to a table for reporting. Or you can open the txt file in Excel.
To run, this script requires a local instance of SQL Server or MSDE. It uses those libraries to instantiate. If you have SQL 2000/MSDE, it will only connect to other SQL 2000 instances. If you want to connect to SQL 2005 instances, you have to have SQL 2005 (which can connect to both SQL 2000 and 2005 instances).
' Script name: DBChecker.VBS
' — SQL Server auditing script
' — Version 1.3a Written 5-16-08 by Greg Burns
'
' Description:
' This script enumerates all databases in a SQL server instance,
' then calculates the size of each database, and displays the
' path to the database files on disk. The Version of each instance is
' also displayed.
'
' You can specify a
' space-delimited list of SQL Server instances and the script will
' process all of them in sequence.
'
' Usage: CSCRIPT VBSIZE.VBS [<instancename>] [<instancename>] [<...n>]
' Notes:
' 1. Instance name uses the following syntax: HOSTNAMEINSTANCENAME
' 2. To capture output to text file, use the redirect,
' e.g. "CSCRIPT DBChecker.VBS myservermyinstance > dbcheck.txt
' 3. Leave <instancename> blank to assess only the default SQL instance.
' 4. This version of DBChecker uses Windows Authentication (SSPI) to
' connect to the SQL instance. If you attempt to
' connect to a SQL instance or DB for which you do not have
' login access, the script will abort with an
' 'unspecified error.'
wscript.echo "Database Size and Location Reporter"
wscript.echo " Size = Total Size of Data File + Transaction Log of DB."
wscript.echo "————————————————-"
'Configure array based on command line arguments
' If no arguments, assume local hostname
If Wscript.Arguments.Count = 0 Then
arrComputers = Array(".")
Else
Dim arrComputers()
For i = 0 to Wscript.Arguments.Count – 1
Redim Preserve arrComputers(i)
arrComputers(i) = Wscript.Arguments(i)
Next
End If
For Each strComputer in arrComputers
'Connect to instance:
strDBServerName = strComputer
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
'Instance information
wscript.echo "SQL Instance: " & strDBServerName
wscript.echo "Version: " & objSQLServer.VersionString
wscript.echo "Number of databases: " & objSQLServer.Databases.count
'Table header
wscript.echo "Database, Size (MB), PrimaryFilePath, LogFilePath"
'Iterate through all Databases in instance
for i = 1 to objSQLServer.Databases.count
set objDB = objSQLServer.Databases(i)
intSize = objDB.Size
intTotalSize = cdbl(intTotalSize) + cdbl(intSize)
strDBName = objDB.Name
WScript.Echo strDBName & ", " & intSize & ", " & objDB.PrimaryFilePath& ", " & objDB.TransactionLog.LogFiles(1).PhysicalName
next
'Post total size of all databases in instance
wscript.echo " Total: ," & intTotalSize & ","
wscript.echo "————————————————-"
intSize = 0
intTotalSize = 0
'Next Instance
Next
Revised 9-11-08:
Added code to show path to LDF file as well. This property isn't in the same place in SQL-DMO, as TransactionLog is a collection of its own. The LogFiles(1).PhysicalName value will return the path to the first transaction log file in the collection, which should be close enough for government work, since most of the time, log files for a database are stored in the same subfolder (unless you're doing some really advanced scaling stuff with file groups).
moffitar