- Host: Steve Stedman
- Topic: Backup Types and Retention Periods
- Recording Date: October 7, 2025
- Listen on Spotify!
Stedman SQL Podcast Season 2 Episode 26 Backup Types and Retention Periods
In this episode of the Stedman SQL Podcast, Steve Stedman explores one of the most essential topics in SQL Server management—backup types and retention periods. Learn the differences between full, differential, and transaction log backups, how they work together to ensure data protection, and how to determine the right retention strategy for your organization’s recovery and compliance needs. This episode provides practical insights to help DBAs, developers, and IT managers avoid data loss, reduce downtime, and maintain reliable recovery options.
Podcast Transcript
Steve Stedman 00:16
Hey everyone, and welcome to this week’s Stedman SQL podcast. This is season two, Episode 26 today, we’re going to be talking about SQL Server Backup types and retention periods and strategies for data protection and long-term recovery.
But first, let me talk about our October promotion we have for anyone who signs up for a SQL server performance assessment or SQL Server health assessment, we have free one year of our monitoring service with any of those purchases during the month of October. I’d like to call out a recent popular episode of The Stedman SQL podcast. This was season two, Episode 22 where we talked about building a career as a SQL Server DBA. And that’s one of those that a lot of good stuff in there to hopefully help you if you’re on your way becoming a senior DBA one day.
So, for today’s episode, yeah, let’s take a look at this. So we’re going to look at a SQL Server Backup types and retention periods and why long term retention matters. Now, the thing here is backups may be one of the most critical things a DBA to be doing to take care of their SQL Server for data protection, compliance and disaster recovery, but so often we see that it’s not being done well or not being done, not being done as good as it could be. So the target audience for this is really DBAs and developers, but also IT managers and other management roles who are making decisions on what kind of a backup you need. One of the common things that I run across, and I see this when we’re working with a lot of our Managed Services or our assessment customers when they first start working with us, is that a lot of people have got good sized databases, and by good size, I really just mean any database that’s big enough that the backups are becoming an issue for retention. And oftentimes, what we see is that somebody will have daily full backups of their SQL Server databases, and that’s it. And then when we look at it, oftentimes we see that they only have three to five days worth of full backups being kept around, because those sold, those full backups, are so darn big that they don’t have the space, or they don’t have the budget for the space to be able to keep around two months or four months worth of those full backups every single day, because that can get really, really expensive on disk space over time.
So, what I’m going to jump into and talk about here is, what if instead, you could have two months of backup history that could give you a point in time recovery, rather than just once every 24 hours using about the same amount of disk space. That’s what that’s what we like to do. And we’ve done this with several clients. Just recently, we worked with one where we went from five days worth of full backups to about a month and a half, we went to six weeks worth of backups that gave a point in time recovery that they can recover at any point over that last six weeks. So we and we were able to do that without really using much more disk space than what they were already using for that five day backup. And we’ve done this multiple times. So if you’re a client that we’re working with, and you hear that, it’s probably not you, it’s the previous client that we worked with to do the same thing. All right, so what I want to cover first, because we go into this, are the types of SQL Server backups that we have. So let’s cover the type of SQL Server backups. And the simplest backups that most people learn when they first start doing backup work is full backups. And basically the way a full backup works is that it takes a complete snapshot of the database, and if you’re not using any kind of compression, then that backup file size is approximately the same size as your whole database. So if you have a database that’s approaching a terabyte in size, then that full backup uncompressed is about a terabyte in size, typically. So full backups are kind of the easiest thing to use, and that’s why people come across them first. Because the other types of backups that we’re going to talk about, differential and transaction log backups, they rely on the full backups being there. So full backup is always the starting point for recovery. You’re going to take a complete copy of that database and put it back in place. Yes. Now with full backups, I said the data, the backup file is typically about the same size as the database if you don’t have compression on, but if you turn on backup compression, you may get something maybe 40 to 80% of the size of that original database file, depending on the type of data you’re storing, and that means you can, just by, simply by turning on compression, you can be taking up less space for those full backups, but you can also be getting those full backups to write faster and to be read faster, because there’s less IO that has to happen when they’re compressed. Okay, so full backups, it’s just, here’s a complete copy of the entire database, and when it gets restored, it just goes in and replaces whatever database is there following that.
The next one that we can use, or that we typically talk about using, is differential backups. Now, when you take a full backup of your database, as long as you don’t use the copy only play, but a full backup of your database clears a section of the database that tracks what data pages have been changed. Now you then, after that full backup is done, you start making changes in your database, and any of those pages that get changed get tracked so that they can be backed up as part of a differential backup. And what a SQL Server differential backup is, is, it is a backup of everything that has changed in that database since the last full backup. And the way it works when you do a restore is, you start by restoring your full backup, and then you can apply a differential backup, just one differential backup on top of that full to get it up to the specific point in time that that differential backup was taken. Differential backup sizes vary depending on how much your database is changing. But a common thing we see is that if somebody has a good sized database, assuming it’s not like data warehousing, that’s rebuilding everything every day, or something like that, but let’s say you’ve got that terabyte size database, and it’s really unlikely with normal OLTP type system, that every single thing in that database is going to be changing every day. So if you were to take a full backup and then a day later take a differential backup, that differential backup would only be the size of all of those things that had changed. And then, of course, it would be smaller than that if you have backup compression turned on.
So using differential backups rather than full backups, you can save a lot of space and time because those differential backups write a lot quicker. So let’s say you got a terabyte of database that full backup is going to be terabyte, and then whatever that is compressed, your differential backups are just going to be based the size is going to be based off the rate of data changing every day. And let’s say you have 5% of your database that is changing every day. Maybe that’s highest for some, maybe it’s low for others, but what that means is that if you did a full backup on the weekend and you did a differential backup on Monday, that would be that differential backup would be about 5% of the size of that full backup. Now, if you had 5% change over the next day, then that differential backup might be 10% of the size of that full backup, and then after three days, it might be 15% of the size of the full backup. So the differentials keep getting bigger and bigger based off of the number of changes that are happening every day, and depending on the amount of a rate of change on that database, those differential backups may get to a point where it’s not worth doing a differential backup. There’s so much that has changed in your database that you take another full backup and reset those differential flags so that you’re back to small differential backups, full backups. And differential backups will work with whatever recovery model you’re using in SQL Server, and they will get you to they will get you to the point in time of the last full or the last differential by either doing just the full or the full, plus a differential backup to get you or differential restoring each of that point in time.
So next we have transaction log backups. And what transaction log backups do is they capture all of the transactions that have occurred since the last transaction log backup. Now this is what is needed for that point in time, recovery in a full recovery model. Now transaction log backups don’t work with simple recovery model, but they do work with full and bulk logged recovery model. Now, what transaction logs do? It’s, I mean, the transaction log is there and it’s tracking everything that happens, so that if you abort a transaction, it can get rolled back. But if you’re in full recovery model, those stay written in the transaction log until you run a transaction log backup one day or in the next two minutes. So with transaction log backups, if you have a certain amount of data that’s changing over an hour, whether you’re doing those transaction log backups every 30 seconds or simply once an hour, at the end of that hour, all of those files are going to add up to be about the same size, meaning, if you’re backing up every 30 seconds, you’re backing up a bunch of really small files frequently, or if you’re backing up once an hour, you’re backing up a big file with a lot of changes infrequently. Now it’s usually better on the system to do a bunch of small backups over time, rather than one big backup once an hour or even once a day. Part of the danger is if people have full recovery model turned on in their SQL Server database, which says, keep all of these things in the transaction log until they’ve been backed up. What happens if you don’t have log backups is those transaction log files just continue to grow and grow and grow, and if you’re not backing them up, you will eventually run out of disk space. And I’ve seen this on server servers that somebody installs it. They have databases in full recovery model. They turn on full backups, maybe using differential backups, but they’ve never turned on transaction log backups. And then it might be three months later, or it might be five years later that you find out that every transaction that has ever been done on that database has been logged into that transaction log, and it’s just chewing up all of the disk space. So you want to make sure that if you’re in full recovery model, that you have transaction log backups going otherwise, it’s sort of pointless to be in full recovery model.
Typically, there’s also file and file group backups, and these are useful for really large databases where you’re backing up specific files or file groups. We’re not going to go into a lot of detail on that, because most of the environments that we work in don’t really take advantage of that. So keep in mind that file and file group backups are something that can be done if they’re needed. Now I touched earlier on recovery models, and I mentioned that if you’re in full recovery model, that you need to be able you need to take transaction log backups in order to keep your log file from growing. Same is true for bulk logged recovery model, because it also tracks those transactions into the transaction log. But with simple recovery model, the transaction log is used until the transaction completes, and then at that point, whatever space was allocated for that transaction gets freed up once the transaction has been committed or rolled back. So if you have a database that you’re not planning to do log backups on, you don’t need point in time recovery, you should probably have your database a simple recovery model. If you have a database that you need to do point in time, recovery, full and bulk logged recovery models are the way to go to get those point in time, transaction, log backups working.
One of the things that we always look at is, how do we choose the right backup type based on the database size, the workload and the recovery needs? Here’s the question, do you have enough disk space for full backups once or twice a day for whatever your expected retention period is? And some people will say, Oh my gosh, that’s five days I can have keep those four. Others might say, Yeah, I’ve got plenty of space to keep that for 14 years because my database is so small. Well, if that’s the case, maybe a full backup every day is okay. But if you are constrained on this space, we want to look at is some options on how you can do those backups in a better way to get you more longer term retention with without necessarily adding more space. Let’s talk about retention periods and what this really means. So I like to ask the question, and I don’t like to ask this of the IT Crowd, typically, that we’re working with, but it’s a question that should go to management, because it really comes down to the business case or the business need, is, what type of backup retention do you need? And some
people will say, Oh, well, three days is great. And then we sort of look into what could go wrong in those three days. What if it’s a Friday? Somebody accidentally drops a table right before end of day, Friday, and nobody noticed that? Notices that until Monday? Well, at that point, three days have passed, and that Friday backup, or the Thursday backup that would have been where the data was there, may be purged at that point, and you have no way to get that dropped table back. Okay, but there’s a lot of other factors that go into this, other than like catching something that might have been dropped or deleted. The other thing is, what are the business requirements? What about regulations? GDPR, HIPAA, SOX, PCI, other maybe medical related things that depending on, well, I guess that’s HIPAA. HIPAA would cover that. But what are the requirements? Regulatory? Requirements that are put on your business, and some of those regulatory requirements have specific backup retention that needs to be kept for a certain amount of time. It might be that, depending your business, you need to keep three years of backups. Might be have to have seven years. It might be that five days is okay, but I’ve never seen an environment where five days really was okay when we talked to management on it. But that’s where you get to the storage and cost, because the storage of all these backups can get very expensive over time, depending on your retention needs. So what we want to look at is different retention periods, and I like to consider short term, short term being like, I don’t know, a couple of days up to a week or two for operational recovery. This would be things like the drop table, or somebody accidentally does a bad update on the table, things like that. Then you’ve got medium term, the things that are, like, a couple of weeks out to maybe three to six months. These are things that are for auditing and compliance checks. Worked with a client a while back where they went through two years worth of backups in a legal case in order to be able to find out who did what and when. And that kind of thing can be really important, depending on the type of business you’re in. And then we have long term backups. These are the backups that you want to keep around for more than a year, sometimes as much as seven years, and they’re for legal, regulatory or historical purposes. Now these are backups that you may never use them, but you need to have them so that if you get audited by something, that you can go back to that point in time, four years ago, and prove exactly what happened or what the data looked like at that point in time. Okay, so what we want to look at next is why this long term retention matters. And I think on the long term retention, it’s one of those things that varies a lot from business to business. And let’s look at a couple examples. Here. A hospital may be using a SQL Server for patient records, and there may be rules that they need to retain data for seven years based off of HIPAA requirements. Now it might mean that the data doesn’t actually ever get deleted out of a database, and your current database may have that seven years worth of data in it, but the scenario may be to go back and say, let’s say it’s, it’s some kind of a HIPAA filing, or maybe it’s a lawsuit over some something that was done incorrectly on a patient. Well, you want to be able to prove what the data looked like at the time that patient was treated, maybe four or five years ago, rather than what it looks like now, because one could argue that the data now may have been modified to cover something up. Now, I’m just kind of looking at worst case scenarios. I’m not saying that that would happen in any of your environments, but someone could argue in a legal court of law that that is not the actual right data. We want to look at the data that was there four years ago. Without those long term backups, you can’t really prove what it looked like at that point in time.
So that’s one of those things that we always look at different kinds of storage, and if all you have is a network share and that spinning disk, or whatever it may be your SSDs, that may not be the best long term storage options. So we look at things like Azure Blob Storage or other cold storage options for those long term retention scenarios. Gosh, 1520, years ago, before people had internet speeds that were big enough to put their backups like this to the cloud. We used to do backups for people onto a USB mounted external hard drive. I worked with one client where they did their long term storage on that and it put everything on that hard drive. And then once a week, someone picked up that external hard drive. They had three of them in the cycle. One of them would be in transit, one of them would be in the server and one of them would be in a safe deposit box. And they were fortunate that their building was very close to their bank within a safe deposit box, but they would, once a week, put that drive in the safe deposit box for the long term storage. When we think about other cold storage options, like Azure Blob storage, or AWS solutions what we’re really looking for there is a way that you can hold on to that backup for a long amount of time without a huge cost associated with it. Now, keeping that in mind, it might be a little bit more work to get those back when they’re needed, but as long as you can get them back and use them for whatever purpose that’s important.
Now let’s look at another scenario, financial auditing. Let’s say you’re a financial firm, like, I don’t know, a bank or and you have tax requirements that you’re paying. Well, there’s different jurisdictions and different things that talk about how long you have to keep your data to prove for your taxes, what if you what? If your database, if somebody deletes something out of your database, and then you get audited by the IRS, and you have to look at something from three years ago, but your answer to the auditor is, gee, we don’t have that data because somebody accidentally deleted it. Well, wouldn’t it be great if you could just go grab a copy of that database from three years ago and look at what it actually was at the point that those taxes were done. Well, I think with the IRS, you’re sort of assumed guilty until you can prove that you’re not on tax audits. So it’d be great to be able to have those backups around for that. So one way to do that, don’t think that if I’m saying that you should have three years or seven years worth of backups, don’t think that I’m telling you that you want to have every single backup stored every day for that full three year period. What you can do is do something like keep a monthly full backup on a secure off site location with clear documentation on how to restore that and how to use it, so that in the event that that audit does happen, or in the event there’s something you have to prove or go find or fix that’s missing, you can go grab that backup out of that secure off site location. It might be a pain to get it back, but that’s okay, because you’re probably not going to be doing it very often, bring it back, make it so it can be used, and then that covers it there for your financial auditing scenario. In that case, another one that I think is more frequent, that we use quite a bit is data loss from human error. I mean, we work with a lot of clients where we’ve got a call that says, whoops, somebody dropped a table, or somebody deleted a column, or somebody updated a column with the wrong formula, those kind of things. And when we find them, the question with those is, how can we get something back quickly and fix it. Just bring back a single column or single database. Well, we can’t just bring back a column or a single table. What we do is we restore a copy of the full database somewhere, and then we copy and update the data to fix what was destroyed by that update statement, or whatever it may have been. So that’s why sometimes those things get unnoticed for a while, depending on how bad of a scenario it is, and if you have a six month old backup, it might be really useful in helping with that type of recovery. In that environment, it might be great to maintain quarterly full backups indefinitely or for some long term period so that you can get back to that point in time.
So the key takeaways on this is that everybody has different compliance needs, but many of them involve longer term retention. That’s more than four or five days to protect against any of these different scenarios or cases. Okay, so best practices, or the way that we will frequently do it when we’re working with clients, and I’m going to walk through my backup strategy that I started with. But it’s one of those things that depending on the system, depending on how much change you have, depending on how big your databases are, this may change a little bit, but generally the philosophy is we will take a full backup, weekly, differential backups daily, and transaction logs, some people say hourly, but I like to do transaction logs every five minutes or every 10 minutes, depending on the specific case there. And what that does is that makes it so that if those backups are being moved off of that server, and that server just completely dies, it makes it so that we can get it back. And if we’re doing five minute log backups, with losing only as much as five minutes worth of data, sometimes we’ll set that to two minutes, depending on what the specific business needs are. But what that means is that we can do that recovery and get them back to any point in time during the day based off of that, because we’re doing the full backups weekly. That’s the big ones we can keep. If previously we could keep five days worth of backups, maybe instead, we keep four weeks worth of full backups. You add in the differentials and the transaction logs, and you might be taking up less space than you were taking originally with those five days worth of full backups, and you have an entire month’s worth of options to be able to restore it to any point in time. Okay.
Now the other thing with that is we like to be able to use immutable backups, or backups that can’t be modified, meaning, once SQL Server writes that backup to some protected location, if ransomware comes along later, it can’t get that and change it or attack it in some way. And one of the things we also like to do is to make sure that we can get those backups off of the current server as quickly as possible. If you’re doing all these backups on your local server, and that server gets destroyed for some reason, the virtual machine goes bad, or whatever it may be, or ransomware. If those backups are stored on that computer, well, they’re gone too, and you’re totally out of luck. So it’s really important to get those backups off of the current server into somewhere like backup storage appliance, or somewhere like long term cloud Blob Storage things. Like that. Now the other thing that’s really important when you’re running backups is also to run check DB regularly, preferably daily, if you can, so that you know that what you’re backing up is not a corrupt database.
Now we do a lot of corruption repair at Stedman solutions. One of the things that we come across regularly is that somebody has corruption in their database, and we find out that that corruption may have been there for seven or eight months, and every one of those backups that they have, it contains the corruption. So there’s a lot of options to repair a corrupt database if you have good backups, but not knowing if you have a corrupt database or not, makes it really difficult to do that repair. So whatever your retention period is, you should have check DB running frequently enough that you know the database is not correct. So retention strategy, I’d say you’re keeping some short term amount of data, like maybe two to four weeks, like the example we just walked through with the weekly differentials and full and logs that you’re keeping, those somewhere that’s local, that you can get it back fairly quick, not on the same server, but on the local network, somewhere in some kind of immutable storage long term you archive the older backups to cheaper, slower, long term storage. There’s different types of Azure Storage, and there’s different types of AWS cold storage and other options like that, where, yeah, it might take a little bit longer to get it there, but it’s something that’s not being kept around. And then the other thing too, is to use sort of a tiered retention policy. So for instance, if something happens right now and you need to do a point in time, you really need those log files to get you back to that point in time restore. But if, let’s say, you’re asked to do a restore from three years ago, well, monthly granularity, or even weekly or quarterly granularity might be good enough for those type of older backups. So what we like to do is, for the short term, keep very granular, very frequent backups, and as the retention period goes out, we keep less frequent backups. An example of that going back to our fulls, diffs and transaction logs, let’s say we’re keeping four weeks of full backups. Maybe we’re keeping three weeks of differentials daily, and we might be keeping just one week worth of transaction log backups. Really depends on the business and the need for point in time recoveries, but generally, when we’ve had to do a point in time recovery, it’s usually something that’s happened recently that we have to get back to. So a couple of key things to keep in mind is testing backups. You want to regularly test your restores to make sure that they’re working. There’s an example that I use in my backup training course, which I’ll mention a minute. We call it Schrödinger’s cat backup, which is from this example of Schrödinger’s cat, which they it was some theoretical physicist example, where you don’t really know if the cat’s alive or dead in a box. But the point is, you really don’t know if your backups any good or not until you’ve actually tried it. So it’s really good to be able to regularly test those backups. And maybe it’s taking it from production to your test environment and you do a restore of an actual point in time backup to get you there, or maybe it’s just some automated script that runs once a week and verifies can restore a backup somewhere. All these type of backups that we talked about should be automated using the SQL Server Agent or other tools like the OLA Hallengren scripts to help automate the backups and retention and compression and cleanup and all that and monitoring them. Because back to the corruption example, we’ve had people call us with corruption and I say, well, what backups do you have available? And they say, well, we thought we had backups running on whatever interval, but they haven’t been working for the last eight months. Well, that’s no good. So you want to be able to monitor the success or failure and alert on that so that you know when those backups have failed, or when they’re not available, or when things like that. That’s part of what we do with our daily monitoring that we have for our managed service customers, is that if they if there’s a backup that runs and it fails or it doesn’t go for a certain amount of time, we get alerts on it, so we make sure we can fix it for the client, on to some of the common questions and pitfalls that people run into.
So first off, question that we see frequently is, how do I balance storage costs with long term retention? And I think the key to that is, don’t do just full backups. Do a combination of full backups, differential backups and log backups with different retention times. That might be an example of you have a monthly backup that goes to some place that it’s kept for a longer term retention. Weekly polls go in another place you have your differentials go with the different retention periods there, so that you’re able to get that longer term retention without. Trimming up as much space. Also, backup compression is important on there. The next question we hear once in a while is, what’s the best way to test an older backup, like a three year old backup? Well, the best scenario for that would be, if you have a test server, just try and restore it on the test server to make sure it works, to make sure it does everything you expect it should do, and you’ve got everything you need. You don’t have a test server. Oftentimes we’ll have people do that restore onto a production server, where instead of the database name, we would restore it as database name underscore and the date it was restored, or the date the backup came from, something like that, so that it can be differentiated from the normal production database.
So some of the common pitfalls that we’ve run into is where people don’t test their backups regularly, and they get in a situation where they think they have backups going but one, they haven’t tested them and they don’t know how to restore them, and two, they don’t know that they’re not working. This is really, I can’t stress enough how important testing those backups are. Another common pitfall that we run into is full recovery model databases without transaction log backups. And that’s just silly. There’s almost no scenario where that’s worth doing. So either you need log backups for your full recovery model database, or you may consider flipping that full recovery model database into simple recovery model if you’re not going to do transaction log backups, because without that, your transaction log will just keep growing and growing and growing. The other thing is ignoring off site or immutable storage for disaster recovery. What if the building your data centers and burns down. What if you get hit with ransomware? What if everything in that server rack where you have your backups and your SQL Server Data and all that has gets flooded, or something, who knows, whatever, but a way to make sure that whatever disaster happens there, that you’ve got something that you can grab from an off site location, a second site, Cloud Storage, whatever it may be, so that you can do that restore and get everything back. So the key here is keep those things in mind, avoid those common pitfalls, and make sure that you have a backup retention period that matches the business needs and doesn’t bloat out your storage to the point that it’s too expensive. So key takeaways here are understand, understanding the backup types. We’ve talked about full backups, which is a copy of the entire database. Differential backups, which is a copy of everything that’s changed since the last full and transaction log backups, which is a copy of all the transactions that have occurred since their last transaction log was taken and those are all important to do pretty much any recovery that needs to be done. Now, long term retention is critical for compliance audits or late discovered errors and immutable backups are super important to be able to save, save you from ransomware or other malicious attempts, and that you need to make sure you test and automate your backups to ensure reliability. If you’re not testing your backups, I would argue that you don’t know that you have good backups. Some resources to recommend here. Well, the first thing I would recommend is Stedman’s SQL School, our SQL Server, backup and recovery course, we cover a lot of things that we covered here, and much more. And that’s available at http stedman.us/backup the URL is stedman.us/backup and that will redirect you to the backup and recovery course other items, I would say, subscribe to the Stedman SQL podcast to get more SQL Server insights. And if you need help, reach out to us. We help our clients with backup scenarios like this all the time, and we can come in and analyze and look at what you’re doing and figure out how to make sure that your backup strategy meets all of your specific business requirements. Can visit Stedmansolutions.com for additional resources or to find out more about how we can help. And all the podcasts can be watched on YouTube and Spotify. If you go to Stedman.us/Podcast you can see all the episodes and all the different options to watch there. So I’d like to say thanks for watching this podcast today. Appreciate everyone who’s watching or listening, and if you like it, please click the Thumbs Up or the bell icon to subscribe to get more future podcasts as we do them.