We’re ending our ChatGPT series with a DBA essential; backups and restores. As a DBA, it is our responsibility to ensure that data is protected and recoverable in the event of an emergency. The best way to do this is through taking frequent backups of your data. However, just taking backups is not enough. Throughout this post, I’ll cover some essential backup strategies I like to use, methods for restoring data, and how to make sure your data is not corrupted.

Recovery Models

There are three different recovery models a database can utilize. Each recovery model has its own advantages and implementations that will help influence your decision.

Simple recovery model

If a database is in the simple recovery model, that means that the transaction log is automatically truncated after each checkpoint. This means that the log space used by completed transactions is reclaimed for reuse, preventing the transaction log from growing excessively.

There is less administrative overhead if a database is in the simple recovery model due to the absence of transaction log management, however, there is a tradeoff in the sense of data loss. We’ll talk about this more later, but there are no transaction log backups in the simple recovery model, thus, forcing the reliance of full and differential backups for data recovery to move to the forefront and reducing the granularity of data recovery.

Full recovery model

If a database is in the full recovery mode, then the transaction log for that database starts to play a more vital role. Unlike the simple recovery model, the space in the transaction log does not get reclaimed after a transaction completes. Transaction log backups are needed to reclaim this space.

There is more administrative overhead when databases are in this recovery model, however, you gain much more control over data recoverability due to the utilization of transaction log backups.

Bulk-logged recovery model

The Bulk-Logged Recovery Model is designed to optimize logging for certain bulk operations, reducing the amount of log space consumed while still providing the ability to recover most operations. It is a hybrid between the Simple and Full Recovery Models.

In the bulk-logged recovery model certain bulk operations are minimally logged, meaning that they consume less log space compared to the Full Recovery Model.

NOTE: It is not really common to see the bulk-logged recovery model used out in the wild. It’s there and has its use cases, but you mainly see the simple and full recovery models used. For the remaining duration of this post, I’ll just be referencing simple and full since you tend to see those the most.

Backup Types

Full backup

The Full database backup is a snapshot of all the data and objects in a database as it sits on disk. This is basically a point in time picture of the entire database. Databases in both the simple and full recovery model use the Full backup for restores.

Differential backup

The Differential database backup functions as an incremental backup type. When running a Diff backup, any changes captured since the last Full backup are recorded. Both the simple and full recovery models leverage the Diff backup.

Transaction log backup

The Transaction log backup, or more commonly known as a t-log backup, records the transaction log of a database. It stores the logged transactions that are made against a database. Databases using the Full recovery model utilize the t-log backup.

Backup Cadence

Now, depending on who you talk to, you may get a different answer on when to run each type of backup. I like to have the following backup cadences for the following recovery models:

Simple recovery model backup cadence

If your database is in the simple recovery model, I like to use the following backup cadence for user databases:

• Weekly full backups
• Daily differential backups

Full recovery model backup cadence

If your database is in the full recovery model, I like to use the following backup cadence for user databases:

• Weekly full backups
• Daily differential backups
• 15 minute log backups

If you follow the above cadence for user databases, then you will have great recoverability. There is a special consideration for system databases, though. For system databases (master, model, and msdb) I like to use the following backup cadence:

• Daily full backups

The reason for this is because those databases are small in size, critical to server availability, and encounter infrequent changes. This allows you to have daily recoverability points for your system databases should you need them.

Tools for Reliable Backups

There are many tools that can accomplish SQL Server backups for your databases. Just know that some are better than others. I prefer the route of native SQL Server backups. This means that you rely on SQL Server Agent jobs to schedule and coordinate your backup syntax to carry out your database maintenance. However, there are other 3rd party tools out there that support SQL Server backups like Redgate, Veeam, Actifio, and others of the sort.

Circling back to native backups, one tool that is absolutely amazing, and I happen to have a lot of experience with is Ola Hallengren’s Maintenance Solution. There you can find detailed information on Ola’s native SQL Server scripts and jobs to automate all of your database backup maintenance.

Backing up your Databases isn’t Enough

Database backups are essential, but they’re not enough to ensure data integrity and security. Ola Hallengren’s Maintenance Solution also offers Database Integrity Checks. There are system and user database integrity check jobs that Ola offers that will scan your databases for corruption and integrity issues. These jobs go hand-in-hand with the provided backup jobs because before your databases are backed up, they can check for the above mentioned issues.

Adding on, backing up your databases to one location is not enough. You should have a process in place that backs up your backups to another offsite location. That way, in the event your local backups become corrupted or unavailable, you have an avenue for restore and data recovery.

Testing your backups is another important aspect of ensuring data recoverability. There are many easy implementations of processes that will restore your backups to another server run integrity checks against those database backups to ensure there is no corruption in your databases.

Restoring your Data

There are many avenues you can take to restore data in the event you need to. Here are some of the approaches I’ve taken in the past:

• Native SQL Server Scripts
• PowerShell
• SSMS GUI
• SQL Agent Jobs

The best piece of advice I can give you is to practice with the multiple approaches I’ve given you. The reason for that is because in the event of an emergency, one or more approaches may not be available at the time. Learning more than one way to complete any task, let alone database restores is essential to ensuring your databases are protected and recoverable.

Special Considerations

There are some additional checkboxes I like to make sure are ticket to ensure that my backups run smooth, efficient, and are protected:

1. Turning on trace flag 3226
   • This will suppress successful backup messages in the SQL Server log. That way there is less noise in there.
2. Turning on backup checksum default
   • This is an sp_configure change that will default database backups to use checksum to ensure data consistency.
3. Turning on backup compression default
   • This is also an sp_configure change that will default any and all database backups to use compression, weather specified or not. This is extremely useful in the event adhoc backups are taken, or 3rd party backups are used that do not natively call for compression in their code.

The End of a Journey

It’s been so much fun going through this ChatGPT series and compiling some cool topics to share with you. I hope you enjoyed reading and following along, and I really hope you found some of this useful to you. If you haven’t already, feel free to check out the other blogs in the ChatGPT series.