DB backup retention strategy?

[PiqueABoo]

It's not just matt40k's tidy util coz I've seen another freebie util do the same, but why would you want to retain the DB backups created in the last N days as opposed to the last N backups, or perhaps a combination of both?

[vikpaw]

Not really sure what you're asking here. Is it really, 'Why do you want backups?' ?

 

Isn't it simply so if something is deleted or goes wrong, it's possible to go back and retrieve lost data. Also to keep a history , as for certain processes, its encumbent on us to make sure we save the history.

 

I actually keep as many backups as far back as i can. We take SIMS backups daily.

 

The utility just helps keep things tidy, so you could implement a grandfather strategy, and only keep 1 a month, followed by 1 a week for the present month, and 1 every day for the present week.

 

The only problem with SIMS is that it's not possible to do selective restores, so it's all or nothing. My workaround is to restore to a virtual machine and pull out the missing info, and then feedback to the live system.

 

Does that answer the question?

[matt40k]

You should really be keeping a copy of the database before each major change, such as a SIMS release, timetable change. With the cost of SAS discs vs the costs of DVD-RW, I would be burning the backup to disc (with encryption). This way if you find out 2 months down the line you've lost some data or such, you have a way of recovering (ie Capita exports it, then imports it into your current database).

 

Still I've never heard of it happening over 30 days. (Exception being a school merge).

 

You keep backups for n days because it's a backup, it covers you for user error, otherwise RAID would be good enough. For example, user deletes students record, you are able to recover, as well as corruption or failed upgrades.

 

PS: TidyBackups rulez!! Tidy Backups

[PiqueABoo]

Is it really, 'Why do you want backups?' ?

 

Nope.

 

This is a DB backup to a file i.e. one way of avoiding shutting the DB down during a full system backup and/or paying for a Backup Exec SQL options etc. All the credible utils I've found to do that will stuff a timestamp in the file name and optionally delete older ones to help preserve disk space, but they want to delete old files in terms of how old e.g. more than 5 days old. But if your backup to file fails and you don't notice for a week that wipes out all your old backup files.

 

Thus I'm talking about the ability to tell one of these utils to perhaps delete files older than 5 days, however it must always keep say the last 5 backups regardless of how old they are.

[vikpaw]

that would be good, i'm sure matt40k could amend the functionality.

or better still, you need a util that warns you the next day if a backup has failed.

[DMcCoy]

It's not just matt40k's tidy util coz I've seen another freebie util do the same, but why would you want to retain the DB backups created in the last N days as opposed to the last N backups, or perhaps a combination of both?

 

So it ends up in a set rotation backup cycle, say once a week, month etc.

 

I have a backup of sims each month for the last 5 years or so, along with weekly and daily revisions for the last month.

[matt40k]

Ok, so another option to set the min amount of backups kept. Sure I can do that

[Rawns]

PS: TidyBackups rulez!! Tidy Backups

 

Missed this! I'm still using the version 1.1.0.0!

[matt40k]

I think I've done it.

 

If you use:

tidybackups.exe /path: /preserve

it should delete all the backups other then the last 6 (per database)

 

For example if you have 8 sims backups and 7 fms backups, you'll end up with 6 of each. You can ofcourse add number to the end (/preserve:4)

 

It also works with the /days: option, so you could remove all the old backups over say, a year, but you'll always keep at least 6 backups (for each database) regard less of age. And you'll only remove them if they are "old".

 

Hope that makes sense. I've not fully tested it, so only try it on a test machine (or at least have a backup of it first!!).

 

Feedback greatfully received. Run it with /debug /log and it'll tell you want it wants to do without actually doing anything and log it to a file. (i'll need a copy if anything goes wrong).

 

Download tidybackups from SourceForge.net

[matt40k]

Bug found.

 

Fixed in:

Download tidybackups from SourceForge.net