Microsoft Business Intelligence: Back Up Database Task

By using the Back up Database task, a package can back up a single database or multiple databases. If the task backs up only a single database, you can choose the backup component: the database, or its files and file groups.

This task will be very helpful for the DBA’s to maintain the database automatically by creating the package and using it across the servers. This task has many options to do a Full, Differential backups based on our requirement we can go ahead and use the respective option.

Step by Step Back Up Database task

Take Back up Database task

Double click on Back up database task

Create a new connection.

Click ok.

Select back up type. There are three type of back up mode

Ø Full

Ø Differential

Ø Transaction log

Full Back: - whenever you creating a new back up package then you need to create the full backup mode.

Differential:-

A differential base is a full, file, or file group backup of the database. It’s required for differential backups. Backup options such as Checksum and Restore with Verify are useful to build confidence in the differential base’s reliability.

There are two distinct types of differential backups: single-base (which is a differential backup that’s based on a single full backup) and multibase (which is a differential backup that’s based on different file or file group backups). The most commonly used type is the single-base differential backup.

To demonstrate the differential backup process, we have chosen to implement a single-base differential backup on a database operating under the full recovery model. Figure 1 shows a typical backup strategy in which full and transaction log backups are performed. The automated backup job takes a full backup nightly at 6 p.m. and subsequent log backups every 3 hours. In a 24-hour cycle, one full backup file and seven transaction log backup files are created.

Suppose you want to change the strategy by replacing the daily full backups, excluding Sunday, with differential backups, as shown in Figure 2. The first step in implementing this differential backup strategy is to create the differential base with the BACKUP DATABASE command:

Here are the three backup scenarios and their results:

Backup scenario 1:- In this scenario, we scheduled daily full backups and daily transaction log backups every three hours, using a seven-day rolling retention period. As Table 1 shows, the current storage requirement for this scenario is 1336.5GB, which will rise as the database grows. However, the maximum number of restores required remains static.

Backup scenario 2:- In this scenario, we scheduled a weekly full backup and daily transaction log backups every three hours, using a seven-day rolling retention period. As Table 2 shows, there’s a low 380GB storage requirement. Because the full backup is produced only once on Sunday and is retained to the following Sunday when it’ll be overwritten, this mechanism isn’t subjected to daily 156GB increments in storage. However, the time needed to apply maximum number of restores would devalue the storage gain.

Backup scenario 3:- In this scenario, we scheduled a weekly full backup, daily differential backups, and transaction log backups every three hours, using a seven-day rolling retention period. The previous day’s differential backup is deleted after the current day’s differential backup is successfully completed and verified. As Table 3 shows, the differential backups reduced the maximum number of restores compared to scenario 2 and reduced backup storage space compared to the daily full backups in scenario 1.

Ø Scenario 1 versus scenario 2: Although Scenario 1 requires fewer restores, it requires significantly more disk space to retain the backups.

Ø Scenario 1 versus scenario 3: Although Scenario 3 requires about the same number of restores, it requires about 60 percent less disk space to retain the backups.

Ø Scenario 2 versus scenario 3: Although Scenario 2 requires less disk space, the number of restores as the week progress gets prohibitively higher.

Backup compression can provide additional disk space savings for full, transaction log, and differential backups, so the numbers in Table 3 might improve across the board. However, as we mentioned previously, compression isn’t available in all version and editions of SQL Server (which is why we didn’t include it in the scenarios) and its effectiveness can be limited in databases containing recompressed data.

Here I am selecting full backup type.Now you select the database for which you want to create backup. Here you will get following option.

Ø All database (If you want to take back up all database then select this option)

Ø System database( If you want to take back up system database then select this option)

Ø All user database (excluding master, model, msdb and tempdb)

Ø These database ( here you can select specific database)