[sims] SIMS: Backup and Recovery

[Gongalong]

Hi folks,

 

I was curious about what others do for backup and recovery of SIMS.

 

We use DPM 2010, and I have no experience of SQL Server restores. At the moment DPM is set to backup SQL instances daily at 2am.

 

Within the SQL instances there are several databases: model, master, Discover, msdb and SIMS. Is restoring the database as simple as just recovering the SIMS database from the list?

 

Do others backup more frequently? (I'm guessing that DPM takes a shadow copy of the database, with respect to backing up a working database)

 

Any information on this gratefully received.

 

TIA

[Gongalong]

Just as a rider to this, our SIMS Administrator believes that previously she was able to restore SIMS to an hourly increment.

 

We have reinstalled the SIMS server subsequent to my arrival at the school, and the backup strategy has changed.

 

Would it have been possible to restore to an hourly increment by any other means than just backup?

[Mcshammer_dj]

I use a Dbattach script to create a backup for the SIMS database each evening and use @matt40k 's tidy backup utility to zip and shrink these down. By using the Dbattach routine the passwords are passed to the Sims database as part of the process. I have not had any problems with recovering data either live at school request or into the test datacentre as a part of our DR testing.

[DarrenM]

Ours is virtual so we use Veeam to replicate it to our disaster recovery server. When a user thought they had lost data a few months back I was able to quickly run up an old version of the server to look into the problem.

[zag]

Yeh same here, we virtualize it and backup the whole server.

 

Also use backupexec to do the SQL each night.

[Rawns]

I use a Dbattach script to create a backup for the SIMS database each evening and use @matt40k 's tidy backup utility to zip and shrink these down. By using the Dbattach routine the passwords are passed to the Sims database as part of the process. I have not had any problems with recovering data either live at school request or into the test datacentre as a part of our DR testing.

 

Same setup right here!

[vikpaw]

Daily full backup is the norm. I can't imagine you were doing full backups each hour, so the only other option would be to backup transaction logs each hour, but i've no experience of restoring them. I think the theory is you restore the most recent full backup then 'replay' the transactions. @jinnantonnixx would know

 

I think it's a bit overkill, though i wont think that when i actually need it. For the most part, people want to look at data from the day before. If something is lost, it's not usually something that can't be re-keyed.

 

There are little to no audit logs in SIMS, so narrowing down to when something was changed is a mammoth task, unless the user knows exactly, so replaying lots of transactions doesn't sound like fun, plus anyone that has entered data in that hour would lose work too.

 

I prefer to restore a backup to a test server and look at or copy data out from there.