[sims] Backing up SIMS

[LeMarchand]

One of my schools recently came out of a third party SIMS backup agreement and I assumed that the previously set up backup regime using Symantec was still running -t

his stopped the SQL before the backup ran and restarted it after. Unfortunately it seems these commands have been removed. It's easy enough to replace them (though I'm guessing they need revising to stop SQL2012 rather than 2008) but reading the documentation I'm now concerned that maybe it wasn't being done properly in the first place. So a few questions:

 

 

• Is the above a valid method of backing up SIMS?
  
• If so, what should be ticked in the selection area?
  
• Should I be scripting/performing a regular dbattach type backup?
  
• If so, how frequently? (I could only find bak files spaced months apart)
  
• Those files are HUGE. Is it safe to use SIMS to tidy up older ones? How much older do people go?
  
• In my VEEAM schools is a "normal" VEEAM backup (or replication) OK, or should I be doing more?
  

[Banjo]

If there is a SIMS plugin for Symantec then fine. If the script is just stopping the SQL instance - copying the mdf/ldf to another location and then restarting the service you might be in trouble. We use Attix 5 with these plugins for SIMS/Discover/FMS without these you will have difficulty restoring the database(s) as Capita use the dbattach.exe tool.

 

Test the solution you have now, but it doesn't sound promising. .bak files are only made when the db is upgraded/patched or when a user manuualy runs the backup. We don't keep more than 4 or 6 months worth.

Edited May 10, 2017 by Banjo

[LeMarchand]

If there is a SIMS plugin for Symantec then fine. If the script is just stopping the SQL instance - copying the mdf/ldf to another location and then restarting the service you might be in trouble. We use Attix 5 with these plugins for SIMS/Discover/FMS without these you will have difficulty restoring the database(s) as Capita use the dbattach.exe tool.

 

Test the solution you have now, but it doesn't sound promising. .bak files are only made when the db is upgraded/patched or when a user manuualy runs the backup. We don't keep more than 4 or 6 months worth.

 

Thanks. Pretty sure that's all that used to happen. Doesn't sound like it ever worked!

 

Makes sense about the .bak files now you explain it.

[Esteban_Child_of_the_Sun]

We set up a simple backup for our schools

1. Weekly re-integrate the SIMS / FMS usernames and passwords in to their respective databases, and make a backup of the Docstorage folder to a backup drive (usually robocopy and that way it only updates changed files)

2 Twice daily:- backup the SIMS database(s) using DB attach calling the backup file Mon AM, Mon PM, etc. and save it to the backup drive

3. Daily:- backup FMS using FMS's DBAttach (yes they are different) and the Discover backup utility to backup discover (makes the backups a couple of MB instead of a few hundred MB) and backup all changed files in Docstorage in the last 24 hours to the backup drive (in a folder for the day backed up). Also make a backup of the shared SIMS folder.

 

All these backups overwrite the previous week's backups for the same period so we only ever store a weeks worth of backups on the backup drive, then we set our backup software to backup the drive while it's inactive for more than 10 minutes (i.e. after any backup)

[LeMarchand]

We set up a simple backup for our schools

 

Thanks. I our definitions of "simple" may differ somewhat! Does sound sensible, though.

[caffrey]

We just have a backup script that SIMS supplied us when they installed it, I've since changed it a lot

 

I have three scheduled tasks all running these batch files

 

1.

 

Set dd=%DATE:~0,2%
Set mm=%DATE:~3,2%
Set yyyy=%DATE:~6,4%

set var=%dd%-%mm%-%yyyy%

md C:\BACKUP\%var%

"C:\yourlocationofthis\DbAttach.exe" /BACKUP /AUTO /SERVER=yoursimserver\SIMSdatabase /DATABASE=yourdatabase /USER=youruser /PASSWORD=yourpassword /PATH=C:\BACKUP\%VAR%\SIMSBACKUP.BAK"

md x:\%var%

rar a -hpwhatpasswordyoulike -m5 x:\%VAR%\%VAR%_SIMSBACKUP.rar c:\BACKUP\%VAR%\SIMSBACKUP.BAK

 

batch2 creates and compresses a copy, which goes into an upload folder which in turn is monitored by google drive sync - this creates a cloud copy

 

del /Q c:\upload\*.*

Set dd=%DATE:~0,2%
Set mm=%DATE:~3,2%
Set yyyy=%DATE:~6,4%

set var=%dd%-%mm%-%yyyy%

rar a -hpwhateverpasswordyoulike -m5 c:\BACKUP\%VAR%\%VAR%_SIMSBACKUP.rar c:\BACKUP\%VAR%\SIMSBACKUP.BAK

copy c:\BACKUP\%VAR%\*.rar c:\upload\

del c:\BACKUP\%VAR%\%VAR%_SIMSBACKUP.RAR

 

Batch 3

 

Compresses and sends a copy to local NAS storage which is located in a separate building (Mapped drive - here as example is X: )

 

Set dd=%DATE:~0,2%
Set mm=%DATE:~3,2%
Set yyyy=%DATE:~6,4%

set var=%dd%-%mm%-%yyyy%

md x:\%var%

rar a -hpwhatpasswordyoulike -m5 x:\%VAR%\%VAR%_SIMSBACKUP.rar c:\BACKUP\%VAR%\SIMSBACKUP.BAK

 

Now if I had to get SIMS up and running though I wouldn't have a clue but I've plenty of places the backup is located!

[LeMarchand]

We just have a backup script that SIMS supplied us when they installed it, I've since changed it a lot

 

That looks a bit more understandable to me! IIRC the .bak files form part of a chain though (meaning one has to keep trimming the chain if space is tight - which it is) or did I get hold of the wrong end of the stick there?

[caffrey]

First batch doesn't care about space as I've lots, so I just manually clear it out now and again - I've been too lazy to fix it

 

Second batch just makes sure the latest backup is always in the cloud

 

Third, just compresses the .bak (usually compresses up to about 25% of the original size) and keeps them all again, again too lazy to fix the script

 

I could even just put them all in one script but it's just been built on over the years

 

Might revisit it during some downtime, replace rar with 7zip (I use command line rar in the same dir as the batch files)

[TwistedHelixis]

We don't use full sql anymore (just express) but when we did we were using that to create database backups and it will also delete any backups after x days.

 

EDIT - Within SQL studio we would create a database Maintenance plane

Edited May 10, 2017 by TwistedHelixis

[dry]

1.

 

Set dd=%DATE:~0,2%
Set mm=%DATE:~3,2%
Set yyyy=%DATE:~6,4%

set var=%dd%-%mm%-%yyyy%

md C:\BACKUP\%var%

"C:\yourlocationofthis\DbAttach.exe" /BACKUP /AUTO /SERVER=yoursimserver\SIMSdatabase /DATABASE=yourdatabase /USER=youruser /PASSWORD=yourpassword /PATH=C:\BACKUP\%VAR%\SIMSBACKUP.BAK"

md x:\%var%

rar a -hpwhatpasswordyoulike -m5 x:\%VAR%\%VAR%_SIMSBACKUP.rar c:\BACKUP\%VAR%\SIMSBACKUP.BAK

Capita supplied a similar script to us when SIMS was initially installed. It's never really sat right with me that credentials for the SIMS DB have been in plain text like that. Not sure whether there's an updated script that Capita now supply but its something I've always been meaning to investigate.

[Esteban_Child_of_the_Sun]

You can use AD authentication for a backup account in SIMS and use trusted on the command line instead of putting in a username and password.

[caffrey]

Capita supplied a similar script to us when SIMS was initially installed. It's never really sat right with me that credentials for the SIMS DB have been in plain text like that. Not sure whether there's an updated script that Capita now supply but its something I've always been meaning to investigate.

 

It's not in a shared folder and the Sims server isn't easily accessible, it's not the best and it does cause me concerns

 

Funny thing was, I fixed the script for the engineer, they were named Monbkup1,Tuebkup1,Wedbkup1,ThuBkup1 and Fribkup1 with 5 separate scheduled tasks, I modified it to take todays date etc. and roll it all into one.

[vikpaw]

I usually just have a maintenance plan in sql management studio, the created bak can then be put to tape. Depending on space availability, the files are compressed, and retired. I always like to keep one per month minimum plus weeklies and dailies, and with SIMS, at least one per update, otherwise a restore will not work well.

 

Key lesson I learned was to ensure all stakeholders, IT Manager, MIS Manager, Network Manager, Backup Operator etc. know what is going on, so the backups don't interfere. Had some nightmare scenarios with multiple backups happening including Symantec sql-agent, and none of the data that went to tape were usable due to a mixture of full, differential, stopped service.... backups.

 

Be careful you check exactly what Capita scripts are doing, sometimes they will play around with other settings for truncation of logs.

 

Old School - System Manager backup was always a good one, and automating with DBAttach is good too, just bear in mind there used to be issues with the logins and having to be transferred in and out of the database to be retained. For this reason, worth backing up your master database along with the SIMS ones. Worth reading this old thread too : http://www.edugeek.net/forums/mis-systems/71629-sims-backup.html