Open Menu

© 2018 Capita Business Services Ltd. All rights reserved.

Capita Education Software Solutions is a trading name of Capita Business Services Ltd. Our Registered office is 30 Berners Street, London, W1T 3LR and our registered number is 02299747. Further information about Capita plc can be found in our legal statement.

Product / SIMS 7 / SIMS 7 - Backup Services / SIMS 7 - Backup Guidance

SIMS 7 - Backup Guidance

Introduction

A number of partners provide backup services to schools, this document seeks to list the set of items that need to be backed up and restored.  Please note that we would expect customers to prove that their disaster recovery plan works. 

Backup Priorities

Software

We have an agnostic view as to whether software including SIMS, MS SQL Server need to be backed up.  There will always be copies of these installations available and typically it is wiser to re-install software rather than recovering it from backup.  That said, the ability to restore complete machines is useful and may well be quicker than rebuilding a machine, assuming that the hardware is unchanged (or virtual).

SIMS Server Locations

The SIMS File Server ‘S:\SIMS’ is relatively static data and mainly changed by SIMS Upgrades.** NB: If Options / NOVA / IEP Writer store data on the SIMS File server then it becomes essential to back up the SIMS File Server.  An easy restore of this folder would make the recovery of a SIMS system much faster.

Data

SQL Server

  • The FMS Database
  • The SIMS Database
  • The Master Database
  • MSDB

All of the above should be backed up on a regular basis.  How regular is simply a case of what the customer is prepared to lose / re-key.  At most this should be a day’s work.

Backup assumes that a SQL .bak file is created which provides all that is required in a single file.  It is theoretically possible to copy the mdf/ldf pairs but this process requires that the SQL Server takes the database offline whilst the copy is made.

There are alternative techniques such as volume shadow services which can be used to achieve the same.  ESS requires that the data is backed up but backup vendors must decide how to implement theE process.

 

 

Please note that whilst it is possible to restore SIMS / FMS without restoring Master, Master contains all of the SIMS users and passwords.  The users can be regenerated and the passwords reset, but the passwords cannot be recovered.  Hence it is usually preferable to restore master / msdb along with the SIMS and FMS databases.

Failure to restore msdb will affect SQL Server services. SQL  uses this to schedule SIMS change control logging.  

SIMS and FMS are loosely coupled but there are potential issues with restoring one without the other.  For example if a new member of staff is created in SIMS and exported to FMS and a backup of SIMS is restored without that member of staff, then data fixes may be required which may be chargeable to the customer.

There is also an additional database:

  • SIMS Discover Database

Whilst this can be backed up and restored AS A PAIR with SIMS, it can be regenerated if the Discover Backup utility is used.  Discover is a mainly a copy of SIMS data refactored in to a data warehouse.  There is a small amount of real data that cannot be regenerated from SIMS and this is what the Discover Backup Utility extracts.

The SIMS Document Server

On the SIMS Document Server, it is essential that the folder:

  • <Doc Server Root>\<SQL Server>\<SQL Instance>\<Database Name>\
  • <Doc Server Root>\<SQL Server>\<Database Name>\              (If a default instance is used)

And all sub folders (2 character names) are backed up.

All of the files are encrypted and have mangled names.  As such they cannot be used outside of SIMS (or a partner application that uses SIMS to retrieve them).

dbAttach will create a copy of all of the files as part of a backup.  This may or may not be useful to partners.

FMS

In addition to the .mdf and .ldf if a school is using the Attached Documents functionality within FMS there will also be a directory called FMSDocumentImages_<dbname>  in the SQL Server data directory. If the .mdf and .ldf have been manually detached, this folder will need to be copied at the same time. If a .bak has been generated using the standard SQL backup process (either from SQL Management Studio, or DbAttach, or another tool) the data contained in this folder is automatically included in the backup, and no additional action is required.

Multi-Company

Schools are expected to have more than one FMS database.  In academies, this may well be a current FMS database and a pre-academy database that finance laws require to be kept for 6 years.  Additionally, Schools may have one database for public monies and another for private monies.  Rules changed with the advent of academies, but for the purposes of backup consideration must simply be given to having more than one FMS database to backup.

Discover

The discover backup utility is documented elsewhere but the use of the utility will significantly reduce the volume of data to backup.

On a SIMS installation the latest instructions will be found in <SIMS .Net>\Discover_Config.chm

SIMS Server Locations

If Options / NOVA / IEP Writer store data on the SIMS File server then it becomes essential to back up the SIMS File Server.  An easy restore of this folder would make the recovery of a SIMS system much faster.

The backup of ALL subfolder except the Setup and Documents folder would be recommended.

dbAttach

Please note that dbAttach can be used to resolve a number of issues.  When dbAttach detaches the database, it retains the SQL Server passwords within the database.  When dbAttach attaches the database it restores the passwords and matches up accounts to SIMS users.  The SIMS/FMS Backup  do not contain the SQL Password (encryptions thereof).  False positive results can be achieved when a backup is restored, the SIMS/FMS database is detached / attached using dbAttach.  Old copies of SQL passwords can be re-instated using this means which is likely to be undesirable.

Please also note that using SQL tools to detach a SIMS database from one server and attaching it to another server will not work.  The first work around is to use dbAttach to repeat the detach/attach process which in most cases would at least enable access to SIMS as the Sysman user.

 

 

Parameters

dbAttach: Database Attachment, Restore & Backup Management Utility

Parameters :

/SERVER="ABCD"      - ABCD is the name of the SQL Server to upgrade.

 

/DATABASE="ABCD"    - ABCD is the name of the SIMS database on the server to upgrade.

                      (Note that if not supplied the settings from the Connect.INI in the

                      current directory will be used.)

 

/USER="ABCD"        - ABCD is the user name: SQL User for installations, Attach, Detach and Restore

                      SIMS User name when used to backup with /BACKUP

 

/PASSWORD="ABCD"    - ABCD is the password for the user given.

 

/TRUSTED            - Used instead of /USER and /PASSWORD.

                      Uses the current Windows Logon.

                      User must have been setup in System Manager.

                      For DBAttach version 1.8 /TRUSTED can only be used for /BACKUP without /PATH.

 

/AUTO               - If supplied, will attempt to perform specified operations automatically.

                      If automation fails, the UI will be displayed.

 

/NOBACKUP           - If supplied no backup will be taken of the database, and if a problem occurs,

                      no restore will be performed.

                      For use with /AUTOATTACHMODE and /AUTODETACHMODE

 

/BACKUP             - Create a SIMS backup - also requires /USER, /PASSWORD, /DATABASE, /SERVER

                      /AUTO, /MASTER(optional) and /DMS(optional) to be specified.

                      This parameter is not necessary when using /AUTOATTACHMODE or /AUTODETACHMODE

 

/AUTOATTACHMODE     - Automatically Attach specified database - requires /USER, /PASSWORD,

                      /DATABASE, /SERVER, /AUTO and /SOURCEDB

                      This parameter can only be used with the sa login.

 

/AUTODETACHMODE     - Automatically Detach specified database - requires /USER, /PASSWORD,

                      /DATABASE, /SERVER, /AUTO and DROPSYSLOGINS(optional)

                      This parameter can only be used with the sa login.

 

/PATH               - Specifies a path for Basic Backup & Basic Restore.

                      (Initiates Basic Backup Mode, requires sa login.)

                      When used with /RESTORE or /BACKUP, this path MUST include the filename

                      and the .bak extension.

 

/SOURCEDB           - Specifies a path for use with /AUTOATTACHMODE.

                      This path MUST include the filename and the .mdf extension.

 

/RESTORE            - Specifies Basic Restore Mode - Requires /PATH, /USER, /PASSWORD, /DATABASE,

                      /SERVER and /AUTO. Also /OVERWRITE as an option.

                      The /OVERWRITE parameter is required if the database exists.

                      This parameter can only be used with the sa login.

 

/OVERWRITE          - Specifies that if a database with the same name exists when attempting

                      to Restore, overwrite that database.

 

/MASTER             - If specified will also take a back up of the Master database.

                      User must be a SQL Server System Administrator to use this option.

 

/DROPSYSLOGINS      - If specified will remove SIMS users from the SQL Server.

 

/DMS                - If specified will also back up Document Management Server for the database.

 

/SIMPLERECOVERY     - When attaching a SIMS database to the server set the RECOVERY_MODEL

                      to SIMPLE.

                      Default setting was and remains as FULL recovery mode.

NB:        We note that dbAttach does not back up MSDB and have added this to the backlog of work to be done.  Whilst no unrecoverable data is stored by SIMS in MSDB, failure to restore  it will affect Partner Change Logging.

Data Security

  • Backups of the above contain sensitive personal information.
  • Backups may contain financially confidential information.
  • Backup files from SQL are insecure and must be secured.
  • Data Protection Act 1992 applies to backups within the UK.
  • GDPR effective from 25th May 2018 applies to backups within the EU.
  • If services are provided to SIMS customers outside of UK jurisdiction then the responsibility of compliance with the appropriate laws lies with the  SIMS Partner.

Our joint customers should be asking the following questions:

  1. Where is the backup stored?
    1. Is it stored within the EU? If not does it comply with the law?
    2. Does the location of the backups comply with Local Authority requirements (if applicable)
  2. Who has access to the backups
    1. What jurisdiction are they under?  The servers may be in the UK but the agents managing the system may be elsewhere. 
    2. Does this comply with GDPR / DPA and LA regulations?
    3. Are they DBS (Disclosure and Barring Service) checked?
  3. How can the school prove that they have a workable disaster recovery plan?  Any plan that is not tested should be assumed to fail.  It has been known for example that a school server is changed but the old one remains in place and is backed up whilst the current active server is used but not backed up.   
  4. Does the school need to make any changes to its Data Protection Registration to use the Product?

GDPR / DPA

ESS partners will find links to useful GDPR / DPA resources here.