Chapter 4. Data Protection for SQL Server backup
4.2 Legacy GUI backups
In this section we discuss how to perform SQL Server backups using Data Protection for SQL Server GUI tool. In the next examples, it is expected that you have already installed and configured Data Protection for SQL, launched the Data Protection for SQL Server GUI as explained in 4.1.3, “Launching the GUI” on page 112, and expanded the SQL Server tree on the left pane. All backup scenarios are performed starting at this point.
4.2.1 Full database backups
To perform a full backup of a database, execute the following steps:
1. Select the Backup Databases tab.
2. In the left pane, choose the databases to back up. If you want to backup all databases for the SQL Server instance, choose the SQL Server instance name, and automatically all databases under this instance will be selected.
3. In the Backup Options section, choose the Legacy Backup radio button, and the number of stripes for this backup.
4. In the Backup Type drop-down menu, choose Full.
5. Click Backup to start the backup.
Figure 4-9 on page 115 shows how to select all databases in a SQL Server instance for a full backup.
Figure 4-9 Legacy full backup
The backup progress will be displayed as shown in Figure 4-10.
Figure 4-10 Database operation progress
After the backup is complete, the Backup Progress window indicating whether or not the operation completed successfully and the object(s) backed up with detailed status information is displayed, as shown in Figure 4-11. Click OK to finish. If any error message is displayed, it means that the backup has failed.
Figure 4-11 Backup complete message
4.2.2 Transaction log backups
Performing a transaction log backup is very similar to a full database backup. The difference is that you must choose Log instead of Full in the Backup Type drop-down menu . When you choose this option, in the Backup Options section, the options Truncate Log and Log Est % Chg become available for you change their configuration. Unless you are performing a tail-log backup, we recommend that you do not uncheck Truncate Log, otherwise the transaction log file will not be truncatde when the transaction log backup finishes, allowing the transaction log to consume all disk space available.
The Log Est % Chg spin box allows you to estimate the percentage of database pages that have changed due to non-logged operations since the last log backup. The default is 0.
All databases that cannot have a transaction log backup due to the simple recovery model or truncate log on checkpoint option active are displayed with an “X” on the left pane, so that you cannot choose these databases to perform a log backup.
Figure 4-12 on page 117 shows an example of how to select a database for log backups. In our example, we select the SalesDB database to perform transaction log backup. Note that several databases on the left pane are marked with an “X”, such as master and msdb databases. You are not allowed to perform transaction log backups for these databases.
Note: An example of tail-log backup is shown in 4.2.7, “Tail-log backups” on page 121.
Figure 4-12 Legacy transaction log backup
Click Backup to start the backup operation. The Backup Progress window is displayed to inform you whether or not the operation completed successfully and lists the object(s) backed up with detailed status information.
4.2.3 Differential backups
Performing a full differential backup is similar to full and transaction log backups. You must click on the Backup Databases tab, and select Differential in the Backup Type drop-down menu. The parameters Stripes and Diff Est % Chg become available for you to customize your backup operation. The parameter Diff Est % Chg assumes the value specified in
“Estimate % Change for Differential Backup” , as discussed in “General tab” on page 105. It is used to estimate the percentage of database pages that have changed since the last full backup.
Figure 4-13 on page 118 shows an example of a differential backup.
Figure 4-13 Legacy differential backup
Click Backup to start the backup operation. The Backup Progress window is displayed to inform you whether or not the operation completed successfully and lists the object(s) backed up with detailed status information.
4.2.4 Group backups
While for full, log and differential backups you use the Backup Databases tab, for filegroup, file and set backups you use the Backup Groups/Files. To perform a group backup:
Select the Backup Groups/Files tab.
Select the Group radio box in Backup Type section.
Select the number of stripes in Backup Options section.
On the left pane, expand the SQL Server tree to display the list of databases. Note that, as for transaction log backups, neither databases using the simple recovery model nor with truncate log on checkpoint active are available for selection. You can identify those databases not available for group backups by the “X” on its checkbox.
On the left pane, click on the database you want to backup. The existing filegroups for the database will appear under the database name, and also on the right pane.
Select the filegroups you want to back up. Note that when you highlight the filegroup. the files belonging to this filegroup are listed on the List View in the right pane. However, you cannot select the file individually; if you select a file, all filegroup will be selected.
You can select several filegroups on the same database, or filegroups on different databases repeating the last step.
Figure 4-14 on page 119 shows an example of group backup. In this example, we are backing up the SalesDB_LA filegroup of the SalesDB database.
Figure 4-14 Legacy group backup
Click Backup to start the backup operation. The Backup Progress window is displayed to inform you whether or not the operation completed successfully and lists the object(s) backed up with detailed status information.
4.2.5 File backups
Peforming a file backup is similar to a group backup. On the right pane, click the Backup Groups/Files tab, select the File radio box in the Backup Type section, and choose the number of stripes. On the left pane, expand the tree to select the files you want to back up. In comparison to a group backup, in a file backup you can choose individually the files to back up on the left pane, while in the group you choose the filegroup itself, and back up all files belonging to the filegroup. Note that when you highlight the filegroup on the left pane, the files belonging to this filegroup are listed on the List View in the right pane, and for file backups you can individually select any file belonging to the filegroup.
Figure 4-15 on page 120 shows an example of file backup. In this example, only the file SalesDB_LA_1 is backed up. All other files from the SalesDB_LA filegroup are not included in this backup.
Note: We recommend you to follow group backups with transaction log backups for all SQL databases you back up.
Figure 4-15 Legacy file backup
Click Backup to start the backup operation. The Backup Progress window is displayed to inform you whether or not the operation completed successfully and lists the object(s) backed up with detailed status information.
4.2.6 Set backups
Performing a set backup is similar to group or file backups. On the right pane, click the Backup Groups/Files tab, select the Set radio box in the Backup Type section and choose the number of stripes in the Backup Options section, On the left pane, choose what SQL Server files and filegroups you will be back up.
Figure 4-16 on page 121 shows an example of a set backup. In this example the filegroup SalesDB_EMEA and the file SALESDB_LA_2 from the filegroup SalesDB_LA are backed up.
Note: We recommend you to follow file backups with transaction log backups for all SQL databases you back up.
Figure 4-16 Legacy set backup
Click Backup to start the backup operation. The Backup Progress window is displayed to inform you whether or not the operation completed successfully and lists the object(s) backed up with detailed status information.
4.2.7 Tail-log backups
A tail-log backup is similar to a transaction log backup. The only difference is that you must remove the checkbox Truncate Log in the Backup Options section.
Figure 4-17 on page 122 shows an example of tail-log backup. Note that the truncate log checkbox is unchecked.
Note: We recommend you to follow set backups with transaction log backups for all SQL databases you back up.
Figure 4-17 Legacy tail-log backup
Click Backup to start the backup operation. The Backup Progress window is displayed to inform you whether or not the operation completed successfully and lists the object(s) backed up with detailed status information.