Sims Backup - Advice

[pooley]

I arrived at work today to find Sims being a bit flakey, would bomb out at random points with a "Severe error - please ignoe bla bla bla" type of error, nothing on the capita support site.

Phoned our Sims support (LEA) and they took a copy of our database and attached to one of their test machines. End result = database screwed

I'm told by admin staff that main sims machine (XP acting as a server) was playing up on Friday, I wasn't in so they just put up with it.

So today I ask the secretary for the backup drive (usb drive) and she says she didnt backup on Friday due to machine not being stable, so I have to recover from a week old database.

recovery worked well, a pain in the bum to enter a weeks attendance and dinner money records along with Dinner payments (we are piloting Dinner Money 7) so today was a major headache.

So at the risk of bolting the door after the horse has left the stable I am going to imliment a new backup regime. Here's where your advice comes in.

Currently we just backup the Sims SQL database to a usb, along with docstorage and a few other bits and pieces.

I wrote a batch file that stops the sql service and xcopies the files to the usb, Overwriting what was on there. Now if the Secretary had backed up on Friday, I have a sneaky suspicion that the database was probably not 100% then and it would of overwritten my Good but OLD database and I would of gone home Sick

So a new solution is required.

I'm thinking NTBackup to a NAS device every night, Full/incremental and a full offsite backup to be taken on a Monday/Friday.

Anyone have any opinions or like to share their solutions?

It was a long hard slog today, lessons learned and could of been worse.

S

[kmount]

Moving from a single usb hard disk to a nas seems like a big but necessary jump.

How big is the back up in itself? - I'd be suggesting you take daily's at least and then yes, your weekly's off site. Check whether your LA offers some form of remote secure storage as if your database isn't too big you could upload it there for safe keeping as part of your shedule.

Ours?

We run a job at 5am each morning with Backup Exec which takes the SQL snapshot to a tape which is replaced on a Friday (it holds the weeks worth of diffs) so we have each days snapshot should corruption occur and it's noted within a few days the same tape is in play, otherwise we can go back.

[vikpaw]

I used to do a full backup every night to tape.

Now we have daily diffs and a weekly full, also storing some backups locally. It's probably a bit of overkill but it's only a small overhead to make frequent backups and definitely worth it in case of disaster.

My issue is when even for the tiniest of problems, the recommended solution is to restore to backup. Like you've found, there's a hell of a lot of work that goes on in SIMS and to lose that is not fun.

You're lucky you could put that data back in. We manage, students leaving, joining, staff being added for next year, grades going in and reports being uploaded to student records, cover management as well as timetable changes for this year and next, fees, finance inc. payroll, plus attendance. .. . . on a daily basis.

So, when we were told to roll back to the backups from two days ago , it was not an option. I'd be lynched if we had to do that!!

[bossman]

Sims Backed up via VSC full backup every night to OpenFiler Box using backup script.
Hope we never have the need for it but it is there all the same.

[pooley]

Thanks guys, was thinking NAS maybe overkill but better to be safe than sorry.

Just need to source some backup software that deals with backing up SQL

[AyatollahPies]

Thanks guys, was thinking NAS maybe overkill but better to be safe than sorry.

Just need to source some backup software that deals with backing up SQL

You could just create a batch file to run a dbattach backup, before your main backup.

[pooley]

You could just create a batch file to run a dbattach backup, before your main backup.

I could, but that would involve me asking the LA for the SA password, prob not going to give it to me

[matt40k]

USB? Please tell me your backup plan was to a USB pen?!? Nooo... the amount of times I've had student loose data, SIMS is extremely important. Back it up to tape or DVD. Don't both with HDD, to easy to damage if you drop it. Plus, what happens if you get a virus? Anyway, my opinion.

I would ask for the SA password, failing that I can send you a command to reset the SQL password (assuming you have the administrator password and can physical connect to the server).

Backup to dvd make sure it's encrypt and take that offsite, NovaStor does it I know, I believe v.10 does SQL live as well.

Failling that I would look at Dec '08 release (or March '09 now) and upgrade DMS, it comes with a backup feature for DMS. Shove it to another server, then run dbattach dump it to another machine. Still I'll rather do that as well as external media.

[Ric_]

I do two scheduled backups using the native backup in SQL each day. These are then backed up to tape each night by a remote server.

This means a major cock up can recover the database from a maximum of a couple of hours ago (off the local hard disk) or the database and files (document server and more) from the previous night following hardware failure.

IMHO this seems like a reasonable comprimise and it means that the SIMS database doesn't need to be taken offline to backup the data.

This regime is due to be changed to a VM snapshot method once I get my new 7110s online.

[vikpaw]

Just need to source some backup software that deals with backing up SQL

SQL management interface, but you'll need the passwords.
Or BackupExec has an SQL agent not sure if that needs a password, i don't think so.

[Danf]

You could just create a batch file to run a dbattach backup, before your main backup.

That's exactly what we've been doing for a while now. Unfortunately a recent patch has messed things up and I need to restore a backed up version.

Attempting to restore through dbattach /restoreui fails 'exclusive access could not be obtained because the database is in use'. Nobody is logged in, I've restarted the sql service, even restarted the server with no luck.

Any ideas on what to try next would be gratefully received

[matt40k]

That's exactly what we've been doing for a while now. Unfortunately a recent patch has messed things up and I need to restore a backed up version.

Attempting to restore through dbattach /restoreui fails 'exclusive access could not be obtained because the database is in use'. Nobody is logged in, I've restarted the sql service, even restarted the server with no luck.

Any ideas on what to try next would be gratefully received

I've had this before...

If I remember right, I also had issue extracting the zip, so I used WinRar, once it's in a BAK format, then you can restore, if that fails, like you, then I think the first thing to try is to deattach the db, the reattach the mdf. Failing that, get Microsoft SQL Server Management Studio Express (SSMSE)- http://www.microsoft.com/downloads/d...displaylang=en (assuming it Express)

Then restore using that, if that still failes, delete the database. The restore from the bak. At this point, you should have about 5 backups of the mdf (just for sheer fear you have now of loosing everything). Warning, SSMSE does delete the mdf and ldf files, unlike dbAttach.

[DMcCoy]

I do two scheduled backups using the native backup in SQL each day. These are then backed up to tape each night by a remote server.

This means a major cock up can recover the database from a maximum of a couple of hours ago (off the local hard disk) or the database and files (document server and more) from the previous night following hardware failure.

IMHO this seems like a reasonable comprimise and it means that the SIMS database doesn't need to be taken offline to backup the data.

This regime is due to be changed to a VM snapshot method once I get my new 7110s online.

Full VM snapshots are not the best method for SQL/Exchange etc. You get a consistent state with the online SQL/Exchange backup, and it will also truncate the temp DB and log files. Snapshots could work, as long as they are supplemented with proper backups. If you are exporting disk images you may be ok if it can prepare the DB with a shadow copy agent.

[Danf]

I've had this before...

If I remember right, I also had issue extracting the zip, so I used WinRar, once it's in a BAK format, then you can restore, if that fails, like you, then I think the first thing to try is to deattach the db, the reattach the mdf. Failing that, get Microsoft SQL Server Management Studio Express (SSMSE)- Download details: Microsoft SQL Server Management Studio Express (assuming it Express)

Then restore using that, if that still failes, delete the database. The restore from the bak. At this point, you should have about 5 backups of the mdf (just for sheer fear you have now of loosing everything). Warning, SSMSE does delete the mdf and ldf files, unlike dbAttach.

Thanks, your second suggestion inspired me to stop the sql service, ensure i had multiple additional copies of the mdf/ldf files and rename the originals to keep them out of the way when i restarted the sql service. Don't know why i didn't think of that in the first place.

so, on with the restore....

restore detected an error in database 'sims' as read from the backup set.


oh well.. lots of older backups to try...

[matt40k]

Thanks, your second suggestion inspired me to stop the sql service, ensure i had multiple additional copies of the mdf/ldf files and rename the originals to keep them out of the way when i restarted the sql service. Don't know why i didn't think of that in the first place.

so, on with the restore....

restore detected an error in database 'sims' as read from the backup set.


oh well.. lots of older backups to try...

What was the error? If you go onto supportnet and search for dbdiag there is a checkdb script, any errors on that = hardware issues.

If it's an error with the ldf (not matching mdf or such), that's pretty easy to resolve.