+ Post New Thread
Results 1 to 5 of 5
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 ...
  1. #1
    skunk's Avatar
    Join Date
    Mar 2006
    Location
    North West
    Posts
    311
    Thank Post
    88
    Thanked 40 Times in 33 Posts
    Rep Power
    30

    Question 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:

    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

  2. #2

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,408
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    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.

  3. Thanks to matt40k from:

    skunk (30th January 2012)

  4. #3

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    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 12:19 PM.

  5. 2 Thanks to jinnantonnixx:

    skunk (30th January 2012), vikpaw (30th January 2012)

  6. #4
    skunk's Avatar
    Join Date
    Mar 2006
    Location
    North West
    Posts
    311
    Thank Post
    88
    Thanked 40 Times in 33 Posts
    Rep Power
    30
    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.

  7. #5

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    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.

  8. Thanks to jinnantonnixx from:

    skunk (2nd February 2012)

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SIMS Tidy Backups - SQL 2008 - Autumn release
    By matt40k in forum MIS Systems
    Replies: 49
    Last Post: 11th February 2011, 01:12 PM
  2. [SIMS] SQL Backup with dbAttach [Solved]
    By Rawns in forum MIS Systems
    Replies: 5
    Last Post: 6th January 2011, 10:17 AM
  3. [SIMS] SIMS SQL 2008 - How much memory do you have allocated?
    By Max_Power in forum MIS Systems
    Replies: 15
    Last Post: 5th December 2010, 09:02 AM
  4. [SIMS] SIMS SQL 2005 --> 2008 migration
    By cookie_monster in forum MIS Systems
    Replies: 7
    Last Post: 29th July 2010, 02:24 PM
  5. Replies: 2
    Last Post: 10th December 2007, 10:30 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •