Transaction Log Backup Archives

As was discussed in a previous blog, (The Most Important Role of a SQL Server DBA) Backups and Recovery are the cornerstone of all successful SQL Server DBA careers. Exactly how much attention is paid to backups until something drastic happens? NOT AS MUCH AS SHOULD BE!

What is a SQL Server Backup?

A backup is the process of putting data into backup devices that the system creates and maintains. A backup device can be a disk, file, a tape, or even the cloud. Easy enough!

Backups are your Keys to Success

There are four different methods of backup in SQL Server:

Full Backup – A Full Backup is a copy of the entire database as it exists at the time the backup was completed. It is all-encompassing; every detail of your database (data, stored procedures, tables, functions, views, indexes, and so forth) will be backed up. This is why the Full Backup is the foundation of every restore sequence. These Full backups can be quite large and put a strain on your storage capacity. Despite the name, a full backup will only backup the data files; the transaction log must be backed up separately. To ensure you can recover to the point of failure quickly, you will want to also utilize Differential and Transaction Log Backups (we will cover these next). Without a Full Backup, your Differential and Transaction Log Backups are useless as you cannot restore a database without a Full Backup!
Differential Backup – Differential Backups capture only changed data from the last Full Backup. Simple terms: this will backup only the changes since the last Full backup, not backup the entire database. In the event of an outage, the Differential Backups can greatly reduce the time to recover. Using both Differential Backups and Full Backups will dramatically reduce required storage space, as the Full Backups can be very large and the Differentials are remarkably smaller.
- How does a Differential Backup know what data has changed? When data is changed on a page, it sets a flag. This flag indicates which extent (collection of 8 database pages) the Differential Backup will need to backup. If your Differential Backups are set to run daily, each day’s data changes are recorded and the flag will not be cleared until a Full Backup has been executed. Say on Monday you have 3 flags set, and Thursday you have 4 more set, your differential backup on Thursday night will contain data changes represented by all 7 flags, so the flags from the Monday Differential are not cleared by the subsequent Differential Backups. The flags would only be cleared by a Full Backup.
Transaction Log Backup – A Transaction Log is a physical file that stores a record of all the transactions that have occurred on a specific database; much like a recorder, maintaining a record of all the modifications. The Transaction Log Backup will capture the information and then releases the space in the log file so it is able to be used again. In doing this, the Transaction Log Backup truncates the information in the Transaction Log, but the data is not deleted, it is stored in the backup! Without Transaction Log Backups, the Transaction Logs will run and grow nonstop, chewing through valuable storage space.
- Not only does backing up and truncating the Transaction Log manage the filesize, it also decreases the potential for catastrophic data loss. Having all the Transaction Log Backups since your last Full Backup will allow you to perform point in time restores. Transaction Log Backups are ideally set up to execute every few minutes to every hour, depending on your company’s threshold for data loss.
Copy Only Backup – A Copy Only Backup doesn’t change the Differential. It can be a full backup in that it is a record of the database as it exists when the backup is taken; however, it does not reset flags. It is ideal for doing a full backup in the middle of the week without disrupting any other backups. A Copy Only Backup can be used in a Dev space for troubleshooting, or preparing to move to a new environment.

Here is an example of a solid weekly backup plan which uses Full, Differential, and Transaction Log Backups:

Sample Backup Plan

Okay, great…backed up, now what?

Okay, so now you know what SQL Server backups are, a description of each backup type, an idea of how and what they back up, and have an idea of a good plan of action to create a solid backup plan. So, why are backups so important? Do you know how easy it is to accidentally update or delete data? It is just one T-SQL statement away with the wrong filter, or no filter at all. Having good up to date backups and being able to restore them is the difference between looking like a hero and being forced to find a new job. Backups are your foundation for Recovery.

Please come back, this is the 2nd in a series of blog posts regarding SQL Server Backups and Recovery. See you next time when we begin to discuss SQL Server Recovery Models. Thank you for reading!

Smart Differential Backups

Databases are getting bigger, not smaller. More storage capacity is needed for these backups. Backup compression might hurt yourÂ storage capacity. Today, I am seeing more policies include full and differential backups along with transactional log backups. Differential backups are used to offset daily full backups. Typically people will use time increments as the basis for when backups should occur.Â Itâ€™s very common to see automated jobs that do weekly full and daily differentials to reduce storage capacityÂ needed for backups.

How often does your data change?Â Is the rate of change very consistent or does it change depending on the week?Â Â Letâ€™s assume this week itâ€™s Tuesday and over 80% of your data pages have changed. You are not benefiting from taking daily differentials for the rest of the week. The opposite goes for data that doesnâ€™t change that often.Â Maybe you can save a lot of space by doing less frequent full backups.

Leveraging smart differential backups could greatly reduce your storage footprint and potentially reduce the time it takes to recover.

In SQL Server 2017 you can see exactly how many pages changed since your last full backup. This could be leveraged to determine if you should take a full or differential backup.Â Backup solutions and backup vendors will be better for this.

select CAST(ROUND((modified_extent_page_count*100.0)/allocated_extent_page_count,2) as decimal(6,2)) AS 'DiffChangePct' ,modified_extent_page_count ,allocated_extent_page_count from sys.dm_db_file_space_usage GO

Smart Transactional Log Backups

Â The time your users are offline while you are recovering to the point of failure is critical. It could be the difference between keeping and losing customers.Â Â Point-in-time recovery is mandatory for a critical database.Â Transactional log backups have to be restored in order.

Recovery Point Objectives (RPO) drive how often you take transactional log backups.Â If you have a policy that says you can only lose ten minutes of data, you need transactional log backups every ten minutes. Is this really true if there were no changes? What if your RPO is driven by the amount of data loss and not the time of the loss?Â Either way, you can now control when transactional log backups occur based on the amount of data that has changed since the last transactional log backup.

SELECT name AS 'DatabaseName',dls.log_since_last_log_backup_mb, dls.log_truncation_holdup_reason, dls.active_vlf_count, dls.active_log_size_mb FROM sys.databases s CROSS APPLY sys.dm_db_log_stats(s.database_id) dls

This post was written by John Sterrett, CEO & Principal Consultant for Procure SQL.Â Sign up for our monthly newsletter to receive free tips.Â See below for some great related articles.

Tag Archive for: Transaction Log Backup

A Beginner’s Guide to SQL Server Backups