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