November 29, 2016 at 9:35 am
Hi,
Has anyone been able to successfully use Ola Hallengren's maintenance scripts to only clean up old backups and not take a new backup? I have full backups running weekly, differentials running daily, and transaction log backups running every hour. I want to keep two weeks worth of backups (full, diff, and tran), so my plan was to set the @CleanupTime for the full backup to two weeks and then, as part of the job, add two steps -- one to delete any differential backups greater than two weeks old and another to delete any transaction log backups greater than two weeks old.
I don't want to actually take the differential and transaction log backups because the differential will be almost pointless and the transaction log backups run on the hour anyway, so I could see that getting a little confusing too (already it's a little weird because the full backup takes over an hour, so transaction log backups are running during the full backup and I'm not sure how SQL handles that).
Perhaps I'm overthinking this--is there an approach, using Ola's scripts, that makes more sense if I'm trying to always keep the last two weeks' worth of full, differential, and transaction log backups?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 29, 2016 at 10:09 am
Mike Scalise (11/29/2016)
Hi,Has anyone been able to successfully use Ola Hallengren's maintenance scripts to only clean up old backups and not take a new backup? I have full backups running weekly, differentials running daily, and transaction log backups running every hour. I want to keep two weeks worth of backups (full, diff, and tran), so my plan was to set the @CleanupTime for the full backup to two weeks and then, as part of the job, add two steps -- one to delete any differential backups greater than two weeks old and another to delete any transaction log backups greater than two weeks old.
I don't want to actually take the differential and transaction log backups because the differential will be almost pointless and the transaction log backups run on the hour anyway, so I could see that getting a little confusing too (already it's a little weird because the full backup takes over an hour, so transaction log backups are running during the full backup and I'm not sure how SQL handles that).
Perhaps I'm overthinking this--is there an approach, using Ola's scripts, that makes more sense if I'm trying to always keep the last two weeks' worth of full, differential, and transaction log backups?
Thanks,
Mike
Quick question, can you outline/explain your DR requirements?
😎
November 29, 2016 at 11:28 am
Eirikur Eiriksson (11/29/2016)
Mike Scalise (11/29/2016)
Hi,Has anyone been able to successfully use Ola Hallengren's maintenance scripts to only clean up old backups and not take a new backup? I have full backups running weekly, differentials running daily, and transaction log backups running every hour. I want to keep two weeks worth of backups (full, diff, and tran), so my plan was to set the @CleanupTime for the full backup to two weeks and then, as part of the job, add two steps -- one to delete any differential backups greater than two weeks old and another to delete any transaction log backups greater than two weeks old.
I don't want to actually take the differential and transaction log backups because the differential will be almost pointless and the transaction log backups run on the hour anyway, so I could see that getting a little confusing too (already it's a little weird because the full backup takes over an hour, so transaction log backups are running during the full backup and I'm not sure how SQL handles that).
Perhaps I'm overthinking this--is there an approach, using Ola's scripts, that makes more sense if I'm trying to always keep the last two weeks' worth of full, differential, and transaction log backups?
Thanks,
Mike
Quick question, can you outline/explain your DR requirements?
😎
RPO - 1 hr
RTO - 1 hr
Are there other specific DR requirements you're looking for?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 29, 2016 at 12:21 pm
Ola's code is completely open for you to modify to suit if you need to. If the options don't allow you to do NO backups just make your own copy and remove the code you don't want to run. Easy-peasy, lemon-squeezy. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2016 at 12:28 pm
TheSQLGuru (11/29/2016)
Ola's code is completely open for you to modify to suit if you need to. If the options don't allow you to do NO backups just make your own copy and remove the code you don't want to run. Easy-peasy, lemon-squeezy. 🙂
Haha thanks, Kevin.
I know I can go in and modify to my liking, but I didn't want to re-invent the wheel if Ola had made it super easy to do with a specific combination of parameters already...
Mike Scalise, PMP
https://www.michaelscalise.com
November 29, 2016 at 2:50 pm
Mike Scalise (11/29/2016)
TheSQLGuru (11/29/2016)
Ola's code is completely open for you to modify to suit if you need to. If the options don't allow you to do NO backups just make your own copy and remove the code you don't want to run. Easy-peasy, lemon-squeezy. 🙂Haha thanks, Kevin.
I know I can go in and modify to my liking, but I didn't want to re-invent the wheel if Ola had made it super easy to do with a specific combination of parameters already...
I didn't tell you how to do it hoping you would review the documentation and come up with the trick yourself. 🙂
Just putting in a garbage database name for the @databases parameter should do the trick, right? Poof, no backups will be done (and no error thrown either thankfully). Note that backup type is irrelevant here, but is required to be a valid option or you will get an error.
exec dbo.[DatabaseBackup] @databases = '@^#*D*', @BackupType = 'LOG'
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 30, 2016 at 9:50 am
TheSQLGuru (11/29/2016)
Mike Scalise (11/29/2016)
TheSQLGuru (11/29/2016)
Ola's code is completely open for you to modify to suit if you need to. If the options don't allow you to do NO backups just make your own copy and remove the code you don't want to run. Easy-peasy, lemon-squeezy. 🙂Haha thanks, Kevin.
I know I can go in and modify to my liking, but I didn't want to re-invent the wheel if Ola had made it super easy to do with a specific combination of parameters already...
I didn't tell you how to do it hoping you would review the documentation and come up with the trick yourself. 🙂
Just putting in a garbage database name for the @databases parameter should do the trick, right? Poof, no backups will be done (and no error thrown either thankfully). Note that backup type is irrelevant here, but is required to be a valid option or you will get an error.
exec dbo.[DatabaseBackup] @databases = '@^#*D*', @BackupType = 'LOG'
Kevin,
Don't get me wrong--I'm all for exploring to gain a better understanding of the code, and that would've been my next step.
In any case, thanks for sharing. However, I'm not sure that's exactly what I'm looking for. If I put garbage in for the "databases" parameter, then how could the procedure possibly know what databases's backups to delete? And I would actually argue the backup type is relevant because the procedure needs to know which backup type I'm trying to delete.
Perhaps I haven't communicated my goal well enough. The gist of it is that I want to delete the differential backups for all user databases that are > 2 weeks old, without actually taking a differential backup--using Ola's script. I want to do the same thing with transaction log backups.
Therefore, I would expect to use something like:
EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @BackupType = 'LOG', @CleanupTime = 336, @LogToTable = 'Y'
I took a quick differential backup of a user database, waited an hour, and ran the above command with a @BackupType = 'DIFF' and @CleanupTime = 1. It looks like it didn't take a backup (which is good for what I'm looking to do) but it also didn't delete the diff I took an hour ago...
Mike Scalise, PMP
https://www.michaelscalise.com
November 30, 2016 at 12:16 pm
Ahh, well, that's what happens when you post after days of being sick and starting an international trip. :hehe:
Taking a closer review (albeit after being sick for more days and awake for going on 31 hours now) I don't think you have a choice but to alter the code. I would add a new parameter @PerformBackup and then put a conditional block around the code you find at this comment:
-- Perform a backup
I think there is actually just one line of code you would need to not execute:
EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @Command = @CurrentCommand03, @CommandType = @CurrentCommandType03, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
I would also recommend turning on the delete backups before performing backup flag.
Hope this helps.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 30, 2016 at 1:21 pm
TheSQLGuru (11/30/2016)
Ahh, well, that's what happens when you post after days of being sick and starting an international trip. :hehe:Taking a closer review (albeit after being sick for more days and awake for going on 31 hours now) I don't think you have a choice but to alter the code. I would add a new parameter @PerformBackup and then put a conditional block around the code you find at this comment:
-- Perform a backupI think there is actually just one line of code you would need to not execute:
EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @Command = @CurrentCommand03, @CommandType = @CurrentCommandType03, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @ExecuteI would also recommend turning on the delete backups before performing backup flag.
Hope this helps.
Kevin,
Sorry to hear that you're feeling under the weather. And thanks for the suggestion. I'm actually a little surprised that you would recommend turning on the delete backups before performing backup flag. What if the backup failed? You wouldn't be able to get back to the most recent good backup...
Again, thanks.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 1, 2016 at 4:34 am
Hmm, I thought you said you wanted to just delete backup files WITHOUT taking any actual backups. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2016 at 5:37 am
Ohh. My apologies. I thought you were making a general recommendation for running Ola's scripts.
Yes, I could see how setting that flag in the command might achieve the results I'm looking for. I'll give it a shot and let you know how it goes!
Thanks for the suggestions.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 1, 2016 at 7:10 am
Mike Scalise (12/1/2016)
Ohh. My apologies. I thought you were making a general recommendation for running Ola's scripts.Yes, I could see how setting that flag in the command might achieve the results I'm looking for. I'll give it a shot and let you know how it goes!
Thanks for the suggestions.
Mike
I think you may have misunderstood me. I meant to turn that on AND make a code adjustment to conditionally execute the actual backups. If you just turn it on without any sproc mod you will definitely do the backups that you don't want to do.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2016 at 7:24 am
TheSQLGuru (12/1/2016)
Mike Scalise (12/1/2016)
Ohh. My apologies. I thought you were making a general recommendation for running Ola's scripts.Yes, I could see how setting that flag in the command might achieve the results I'm looking for. I'll give it a shot and let you know how it goes!
Thanks for the suggestions.
Mike
I think you may have misunderstood me. I meant to turn that on AND make a code adjustment to conditionally execute the actual backups. If you just turn it on without any sproc mod you will definitely do the backups that you don't want to do.
Got it. I'll look into doing both this morning. Something else that just came to mind as a potential solution. Would love to get your thoughts.
As I mentioned, my goal is to have two weeks' worth of full backups and all of the associated differential and transaction log backups that would allow me to restore from that second full backup if I needed to. Full backups run once a week, differentials run every day except for the day that the full backup runs, and transaction log backups run every hour.
What if I run all of these jobs (full, diff, t-log) with a "CleanupTime" of 336 (two weeks) rather than just trying to delete the two-week-old logs and differentials each week when the full backup runs? I think that would ensure I always have at least two weeks' worth of complete backups (and probably some additional t-logs and differentials that are associated with full backups that have since been deleted). Now I'm just typing as I think, so I'm going to stop.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 1, 2016 at 7:50 am
Mike Scalise (12/1/2016)
TheSQLGuru (12/1/2016)
Mike Scalise (12/1/2016)
Ohh. My apologies. I thought you were making a general recommendation for running Ola's scripts.Yes, I could see how setting that flag in the command might achieve the results I'm looking for. I'll give it a shot and let you know how it goes!
Thanks for the suggestions.
Mike
I think you may have misunderstood me. I meant to turn that on AND make a code adjustment to conditionally execute the actual backups. If you just turn it on without any sproc mod you will definitely do the backups that you don't want to do.
Got it. I'll look into doing both this morning. Something else that just came to mind as a potential solution. Would love to get your thoughts.
As I mentioned, my goal is to have two weeks' worth of full backups and all of the associated differential and transaction log backups that would allow me to restore from that second full backup if I needed to. Full backups run once a week, differentials run every day except for the day that the full backup runs, and transaction log backups run every hour.
What if I run all of these jobs (full, diff, t-log) with a "CleanupTime" of 336 (two weeks) rather than just trying to delete the two-week-old logs and differentials each week when the full backup runs? I think that would ensure I always have at least two weeks' worth of complete backups (and probably some additional t-logs and differentials that are associated with full backups that have since been deleted). Now I'm just typing as I think, so I'm going to stop.
Thanks,
Mike
It sure would have been helpful if you had said that stuff right from the start! 🙂 Yes, that is (kinda) what you need to do to accomplish what you want.
HOWEVER!!!! If you truly want to be able to restore a full 2 weeks back you actually need to keep TWENTY days of FULL backups because as soon as you deleted that one that is 14 days old you could no longer restore the DIFF/TLOGs that rely on it. I have seen clients get FUBAR because of that critical piece of information. Note also that you must make sure to only delete backups AFTER backup success in this case, as you mentioned before. Jet lagged right now but I also seem to recall needing to keep 15 days of tlog backups in this scenario too or you lose the ability for a point-in-time restore on that 14th-day-ago too.
Hope all that made sense. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 2, 2016 at 8:22 am
HOWEVER!!!! If you truly want to be able to restore a full 2 weeks back you actually need to keep TWENTY days of FULL backups because as soon as you deleted that one that is 14 days old you could no longer restore the DIFF/TLOGs that rely on it.
I'm not sure I understand why I would need twenty days of full backups. Take this, for example.
F = Full (weekly)
D/T = Differential/T-Log (daily/hourly)
Week 1 | Week 2 | Week 3 | Week 4
Su M T W Th F Sa | Su M T W Th F Sa | Su M T W Th F Sa | Su
F D/T D/T D/T... | F D/T D/T D/T... | F D/T D/T D/T... | F
Let's say it's Sunday of Week 4 and any Full, Diff, and T-Log backups > 14 days are deleted after the Full backup is taken. That would mean Week 1's F, D, T backups are completely gone. In this scenario, with only THREE days of full backups at any given time, I can restore from up to two weeks prior. In this case, if I wanted to get back to Monday of Week 2, I only need the Full backup + any T-Logs from the time of that full backup up through the time I'm looking to restore to. Or let's say that I wanted Wednesday, of Week 3, but Week 3's Full backup failed--I should be able to pull Week 2's full backup, and again, take all of the T-Log backups from Sunday of Week 2 up through Wednesday of week 3, right? (and I know I could use differentials too to help speed up the restore process).
I can see wanting to keep 15 days worth of Diffs and T-Log backups to fully cover the two-week period, but I guess I don't understand where 20 days of full backups come into the mix. Unless you say 20 because you know that I take full backups weekly and that 20 days equates to 3 actual full backups.
Does that make sense?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply