Backup Maintenance Demos

Below are demos with screenshots that explain how to set up backup maintenance procedures in SQL Server 2019. We will be using a custom made database titled, "LUCKYGOAT", to perform the maintenance tasks. We will begin with creating an automated Full Backup of a database.

Contents:

• Full Backup Demo
• Differential Backup Demo
• Transaction Log Backup Demo

Full Backup Demo

This demo shows how to set up a full weekly backup and how to store your backups.

1. Connect to the database instance that your databases are held on. Then in the object explorer, check to see if you have any maintenance plans running. As you can see, I currently do not have any maintenance plans in place

   

2. Then, right click on Maintenance Plans and select "New Maintenance Plan". Create a name for the maintenance plan like, "Weekly Full [Database name] Backup" and continue to the following screen. You can unpin Object Explorer on the left and pin Toolbox as shown below.

   

   * The Toolbox is effective for many different Database maintenance tasks. Currently, we are only concerned with the "Back Up Database Task"

3. Drag the "Back Up Database Task" onto the white area in the Design window. It may take some time to load depending on the size of the database. Right click on the task box and select "Edit".

   

4. In the edit pane you can customize the type of backup you want to select, the database that you want to be backing up, and the location to where the backups will be saved.

   * In this demo, we will only be backing up the LuckyGoat database and we will be saving the backups to the disk

   

   

   

5. Next, select the Destination tab. In this tab you can specify where the backups will be stored. If you are backing up more than one database at a time, you would be wise to select the "Create a sub-directory for each database" box as then the backups will be separated in your folder by the different databases. Since we are only backing up a single database in this demo, I will not be selecting this option. Also note that the backup file extension is "bak" as shown at the bottom of the screen. DO NOT attempt to add a period before the bak (.bak) as this will result in an error.

   

6. Then select the Options tab.

   • In the "Set backup compression: " box, I recommend choosing Compress Database like I have done below as this will reduce the file size and increases backup speed significantly.
   • I also recommend selecting "Verify backup integrity" as SQL Server will run a restore verify only command after the backup is created that will ensure the backup was created properly
   • After customizing your options tab, select OK

   

7. Next, we will want to set up some retention and clean up old backups older than a certain age. We will select "Maintenance Cleanup Task" in the tool box and drag it into the white space near the Backup task. Then we will drag the arrow from the backup task and connect it to the Maintenance cleanup task.

   

8. Right click on the Maintenance Cleanup Task and select "Edit".

   • In this edit pane, you must first check that the connection is correct at the top of the pane.
   • Select "Search folder and delete files absed on an extension". Then locate the folder that we are saving our backups to. This we selected when we created the backup task. The file extension box should contain "bak", NOT ".bak". This is very important to remember as the task will not run if done wrong.
   • Then specify the age of backup files that you would like to keep. Depending on the size and activity of your databases this can change. For the sake of the demo, we will saving all backup files within the past 2 weeks.
   • Select OK when your options are selected

   

9. Now with your Backups customized and Maintenance cleanup plan in place, you must now select a schedule for your backups. Click the Calendar icon shown below.

   

10. In the Calendar tab:

    - Create a name for the Full Backup. It could be as simple as "Full Weekly [Database Name] Backup Job" - Make sure that the schedule type is recurring and that the enabled box next to it is selected - Customize the frequency of the backup. Since this is a weekly full backup, we will have frequency being weekly, it recurs every 1 week, and it occurs on Sunday at 12:15 AM. - Finally, select the start date of the job and then select OK

    

11. Now we can close the Design tab, save the changes we have made, open the Object Explorer, refresh Maintenance Plans, and we can see that our Full Backup plan is created. We can also see that under Jobs, the Full Backup Job is created.

    

12. To test if the backup job ran correctly, we will run the job and confirm that the backup was successful.

    

Differential Backup Demo

We will cover how to create a daily differential backup for a database in SQL Server 2019. Most of the steps to perform this task are already explained in the Full Backup Demo. Thus we will begin our demo after step 3 in the Full Backup Demo. We also will not be going over the Maintenance Cleanup task as that is also already covered in the Full backup demo under Weekly Maintenance Demos.

* NOTE: Even though the Maintenance Cleanup Task is not covered in this demo, make sure to set up that task for all backup jobs created, including the daily differential backup shown here

1. Follow steps 1-3 in the Full Backup demo shown under the Weekly Maintenance demos. Be sure to change the Maintenance Job name to Daily Diff [Database Name] Backup.

2. In the Edit pane under the General Tab:

   • Change the Backup Type from Full to Differential
   • Select the databases you want to backup. In this demo we will only be backing up a single database
   • Select where the backup will be saved to. Most likely you will be saving to Disk

   

   

3. Under the Destination tab in the Edit pane:

   • Select "Create a backup file for every database". The folder should already be specified for you. If you are creating differential backups for multiple databases, select the "Create a sub-directory for each database" tab, as this will maintain good organization in files
   • In the "Backup file extension: " box, note that the extension is defaulted to "bak". If we were conducting a full backup, this would be acceptable. However, since we are conducting differential backups we must change the extension to "dff" to be current with industry standards.

   * NOTE: When changing the backup file extension, make sure to enter "dff" instead of ".dff"!

   

4. Under the Options tab in the Edit pane, we will be following the same steps as we followed when creating a Full backup

   • Change "Set backup compression" to "Compress Backup" to increase backup speed
   • Select "Verify Backup integrity"
   • The other options are not as important so we will not cover what they do in this demo
   • Select OK when finished with options

   

5. Then select the Calendar icon shown below to create the schedule of the daily backups

   • Name the Job something that differentiates itself from the other backups like "Daily Diff [Database Name] Backup Job"
   • In the "Schedule type: " box, make sure that recurring is selected and also make sure that next to it, Enabled is selected.
   • Under Frequency, select every day besides Sunday because that is when our Full backup will run. You must keep the "Occurs:" box to "Weekly" to select the individual days in a week.
   • Under Daily Frequency, select the time that you want the backups to run. For this demo we selected 12:15 AM.
   • Once the schedule is customized, select OK at the bottom on the Schedule pane.

   

   

6. Next, create a Maintenance Cleanup Plan that will delete old backups to reduce disk space used

   * NOTE: This step was covered in the Full Backup Demo. Refer to steps 7-8 in that demo.

   * ALSO NOTE: When editing the Maintenace Cleanup task, make sure to change the file extension name from "bak" to "dff" as we are focusing on differential backups. Failure to do this will result in Full backups being deleted instead of differentials!

   

7. Finally, close the Design window and save the changes. Then refresh the Object Explorer and check to see if the Maintenance Plan and Job is there

   

8. To test if the backup ran correctly, we will run the job once and check in our folder if the backup is stored properly.

   

   * NOTE: The differential backup ran successfully and you can see that the DFF differential backup file type is different than the BAK full backup file type

Transaction Log Backup Demo

This demo will show how to create a transaction log backup that will run periodically throughout the day to backup transactions. This type of backup is used if you are using a database that contains many transactions every day.

* NOTE: This demo is very similar to the Full and Differential backup demos shown in this document. This demo will skip some steps that are already shown in the Full Backup Demo. Refer to that demo for steps skipped over.

1. Go through steps 1-3 in the Full Backup Demo

   • Dont forget to name the maintenance plan something along the lines of "Daily Transaction Log [Database Name] Backup" as this is for a log backup and not a full

   

2. In the Edit pane undeer the General tab, change the backup type to "Transaction Log". Also, select the databases that you want to create log backups for. For the purpose of this demo, we are only using one database.

   

3. In the Edit pane under the Destination tab, make sure that the "Backup file extension: " box contains "trn" and not "bak" or "dff" as this is a transaction log backup and not a full or differential.

   * NOTE: If we were backing up multiple databases, we would also check the "Create a sub-directory for each database box" to help organization in the folder. In this demo we are working with only one database so we will leave this unchecked.

   

4. Refer to steps 6-9 in the Full Backup Demo to complete the Maintenance Cleanup Task.

   • When creating Maintenance Cleanup task names, make sure to name the task something along the lines of "Daily Log Maintenance Cleanup Task" too differentiate it from the other Maintenance Cleanup tasks
   • Also be sure to change the file extension to "trn" and the delete file age to 1 day as all older transactions will be stored in the differential backup
   • Below is a screenshot of what your Edit pane in the Maintenance Cleanup Task job should look like

   

5. Refer to steps 9-12 in the Full Backup Demo to create a schedule for the backup job and check to see if the job ran successfully

   • When creating the schedule for the job, change the frequency to occurring daily, and change the daily frequency to occurs every 30 minutes (Or whatever parameters that meet your RPO and RTO standards)
   • Below are screenshots of what your calendar and Object Explorer should look like. Also attached is a screenshot of the Transaction log stored in the folder.