0

I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 2 weeks of transaction logs, 5 weeks of diffs, ,14 weeks of full backups, 13 monthly full backups, and 3 beginning of the year full backups. I already asked the question and can do the first three with changing the @CleanupTime in each of the respective jobs, but I dont know how to stop the Ola scripts from deleting the monthly or beginning of the year backups.

0

If this is your required backup retention policy, then could you not have an extra backup job that runs once a month, and another running at once a year. These two new jobs would point to their own directory and have their own retention policy.

The downside is once a year you'll do 3 at once, daily, monthly and yearly one. But this is a rare event.

I would question if all this backups are needed, would the data be stale and no good by then? Could you lose a year's worth of data if you needed to restore it. If you want it for looking back at the data, is a DWH more suited?

However I don't no the reasons why this is required, there is probably a good reason for your approach. Such as cost saving.

  • So I thought of the same things, but I would like to keep using Ola's scripts if possible. I guess I could just create a new directory for the monthly and yearly backups. I just want to make sure that the cleanuptime for the full backups wont delete the backups in another folder. – J1k0 Dec 1 '18 at 19:53
  • I guess it would be easy to test by setting a cleanup time to 1 day in q job and something greater in another job and see what happens. It could potentially delete both I think if pulling them from the backup set table. – BeginnerDBADan Dec 1 '18 at 20:52
  • 2
    Why not just set up a separate job (or an intermediate step) that copies the backups you want to a different location. Then Ola's cleanup won't have any idea about the copies. – Aaron Bertrand Dec 1 '18 at 21:13
  • I wondered about this but wasn't sure on technologies available, we use second copy or robocopy for tasks like these. – BeginnerDBADan Dec 1 '18 at 21:18
  • I think we are going to follow Aaron's plan. Just add another step to the Ola scripts and copy using powershell monthly and yearly. – J1k0 Dec 3 '18 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.