Automating SQL Server Management with WMI (Part 3)

In the first article of this series, I presented a few simple scripts for 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).
The second article covered methods dealing mostly with SQL Server and database maintenance.

In this article, I will continue coverage of WMI-based methods of managing SQL Server by presenting sample code for performing SQL database backup and restore.

SQL Backup can be performed using the SQLBackup method of the MSSQL_SQLServer class. The method uses as its parameter an instance of another class, MSSQL_BackupSettings. The values of its properties control various backup parameters, such as the type of backup (full, differential, file, or log), media and backup set names and descriptions, formatting and initializing options, etc. A description of each parameter is included in the script comments.

sComputer = "SWYNKSRV01"    '-- name of the computer where SQL Server is installed
sSQLServer = "SWYNKSRV01"   '-- name of the SQL server instance where the database to be backed up resides

Dim sPassword               '-- password securing backup set (required during restore)
Dim sMediaPassword          '-- password securing backup media set (required during restore)
Dim sBackupDescription      '-- description of the backup set (up to 255 characters)
Dim sBackupSetName          '-- name of the backup set (up to 128 characters)
Dim sBlockSize              '-- size of the unit used when formatting backup tape (used when bFormatMedia is set to True)
                            '-- applies to tape backup only
Dim sDatabase               '-- name of the database to back up
Dim aDatabaseFileGroups     '-- array containing names of the filegroups to be backed up
                            '-- applies only if iTargetType parameter is set to 2
Dim aDatabaseFiles          '-- array containing logical (not physical) names of the files to be backed up 
                            '-- applies only if iTargetType parameter is set to 2
Dim aDevice                 '-- array containing names of backup devices
Dim bFormatMedia            '-- True or False, depending on whether the media should be formatted prior to running the backup
                            '-- applies to tape backup only
Dim bInitialize             '-- True or False, depending on whether the existing backup sets on backup media 
                            '-- should be overwritten or not
                            '-- if False, the backup set is appended to existing backup sets
                            '-- whether existing backup set will be actually overwritten, depends also on other factors
                            '-- (e.g. existing non-expired backup sets can not be overwritten)
Dim sMediaDescription       '-- description of the backup media written at the time of initialization
Dim sMediaName              '-- name of the backup media written at the time of initialization
Dim bRestart                '-- True or False, determining whether backup should be restarted after interruption 
                            '-- from beginning of the backup set or continue where it left off
Dim iRetainDays             '-- number of days before the backup set can be overwritten
Dim bSkipTapeHeader         '-- True or False, which disables or enables verification of the media loaded 
                            '-- applies to tape backup only
Dim sSQLServerName          '-- name of the SQL Server instance
Dim iTargetType             '-- type of backup
                            '-- 0 backup of entire database.
                            '-- 1 differential backup of changes after the most recent full or differential backup.
                            '-- 2 backup of specified files only
                            '-- 3 backup of transaction log only
Dim iTruncateLog            '-- log file processing
                            '-- 0 truncate (default). Transaction log is backed up, records of committed transactions are removed.
                            '-- 1 do not truncate. Transaction log is backed up, records of committed transactions are not removed, 
                            '-- 2 do not log. Transaction log is not backed up, records of committed transactions are removed. 
Dim bUnloadTapeAfter        '-- True or False, determines whether tape should be unloaded after the backup completes

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

sBackupDescription			= "Full Backup of SwynkDB on 12/06/02"
sBackupSetname				= "Full SwynkDB 12/06/02"
sDatabase				= "SwynkDB"
aDevice 				= Array("C01DiskBkp")
bInitialize				= "True"
sMediaDescription			= "SwynkDB Backup Media"
sMediaName				= "SwynkDB Media"
bRestart				= "True"
iRetainDays				= 7
iTargetType				= 0
iTruncateLog				= 0

oBackSettings.BackupSetDescription 	= sBackupDescription
oBackSettings.BackupSetName		= sBackupSetName
'-- oBackSettings.BlockSize 		= sBlockSize
oBackSettings.Database 			= sDatabase
'-- oBackSettings.DatabaseFileGroups 	= aDatabaseFileGroups
'-- oBackSettings.DatabaseFiles 	= aDatabaseFiles
oBackSettings.Device 			= aDevice
'-- oBackSettings.FormatMedia 		= bFormatMedia
oBackSettings.Initialize 		= bInitialize
oBackSettings.MediaDescription 		= sMediaDescription
oBackSettings.MediaName 		= sMediaName
oBackSettings.Restart 			= bRestart
oBackSettings.RetainDays 		= iRetainDays
'-- oBackSettings.SkipTapeHeader 	= bSkipTapeHeader
oBackSettings.SQLServerName 		= sSQLServer
oBackSettings.TargetType 		= iTargetType
oBackSettings.TruncateLog 		= iTruncateLog
'-- oBackSettings.UnloadTapeAfter 	= sUnloadTapeAfter

sPassword 				= "BackupPa$$"
sMediaPassword 				= "MediaPa$$"

Set oOutParam = oInstance.SQLBackup(oBackSettings, sPassword, sMediaPassword)

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

You would need to change the provided sample values depending on your SQL Server configuration or type of backup performed. For example, if you wanted to back up only specific files instead of a complete database, you would assign the value to aDatabaseFiles parameter, e.g.:

'-- aDatabaseFiles		= Array("SwynkDB_Data","SwynkDB_DataSec")

(where SwynkDB_Data and SwynkDB_DataSec are logical names of database files) and change the value of iTargetType variable to 2 (backup of specified files only). I commented out (by placing an apostrophe at the beginning of the line) parameters that are not relevant in this example. If you want to use them, simply change the value of the appropriate variable and remove the leading apostrophe.

Restoring the backup requires use of the SQLRestore method of the MSSQL_SQLServer class, which, similiar to the Backup method, uses as its parameter instance of another class (MSSQL_RestoreSettings). The following sample code contains descriptions of the parameters used for the backup:

sComputer = "SWYNKSRV01"   '-- name of the computer where SQL Server is installed
sSQLServer = "SWYNKSRV01"  '-- name of the SQL server instance where the database to be backed up resides

Dim aBackupDevice          '-- array containing names of backup devices
Dim sPassword              '-- password securing backup set (required during restore)
Dim sMediaPassword         '-- password securing backup media set (required during restore)
Dim aDatabaseFileGroups    '-- array containing names of the filegroups to be restored
                           '-- applies only if iTargetType parameter is set to 1
Dim aDatabaseFiles         '-- array containing logical (not physical) names of the files to be restored
                           '-- applies only if iTargetType parameter is set to 1
Dim sDatabaseName          '-- name of the database to restore
Dim iFileNumber            '-- an integer specifying the sequential postition of backup set to be restored 
                           '-- within the backup media Dim bLastRestore
                           '-- True or False, depending on whether restore includes last transaction log in
                           '-- a sequence of backed up logs
Dim bLoadHistory           '-- True or False, depending on whether history tables in msdb are updated during 
                           '-- database verification
Dim sMediaName             '-- name of the backup media from which the restore is performed
Dim aRelocateFile          '-- array containing logical and physical names of database files
                           '-- applicable when restoring database to alternate location
Dim bReplaceDatabase       '-- True or False, determining whether new database can be created if one with the 
                           '-- same name does not exist
                           '-- False (default) will prevent restore from completing if the database does 
                           '-- not already exist
Dim bRestart               '-- True or False, determining whether restore will restart after interruption from 
                           '-- the beginning of backup set 
                           '-- or continue where it left off
Dim dtRestoreTillTime      '-- point in time to which the transaction log should be restored
                           '-- applies only if iTargetType is set to 2
Dim sSQLServerName         '-- name of the SQL Server
Dim sStandbyFile           '-- name of the undo file (in case of restore to a Standby server)
Dim iTargetType            '-- type of restore
                           '-- 0 restore the entire database.
                           '-- 1 restore only specified files.
                           '-- 2 restore only the database transaction log.
Dim bUnloadTapeAfter       '-- True or False, determines whether tape should be unloaded after the restore completes

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

aBackupDevice 				= Array("C01DiskBkp")
sDatabaseName				= "SwynkDB"
iFileNumber				= 1
bLoadHistory				= "True"
sMediaName				= "SwynkDB Media"
bReplaceDatabase			= "True"
bRestart				= "False"
iTargetType				= 0

oRestSettings.BackupDevice 		= aBackupDevice
'-- RestSettings.DatabaseFileGroups 	= aDatabaseFileGroups
'-- oRestSettings.DatabaseFiles 	= aDatabaseFiles
oRestSettings.DatabaseName 		= sDatabaseName
oRestSettings.FileNumber 		= iFileNumber
'-- oRestSettings.LastRestore 		= bLastRestore
'-- oRestSettings.LoadHistory 		= bLoadHistory
oRestSettings.MediaName 		= sMediaName
'-- oRestSettings.RelocateFile 		= aRelocateFile
oRestSettings.ReplaceDatabase 		= bReplaceDatabase
oRestSettings.Restart 			= bRestart
'-- oRestSettings.RestoreTillTime 	= dtRestoreTillTime
oRestSettings.SQLServerName 		= sSQLServer
'-- oRestSettings.StandbyFile 		= sStandbyFile
oRestSettings.TargetType 		= iTargetType
'-- oRestSettings.UnloadTapeAfter 	= bUnloadTapeAfter

sPassword 				= "BackupPa$$"
sMediaPassword 				= "MediaPa$$"

Set oOutParam = oInstance.SQLRestore(oRestSettings, sPassword, sMediaPassword)

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

As before, you will need to adjust the values of the parameters (and remove comment marks) to match your environment and requirements.

In the next article of this series, I'll present WMI-based methods for performing common maintenance tasks, such as truncating transaction logs or rebuilding indexes and updating statistics on database tables.

See All Articles by Columnist Marcin Policht