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:\MSSQL\data\MyDB_Data.MDF" and
"D:\MSSQL\data\MyDB_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:\MSSQL\data\MyDB_Data.MDF","D:\MSSQL\data\MyDB_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