About the Author:
Rey Lawrence Torrecampo is a Pre-sales Engineer for dbWatch and full-time Database Administrator. He has extensive knowledge in Postgres and MSSQL database management systems with SQL as his most proficient language. Coupled with his experiences in Data Science and data migration, he has confidently handled roles in data migration job developments, data analytics and data cleansing.
This blog is a continuation of Backing up your data with dbWatch Control Center: a practical guide on how you can monitor and manage your backups. Previously, we discussed what database backups are there and how to apply them using dbWatch Control Center. This blog will go over recovery models, schedule backups, and recover your backups with dbWatch Control Center.
What are the recovery models?
When a database experiences a hardware failure or other issues resulting in a data loss, the user can restore the state of a database based on its configuration. Recovery models are those database configurations of what type of backup and what recovery method we should utilize during a database failure.
Generally, there are three types of models in SQL servers: SIMPLE, FULL, and BULK LOG. However, for this blog, we will only go over the SIMPLE and FULL recovery model.
In a simple recovery model, your goal is to restore a database base on its latest backup. Consequently, this recovery model is insensitive to point-in-time recovery. As seen below, you restore the most recent full backup and restore the rest of its differential backups.
Contrast that to a FULL Recovery model where transaction logs will enable point-in-time recovery. The FULL recovery model follows the SIMPLE recovery model, but you add another step into the mix. In FULL recovery, you will use transaction logs to obtain the change in your databases.
The application of Database Recovery Models becomes useful during a point of failure. Let’s use the example below, Figure A1.
Figure A1 – Point of failure experienced at 1412H UTC
You have a backup running every day at 1200H UTC. Your differential backup runs every day at 1500H. You backup your logs every 30 minutes, giving you a window time of 30 between any point of failure. Suppose a crash happened in 1612H. What will be your course of action?
If you are using a SIMPLE recovery model, the latest backup you can recover will be at 1500H UTC, resulting in an hour and 12 minutes of data loss. Maybe this kind of data loss is tolerable to your organization’s business continuity plan, but you still lost more than an hour of data.
In contrast, if you are using a FULL recovery model, the latest backup you can recover will still be at 1500H. Plus, a point in time recovery of an hour from the most recent backup means you can recover until the 1600H mark, giving you a 12-minute data loss.
Depending on what model you are using, it will always coincide with your organization’s business continuity plan. You can set the Transaction log back up for every five minutes or an hour. It generally depends on your choice of design.
Automating our database backups
In our previous blog on database backups, we tackled how dbWatch can help a DBA backup database. For consistency, we will use dbWatch to schedule our backups. We will only use Full Backups for simplicity since we intend to show how backups and restoration will work with the dbWatch Control Center.
Before we start, our first course of action is to create a table having data in it. As we could see below in Figure B1, I provisioned database test_backup and table test. Inside the table test, I inserted a total of 37 records.
Figure B1 – test table with 37 records
Figure B2 – Create a backup job
After provisioning a test environment, create a backup job for your database. You can access this option by opening your Instance > Databases > database name > Backups. For this instance, we traversed “SQL Server – 2019 DevEdition – Local” > Databases > “test_backup” > Backups. Afterward, right-click on Backups and select “Create new job – Backup test_backup database.” This option will create a full backup. (See Figure B2 Left Image).
A window will appear. Provide the information you need and click OK once accomplished. In this example (Figure 2 Right Image), I selected with compression, and the job runs every Tuesday at 14:00 hours local to my time.
To check your job from the same directory, Go to SQL Server Agent and access Schedules. (Similar to Figure B3)
Figure B3 - Instance > Databases > database name > SQL Server Agent > Jobs > Schedules
Repeat the same procedure with Transaction Logs. It follows the same Full Backups methodology, but the difference would be to click on “Create new job – Backup test_backup transaction logs.” Don’t worry; after creating a job, that option will be disabled.
Figure B4 – Creating a transaction log
We will schedule a backup running every X minute, as seen in Figure B4 for transaction logs. For that example, we will set the frequency and interval to 5 minutes. To lessen our database burden, we will only provide a window between 2 pm and 3 pm.
Let us look back at the backups created for Full and its transaction logs once setting up everything. As expected, the dbWatch Control Center backups up the targeted database every 2 pm; subsequently, it also backs up logs every 5 minutes.
Practical Applications – Restoring a Full Backup with dbWatch
After setting up our environment, I will demonstrate how to restore a database with a Full backup. In Figure B6, underneath the select statement, two methods are shown: dropping a database and truncating a table. For the first part, we’ll truncate the test table.
Figure B6 – Counting records after truncating test table
As we can see in Figure B6, I removed all the contents of the test table.
Figure B7 – Restoring a Full Backup
Go back to the Backup directory and Select Full backup. In your dashboard, you should see the latest full backup. Click on “Restore from backup,” and a window should appear. On that window, configure the restoration details.
For this example (Figure B7), I opted to set the database to Multi User mode and Force Replace to, Yes, force the databases to restore the full backup. This one of the many ways to simplify the process and not the correct way of doing it. As a DBA, you will set your database recovery base on your needs.
Once you are all set, click OK. It will restore our database to our 1400H saved state.
Practical Applications – Restoring a Transaction Logs with MS SQL and dbWatch
Now that we know how to do a full backup, we’ll go over how to accomplish importing transaction logs. Aside from dropping the databases, we will also add another condition for testing:
Without modifying anything after, these two set conditions should provide the correct indication that we have successfully recovered all associated transaction logs.
Before we start, we should take note that our file extensions are in TLOGs.
We will use another method of obtaining these files. If you need to back up your transaction logs, you have the option to back them up with dbWatch Control Center, as seen below in Figure B8. SQL Servers will be able to read those files with a .bak extension.
Figure B8 – backing up transaction logs using dbWatch Control Center
Alternatively, you can stick with maintaining them as TLOGs. Either way, TLOGS can directly be capture by Management Studios. To open the logs, select your database > Go to tasks > Restore. You’ll have the option to restore using a database or through files.
Figure B9 – restoring through database/files and filegroups
Either option is viable in restoring your database with MS SQL Server, as seen in Figure B9. After you have restored your database to its former state, do a confirmation test. Like how I check the test table using a select query seen in Figure B10. This option is one of many ways in checking if of your database.
Figure B10 – Testing if a database is restored to the latest backup.
The question now is, can this also be done with the dbWatch Control Center? Sadly, this feature is not yet available with Release Candidate 4, and it will feature this in future installments. In the meantime, the compatibility of TLOGs with MS SQL Server helps do a Full Restore.
If you have any questions or would like to know more about dbWatch, please contact us through firstname.lastname@example.org.
For backups using FDL or Management console, you can directly contact the author through email: email@example.com.