Quick Maintenance & Backup for MS SQL
Task
| QMB interface > Tasks menu > Task |
QMB comes with 30 preinstalled tasks. 10 of them are intended for working with popular Ola Hallengren stored procedures. Depending on the type of task, it will allow you to:
A task can be executed as part of a QMB Job or using the Execute task command from the context menu of the server list.
Task form with a T-SQL script |
 |
The program does not allow you to edit the scripts of predefined tasks, but you can always duplicate a task and edit it. |
Name – task name
Type – task type. The following values are available:
The form changes depending on the task type selected. More information about each task type is provided below.
Folder – a list folder where the task will be saved. Folders are used for grouping tasks in the list. If you want it to be saved to the root, use an empty value.
Notes – an optional note for the task.
Type: T-SQL
Tasks of the T-SQL Script are used for executing an arbitrary T-SQL script on the SQL Server. The following parameters are available on the T-SQL script tab:
Execute for Maintenance Policy databases – if this option is enabled, the task will be executed sequentially for every database in the maintenance policy. The name of the database will be passed to the T-SQL script and will replace the ?DataBaseName? placeholder. If the option is disabled, the task script is only executed once.
 |
Attention! We do not recommend using the T-SQL script task type for creating backups. The program has a special type called Create backup for this purpose and it also supports T-SQL script editing. |
You can insert a script written in the Transact SQL language into the T-SQL script field. You can use placeholders that QMB will replace with the necessary values while preparing the task for execution. The program supports the following markets:
| ?DataBaseName? |
Database name. It requires that the Execute for Maintenance Policy databases option be enabled in the task. |
| ?BackupDirectory? |
Path to the backup folder specified in the maintenance policy settings |
| ?FullBackupFileName? | Path and name of the full database backup file |
| ?DiffBackupFileName? | Path and name of the differential database backup file |
| ?LogBackupFileName? | Path and name of the transaction log backup file |
| ?MasterBackupFileName? | Path and name of the full backup file of the master system database |
| ?MsdbBackupFileName? | Path and name of the full backup file of the msdb system database |
| ?ModelBackupFileName? | Path and name of the full backup file of the model system database |
| ?VerifyBackup? |
The value of the Verify backup after completion option for tasks of the Create backup type. Values: 'Y'or 'N' |
| ?CleanupDate? |
The date and time when the backups must be deleted. Calculation is based on the storage period set in the policy settings |
| ?CleanupTime? |
The backup storage period in hours. Calculated on the basis of the storage period set in the policy settings |
File names for placeholders with backup file names are generated according to naming rules defined in the policy settings.
|
The GO command cannot be used in a script. Script tasks are executed in a single indivisible package. |
Type: Create Backup
Tasks of the Create Backup type are used for executing T-SQL scripts that create a full, differential or transaction log backup. In addition, an XML restoration plan will be created after the backup is created if the corresponding option is checked in the maintenance policy. The following parameters are set on the Backup parameters tab:
Backup type – a switch that defines the type of the backup being created: Full, Transaction log or Differential. The T-SQL script changes when different types are selected.
Verify the backup after completion – the option instructs the program to check the backup after its creation. The check is performed using the RESTORE VERIFYONLY command. The command checks the completeness of the backup and its readability. This option’s value is passed to the T-SQL script in the ?VerifyBackup? placeholder.
 |
The RESTORE VERIFYONLY command does not restore data from backups and therefore cannot guarantee that further restoration of data from this backup can be accomplished (visit https://msdn.microsoft.com/en-us/library/ms188902.aspx for more information). For guaranteed verification of backup files, we recommend adding the Restore backups to a temporary database with a subsequent integrity check task to the QMB Job. This tasks will restore the backup to a temporary database to check it. For more details, refer to Type: Restore to the Source Server or Type: Restore according to an XML Plan below. |
The T-SQL script tab also allows you to edit the default task script. Descriptions of fields and placeholders are provided in the T-SQL script section.
Type: Restore to the Source Server
This type of task makes it possible to automatically restore databases from backups on the SQL Server where the backups were created (the source server). Use the XML restorion plan to restore to a different SQL Server. They can be used for the following:
Form of a task of type Restore to the Source Server |
The T-SQL script of a Restore to the Source Server task is formed dynamically at the pre-execution stage. The set of backups to be recovered is based on information about created backups from the msdb system database.
Restoration is always performed to the last known moment of time (time of task execution). For example, we have a QMB Job containing the following tasks:
The T-SQL script for task 2 will be generated in a way that apart from full and differential sets, the necessary transaction logs will be applied, including the latest one created in this QMB Jon on step 1.
The following parameters are set on the Restore backup parameters tab:
The Source database parameter defines the source of backups. Possible values:
The Destination database switch defines the database where the restored data will be saved. Possible values:
|
When restoring to an existing database, all data in it will be lost! |
Check the database after backup restoration – this option allows you to run a database integrity check after its restoration (DBCC CHECKDB).
|
To restore databases in an automatic mode, you will need enough free space on the disks of the SQL Server. Bear this in mind while configuring the maintenance policy. |
Type: Non T-SQL Script
This type of task lets you execute batch files on various script programming languages, such as VBScript, JavaScript, Windows command shell language, and others. Before executing, the script is saved in a temporary file with the extension specified in the task, and then is launched for execution as a normal operating-system file (batch file). The script may return messages to the maintenance log (see the examples below).
The batch file can be executed on the machine where QMB is installed, or on the SQL Server. This option is set on the Parameters tab:
|
To run batch files on the SQL Server side, use the extended stored procedure xp_qmb. For security purposes, the text of the script is temporarily encrypted. The stored procedure xp_cmdshell is not used. |
The Script tab shows the file extension and the script that will be placed in the temporary batch file. Before executing, the script is saved in a temporary file with the extension specified in the task, and then is launched for execution as a normal operating-system file (batch file).
|
Attention! If the specified extension is not supported by the OS, then the script will hang until stopped. Before adding a task in QMB, it is recommended to first test the script, executing it as a batch file in the CMD.EXE console. |
Encoding - the encoding in which the temporary batch file will be stored. Also, this encoding will be used when reading messages for the maintenance log from the temporary file (see placeholder ?QmbOutputLog?).
Messages for the log are outputted by the script to the file ?QmbOutputLog? - checking this box tells QMB that the script will independently output messages for the maintenance log. (Please note that in this case, you will need to make sure you’ve set the script to record to the log.) If the checkbox is empty, then QMB will redirect output on its own to the file ?QmbOutputLog?. The file will then be blocked and unavailable for use in the script. For more information about the placeholder ?QmbOutputLog?, read below.
Execute for Maintenance Policy databases – if this option is enabled, the task will be executed sequentially for every database in the maintenance policy. The name of the database will be passed to the script and will replace the ?DataBaseName? placeholder. If the option is disabled, the task script is only executed once.
In the script, you can use placeholders, which QMB will replace with the necessary values while preparing a task for execution. For the task type Non T-SQL Script, the following placeholders are available in the program:
| ?DataBaseName? |
Database name. It requires that the Execute for Maintenance Policy databases option be enabled in the task. |
| ?QmbOutputLog? |
The path and name of the temporary text file, enclosed in apostrophes, to which output messages are redirected for the maintenance log. Example: "C:\Windows\TEMP\qmbtmp-796a3468-37a1-42f3-9599-f5460dcfcf19.tmp" After executing the task, QMB reads the text from the specified file and records it to the maintenance log. The placeholder ?QmbOutputLog? may be used in a script, if the box is checked for Messages for the log are outputted by the script to the file ?QmbOutputLog?. To isolate errors, you can use the tag <e></e>. |
| ?QmbOutputLog2? |
This placeholder is analogous to ?QmbOutputLog?, but the path is shielded by double slashes. Example: "C:\\Windows\\TEMP\\qmbtmp-796a3468-37a1-42f3-9599-f5460dcfcf19.tmp" |
| ?BackupDirectory? |
The path for the backups specified in Policy Properties. Example: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup |
| ?BackupDirectory2? |
This placeholder is analogous to ?BackupDirectory?, but the path is shielded by double slashes. Example: C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Backup |
The examples below show the CMD script for backup copying with the robocopy utility, with a variety of ways of outputting to a maintenance log.
|
QMB forwards messages from the screen to the maintenance log. The checkbox is empty for Messages for the log are outputted by the script to the file ?QmbOutputLog?. |
Copy Code |
|---|---|
@echo off rem The script copies all files from the backup folder that is specified in the policy, to the folder H:\BackupCopy rem The Robocopy utility is used for copying echo *** Copying files from "?BackupDirectory?" to "H:\BackupCopy" *** robocopy "?BackupDirectory?" "H:\BackupCopy" /r:0 /NJH /NJS /NP /NC /NS /NDL if %errorlevel% LSS 8 exit /b 0 echo ^<e^>Copying finished with errors!^</e^> exit /b 1 | |
|
Messages for the log are outputted by the script to the file ?QmbOutputLog?. The box is checked for Messages for the log are outputted by the script to the file ?QmbOutputLog?. |
Copy Code |
|---|---|
@echo off rem The script copies all files from the backup folder that is specified in the policy, to the folder H:\BackupCopy rem The Robocopy utility is used for copying echo *** Copying files from "?BackupDirectory?" to "H:\BackupCopy" *** >?QmbOutputLog? robocopy "?BackupDirectory?" "H:\BackupCopy" /r:0 /NJH /NJS /NP /NC /NS /NDL >>?QmbOutputLog? if %errorlevel% LSS 8 exit /b 0 echo ^<e^>Copying finished with errors!^</e^> >>?QmbOutputLog? exit /b 1 | |
Type: Create an XML Restoration Plan
The Create an XML Restoration Plan task type allows you to create an XML restoration plan. The XML plan records the sequence of backup file names that are needed to restore databases to their latest available state.
Folder сonnections - Connect to a network or local folder where you want to create an XML restoration plan. This folder should contain the backup files that are needed for the restoration.
XML file name - The name of the file to which the XML restoration plan is written.
Copy missing backup files - If this option is selected, the program will copy the missing backup files. The copy is made from the source SQL Server.
Databases - Database names for which an XML restoration plan will be created. Names should be separated by a comma.
Type: Restore according to an XML Plan
The Restore according to an XML Plan task type allows you to restore databases according to an XML restoration plan.
Folder connections - Connects to a network or local folder containing an XML restoration plan and backup files.
XML file name - The name of the XML Restoration Plan file.
The Source databases switch determines which databases will be restored in accordance with the XML plan.
The Destination database switch defines the database where the data will be restored. Possible values:
Check the database after backup restoration – this option allows you to run a database integrity check after restoration (DBCC CHECKDB).
See also Manual restore according to XML Plan
Type: Copying Backups
A system task of the type Backup Copying copies new and modified files with the extensions bak, trn and xml from the backup folder specified in the properties of the maintenance policy. The task will also delete obsolete backup files in the recipient folder, if the corresponding copying settings have been set in Policy Properties.