Automating SQL Server Management with WMI (Part 2)

In the first article of this series, I presented a few simple scripts automating the most basic SQL Server administrative tasks, such as starting and stopping a SQL Server service and login management (changing passwords and determining mappings between server logins and database users). In this article, we will cover methods dealing mostly with SQL Server and database maintenance.

We will start with the process of detaching and attaching databases. Combining both processes can be useful for transferring databases between two SQL servers, without resorting to backup and restore processes. The methods DetachDB and AttachDB are part of the already familiar MSSQL_SQLServer class.

Script to Detach a Database

Detaching a database removes the reference to the database from the SQL server but leaves the database physical files intact. Before you detach a database, you should note the names of the physical files that it consists of. You will need this information in order to successfully re-attach a detached database. To extract this information, you can run the following script:

sComputer	= "SWYNKSRV01"
sSQLServer	= "SWYNKSRV01"
sDBName		= "MyDB"

Set cInstances = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		sComputer & "/root/MicrosoftSQLServer:MSSQL_DatabaseFile").Instances_

For Each oInstance In cInstances
	If oInstance.DatabaseName = sDBName Then
		WScript.Echo oInstance.PhysicalName
	End If
Next

This will list the names of the physical data files that the database MyDB (whose name is stored in the variable sDBName) consists of. Let's assume that the names returned by the script are:

• "D:MSSQLdataMyDB_Data.MDF" and
• "D:MSSQLdataMyDB_DataSec_Data.NDF"

This list contains only the database files. You can, however, also include in this list the transaction log files. If you don't, the transaction log files will be automatically created during attaching process. Once you have recorded the names of the physical files, you can detach the database from the original server. By setting the bCheck variable to TRUE, you will force an update of the database statistics prior to detaching it.

sComputer	= "SWYNKSRV01"
sSQLServer	= "SWYNKSRV01"

sDbName 	= "MyDB"
bCheck		= TRUE		'boolean value indicating whether statistics 
                  		'should be updated prior to detaching

Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		sComputer & "/root/MicrosoftSQLServer:MSSQL_SQLServer.Name=" & _
		Chr(34) & sSQLServer & Chr(34))

sRetVal = oInstance.DetachDB(sDbName, bCheck, oOutParam)

If oOutParam.ReturnValue = 0 Then
	WScript.Echo "Detaching " & sDbName & " completed successfully"
Else
	WScript.Echo "Detaching " & sDbName & " failed with the error " & oOutParam.Description
End If

Script to Attach a Database

To attach the database on another SQL Server, you can use the next script. Prior to running it, you will need to copy the physical files to the appropriate directories on the target server. aDataFile is the array containing names of these files. Note that the AttachDB method cannot be used to attach a database consisting of more than 16 physical files. This, however, is a limitation of SQL server not the APIs used by the WMI method.

sComputer	= "SWYNKSRV02"
sSQLServer	= "SWYNKSRV02"

sDbName 	= "MyDB"
aDataFile 	= Array("D:MSSQLdataMyDB_Data.MDF","D:MSSQLdataMyDB_DataSec_Data.NDF")

Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		sComputer & "/root/MicrosoftSQLServer:MSSQL_SQLServer.Name=" & _
		Chr(34) & sSQLServer & Chr(34))

sRetVal = oInstance.AttachDB(sDbName, aDataFile, oOutParam)

If oOutParam.ReturnValue = 0 Then
	WScript.Echo "Attaching " & sDbName & " completed successfully"
Else
	WScript.Echo "Attaching " & sDbName & " failed with the error " & oOutParam.Description
End If

Display Processes Script

Next, let's take a look at a way to kill a SQL Server process. The KillProcess method is also part of the MSSQL_SQLServer class. The method takes a single parameter, the process ID. In order to obtain a list of processes running on SQL server, along with their characteristics, you can run the following script:

sComputer	= "SWYNKSRV01"
sSQLServer	= "SWYNKSRV01"

Set cInstances = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		sComputer & "/root/MicrosoftSQLServer:MSSQL_Process").Instances_

WScript.Echo 
For Each oInstance In cInstances
	WScript.Echo String(60,"=")
	WScript.Echo "Handle" & vbTab & vbTab & oInstance.Handle
	WScript.Echo "Hostname" & vbTab & oInstance.Hostname
	WScript.Echo "Login" & vbTab & vbTab & oInstance.Login
	WScript.Echo "ClientName" & vbTab & oInstance.ClientName
	WScript.Echo "Command" & vbTab & vbTab & oInstance.Command
	WScript.Echo "State" & vbTab & vbTab & oInstance.State
	WScript.Echo "CPUTime" & vbTab & vbTab & oInstance.CPUTime
Next

This will provide you with the process id (handle), the name of the host from which the process was launched, the login that owns the process, the name of the client application that launched the process, the type of command executed by the process, its state, and CPU usage.

Kill SQL Server Process Script

Once you have determined the process id, you can kill it by running the next script. Just remember to set the value of the iProcID to match the target process id:

sComputer	= "SWYNKSRV01"
sSQLServer	= "SWYNKSRV01"

iProcID 	= 51

Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
		sComputer & "/root/MicrosoftSQLServer:MSSQL_SQLServer.Name=" & _
		Chr(34) & sSQLServer & Chr(34))

Set oOutParam = oInstance.KillProcess(iProcID)	

If oOutParam.ReturnValue = 0 Then
	WScript.Echo "Process " & iProcID & " killed successfully"
Else
	WScript.Echo "Killing " & iProcID & " failed with the error " & oOutParam.Description
End If

In the next article, I'll continue covering maintenance tasks (such as updating statistics and rebuilding indexes on selected tables) and present methods for backing up and restoring SQL server.

See All Articles by Columnist Marcin Policht