In disaster recovery plans, backups are essential components to restore the most recent saved data.  Our objective is to create a Farm Data Language (FDL) view that monitors and manages backups using the dbWatch Control Center. FDL is a query language for managing your instances and databases. You check out the link for a more detailed explanation on FDL. 

What are the types of backups? 

A full backup backup of all data in a database. This type of backup is the least efficient among all the backups. It takes more time to complete with more data stored in your database. However, a full backup is the fastest during data recovery since you only need to restore the last full backup. 

An incremental backup backups data that changed since the last full or incremental one. In the illustration above, a full backup was done on Monday. For the rest of the week, backups, including the data, changed for that weekday. Meaning, Tuesday will back up Tuesday's data. Wednesday will do for Wednesday's data. And so on.  

The advantage of an incremental backup is that it is the fastest one. But it takes the longest to restore. During recovery, you need to recover the last full backup followed by incremental backup's correct chronological order. So, with the illustration above, you need to restore Monday's data, followed by Tuesday, Wednesday, Thursday, and finally Friday's data in that particular order. 

A differential backup backups data that has changed since the last full backup or incremental one but considers data changes. It observes similar incremental backup principles (starting with full backup and moving on with subsequent backups). However, it includes data from the previous incremental backups and the current one. In the illustration above, a full backup was done on Monday. Tuesday will consist of Tuesday's data. Wednesday will include both Wednesday's and Tuesday's data. And so on.

During recovery, you only need the last full backup and previous differential backup to restore the most recent data. In the same illustration, to restore your latest data, you only need Monday's full backup and Friday's differential back. This much faster than incremental backups. 

Monitoring Backups – Installed Jobs 

Now that we have a bit of understanding about backups, this will help in monitoring backups. The tables below describe the jobs for each platform. I won't explain each job since their description captures the idea entirely. 

SQLServers

Postgres

Creating a view using FDL 

FDL views are similar to database views. We can use these kinds of Views for monitoring purposes. 

To create your customized View, go to FDL Console. On the top, you will find the options for the FDL Console. Open FDL, then select New Console.  

The FDL console will output a new window. By default, the name of the console will be FDL Console #1. Before starting to query with FDL, we need to set a way of returning the values. First, hover over the Results tab. Select Table result so that returned values are in tabular format. In contrast, if you need a report to show branching property relationships thru a series of directories, select Tree result. As for the bottom two, we will discuss them after running the query. 

Next, hover over Execution. Change the default to Triggered so that way you need to select the query statements to be executed. Otherwise, the FDL console will execute all statements found in the Query pane.  

Now that we have set up our preference, let us input a simple FDL query: 

The query above aims to return instances, regardless of statuses, if they are backed up or not. It includes all types of data backups. As you can observe, I did a filter in the display name. "%" is a wild character that means all characters before ("%" + text) the word back up, or after (text + "%") it will be filtered. Hence, we will only get installed jobs that have the word backup in them. This filter should suffice in filtering back up jobs for Postgres and SQL Server. 

For now, we will only run the entire query. Select the whole query and press CRTL + E or go to Execution and click on Execute. You should have a view similar to the one below. 

Changing the result to Tree result gives us the corresponding View: 

This View is somewhat confusing compared to the Table result. Remember, if you want to know each property's relationships, a Tree view is more appealing. But if you're going to format the values in a general overview, the Table result is better. 

Now that I'm happy with how my View is, I will tick the Autorefresh under Result. So, why only Autorefresh? Autorefresh refreshes the values in the result pane, while Loop re-executes the query indefinitely.  Their effects are more prominent when the FDL is running continuously. 

Afterward, proceed to View and select Attach. A window will prompt to name the FDL View. The FDL View should be available in your dbWatch console. 

Managing Backups using Management Module 

After dedicating a view for backups, let us see how we can utilize it for managing backups. With the tabularized result, we want to know which of our backups are struggling. To do that, input "alarm" in the Status filter. This filter should only return values with a red status. 

I have chosen "Localhost – MSSQL 2019 DevEd" to manage its backups in the example above. First, we proceed to Management Module, then click Test > "Localhost – MSSQL 2019 DevEd">Backups. You should see a view like the one below: 

In Backups, you will see lists of databases and their backup information. In the illustration above, the database TestAguy has all three types of backups. Let's check the history. 

Clicking on Backup History, the latest backup available is on February 5, 2021, at 3:43:06 PM. The information backed up is update-to-date as of November 12, 2020, at 2:29:33 PM and contains 80 records in total. 

After verifying the information, go back to Backups, then go to any branching trees: Full (type D), Incremental (type L), and Transaction Log (type L).  

In the image above, we will be running a full (top-most part), an incremental  (middle portion), and a log backup (bottom-most part). The last two can be executed with default values or running it usually. For simplicity, I will use "Run Incremental backup (default values)."  

When running a backup normally, a window above will pop up. The backup can append your previous one, provide a checksum after backing up, creating a new directory if the one your provided does not exists, and so on. You will skip the previous step if you decide to run it with default values. 

Apply the same method to all the remaining databases. Following this, you can either wait for your jobs to execute as schedule or run them manually. Afterward, go back to your FDL View. Backing up all your databases should fix the backup issue.

Take note when there is no existing backup present, the management module will not detect it. It would help if you initiated it with any of the management studio available. Also, the same process shown is identical to Postgres. 

--------------------------------------- 

If you have any questions or would like to know more about dbWatch, please contact us through sales@dbwatch.com.

For backups using FDL or Management console, you can directly contact the author through email: rey@dbwatch.com.  

For more information, visit www.dbWatch.com or wiki.dbWatch.com  

Other Blogs:

Monitoring MyISAM Performance with dbWatch – a Guide

From SQL Instance Management to Database Farm Management