MIS Systems Thread, SIMS SQL Backup Strategy in Technical; I am setting up a backup strategy for our SIMS SQL2008 server. Am currently using the SIMS scripts to do ...
30th January 2012, 01:00 PM #1
SIMS SQL Backup Strategy
I am setting up a backup strategy for our SIMS SQL2008 server. Am currently using the SIMS scripts to do it which is fine, but I want less data loss in the event of a failure so am putting in transaction log backups every hour (maybe 2).
My question is this, which dbs do I need to backup to ensure a complete restoration is possible without loss of login usernames/passwords (this happened to us once when the server went belly up and was a nightmare).
Currently setting maintenance tasks to backup the following:
(discover when I actually deploy it)
Do I need to also back up all/any of the following?
30th January 2012, 01:13 PM #2
Do you use FMS?
On SIMS, get it to run sims.db_p_transfer_login - that will bring across the sims sql logins + passwords to the SIMS db.
Thanks to matt40k from:
skunk (30th January 2012)
30th January 2012, 01:15 PM #3
To safeguard the SQL server, backup the master and msdb databases, the model is used as a template when creating new databases (can normally be ignored unless you know it's been customised), forget the tempdb.
I use log-shipping, so the MSDB is vital to this.
You only need to backup the SIMS databases to restore the SIMS databases. The SIMS usernames and passwords are encypted and stored within the SIMS database. As Matt mentions, there are routines to store and transfer SIMS logins. If you restore SIMS to the same SQL server, the usernames and passwords will be preserved as they are populated to the master database by SIMS. If you need to restore to another SQL server, you'll need to use some SQL commands to create the SIMS users. Documented many time in Edugeek.
Don't make the mistake of restoring the master database of one SQL server to a different SQL server- things will go very wrong.
Last edited by jinnantonnixx; 30th January 2012 at 01:19 PM.
2 Thanks to jinnantonnixx:
skunk (30th January 2012), vikpaw (30th January 2012)
30th January 2012, 01:36 PM #4
No FMS here.
@jinnantonnixx, what is the advantage of log shipping over standard transaction log backup/restore? Not that I have a spare SQL server, but always useful to know.
30th January 2012, 01:49 PM #5
It allows you to keep a 'warm' standby server ready to spring into action like a coiled tiger. Something like that anyway.
In my case, every hour the logs are copied to a shipping file server, then copied to the standby server.
If the main server goes west, the log shipping jobs are run on the main server if at all possible, but in any case the shipped logs are restored to the standby server which is then switched into action.
In a real failure we actually experienced, we've switched 100 SIMS databases to the new server in about 10 minutes. It works well, but requires a lot of planning. I wrote the scripts to generate the log shipping jobs (the wizards are only of use for single databases) and this was tricky.
Thanks to jinnantonnixx from:
skunk (2nd February 2012)
By matt40k in forum MIS Systems
Last Post: 11th February 2011, 02:12 PM
By Rawns in forum MIS Systems
Last Post: 6th January 2011, 11:17 AM
By Max_Power in forum MIS Systems
Last Post: 5th December 2010, 10:02 AM
By cookie_monster in forum MIS Systems
Last Post: 29th July 2010, 03:24 PM
By projector1 in forum MIS Systems
Last Post: 10th December 2007, 11:30 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread