[sims] SIMS SQL Backup Strategy

[skunk]

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:

 

sims

solus3_deployment_server

(discover when I actually deploy it)

 

Do I need to also back up all/any of the following?

 

master

model

msdb

tempdb

[matt40k]

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.

[jinnantonnixx]

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.

Edited January 30, 2012 by jinnantonnixx

[skunk]

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.

[jinnantonnixx]

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.