[sims] Sims backup regimes

[creese]

Im thinking about SIMS here but it may be relavant to other MIS's.

 

We are looking at our backup regime very closely. Also thinking ahead to central hosting next year.

 

How long to keep the backups? Restoring anything over a couple of days IMHO is not desirable do to the amount of data input during time after the backup was taken.

 

We keep about approx 10 days worth (not weekends) on the secondary disk of the ancient servers. 8 backups on a central backup server.

 

What about the summer holiday? My arguement is that as the database is still being backed up each week day backups older than the 6 week holiday would not be necessary.

 

There is talk of saving 6 months worth of backups, I see no point in restoring this far back.

 

Arguements for and against and suggestions most welcome. I see this as a big problem and both separate and different to the usual system backups.

[tmcd35]

We do a full SIMS db backup daily and keep five days worth on the backup array. Or sims install is on a VM which we back up off-site every term.

[creese]

Well, there are a few things to consider.

 

Apart from disaster recovery, the is an archival aspect: There was a recent case where pupil conduct 'disappeared'. You know what I mean. Having records before and after this incident would have been helpful to reinstate the conduct record.

 

 

That's true, but say a record got messed up for whatever reason. You could restore to another database and manually correct the data on the live database referring to the restored database.

 

Long breaks are a special case. An error in data might not be discovered until well into the new term.

 

I agree to a certain extent, the question though is how far back do you go? Sod's Law says it's always a day before your earliest backup.

 

With the 'Missing' conduct, that is not exactly a data loss problem, also how many hours and how many retores would you have to do to get to the 'lost' data?

[creese]

One approach is to determine how much backup storage you have and size your backups to fit.

.

 

I think it's the other way around as this will be new equipment when it goews central.

 

Another point with SIMS is no one as any idea of the rate that these backups will expand. We have small school with huge Docstorages and large schools with huge SQLs.

[vikpaw]

I go with keep as much as i can, and prune / archive only as necessary.

 

My 3GB mdf, compresses down to 800MB-1GB usually. I take a full backup like that everyday, including weekends as people can be working at any time.

 

At the end of last year i pruned down my collection to just 1 per month for the previous year which is fairly reasonable i think.

 

My opinion is that we can never restore to a backup unless it's the direst of circumstances and absolutely necessary. Even 1 day of work is immense when you use FMS, admissions, lesson monitor, lesson planner, assessment, etc.

 

As jinnantonnix said, the only reason for keeping them is that i would restore to a test box, extract the data i want and then reinject it into the live server. So the purpose of the backups is to recover an old template, marksheet, or other such deletable that someone may lose or corrupt.

 

Things to bear in mind are that i believe in the UK you need to keep 5 years worth of attendance data possibly more. It could even be for 5 years after the student leaves, but i may be confusing the different guidelines / laws between data processsing and attendance.

 

I'm not sure if you can take an export of the attendance data and that is satisfactory as it's supposed to include marks, date, marker, any changes etc. There is a link to the legislation somewhere, i'm sure you don't need the whole db just for that data.

 

Another factor is how usable is your backed up data? You cant restore Spring data onto your Summer set up, so you need to be able to recreate a Spring setup. I keep VMs for this, though haven't needed to use them, and it's a bit of a waste of space really.

 

Also post migration to SQL 2008, it's probably not possible to restore those old datasets, so it really is worth thinking, what you would restore a backup for and is it practical to do so.

 

Incidents like that where conduct was changed, unfortunately can't really be resolved even with lots of backups as there is no real auditing of the system.

 

A reasonable system i think would be to keep a monthly backup, along with 1 a week for the past month, and 1 a day for the past week, usual grandfather strategy basically. Keep that back to the last SIMS upgrade. Keep if possible a backup of the old setup either as a VM or a snapshot and 1 backup to go with that. If space is limited go with differentials but i prefer full where possible.

[matt40k]

Remember it'll stay in the SIMS database, the old attendance stuff. For secondaries it's DOB of pupil + 25ys then shred it. (http://www.rms-gb.org.uk/download/789)

 

Restoring SQL2005 onto 2008 is possible, so is upgrade an old database to the latest version, the question is, what introduced the error, the upgrade or a user change.

 

I think 2 weeks worth of backups + plus major points in time (end of year, sims release etc) is good enough. Still, just my opinion. More is always better then none.

[vikpaw]

Remember it'll stay in the SIMS database, the old attendance stuff. For secondaries it's DOB of pupil + 25ys then shred it. (http://www.rms-gb.org.uk/download/789)

 

Restoring SQL2005 onto 2008 is possible, so is upgrade an old database to the latest version, the question is, what introduced the error, the upgrade or a user change.

 

I think 2 weeks worth of backups + plus major points in time (end of year, sims release etc) is good enough. Still, just my opinion. More is always better then none.

 

Thanks, good points. Never looked into converting old data to new, and heard of issues between the SQL versions, so opting for safety.

Good info in that link, interesting that you need to 'shred' the backups. So if you go with the latest backup includes the whole history then you can't shred, you just have ever increasing backups.

 

EDIT: well you can delete old data from the current system as long as it lets you.

Edited September 13, 2010 by vikpaw
typo + edit