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.
