[sims] SIMS backup on Server 2008 (not R2)...

[Koldov]

Hi all,

 

Not sure if I should post here or the in the server forum, but here goes...

 

Over the summer holiday I have succesfully (I think) moved SIMS onto a 2008 server (many thanks to EDUGEEK) from our old and soon to be not supported 2003 server.

 

Unfortunately, I am now having to re-think our backup schedule!

 

We don't have any 3rd party software apart from very old copies of Cobian (running on the 2003 server) and Veritas BackupExec 9 (which I found in the old technicians cupboard unopened!) and I believe that only works up to Server 2003 OS.

 

I need a solid plan for backups before we get back into it (hopefully without buying any new 3rd party software as I am on my own with no budget for the next few weeks), however I know there are limitations to Server 2008 backup.

 

Also I believe that a live SQL database like SIMS may not be backed up in a normal server backup without services being stopped and/or databases being detached.

 

Does anybody run a system like this?

 

Thanks in advance.

 

Kol.

[pantscat]

Windows Server Backup will be absolutely fine for this - it uses VSS so you'll get a "good" SQL backup.

 

Just make sure that your backup schedules are regular enough and you have enough space on your backup target.

I'd also recommend testing your backups too! - no point having them if they don't work!

[fiza]

Use DBAttach to detach the SIMS Database and then backup the db files. In my last school we had a script that would do this in the middle of the night and reattach afterwards. I am sure I got it from someone on Edugeek.

[pantscat]

One more thing to add - why not talk to Capita and see what they recommend/support?

[fiza]

Here it is : http://www.edugeek.net/forums/mis-systems/85759-dbattach-script.html

[Mcshammer_dj]

Yuo don't need to detach the database using dbattach. simply use the built in backup process via a command line script. I also use the exellent sims tidy routine created by @matt40k to keep the size down

[fiza]

Yuo don't need to detach the database using dbattach. simply use the built in backup process via a command line script. I also use the exellent sims tidy routine created by @matt40k to keep the size down

 

Ah Things must have changed (Im not responsible for SIMS in my new place). I got the original script from your kind self!

[Mcshammer_dj]

yeah that script edited will still work fine and if you follow @fiza 's link then you can get the copy of the the tidybackups

[Koldov]

Yuo don't need to detach the database using dbattach. simply use the built in backup process via a command line script. I also use the exellent sims tidy routine created by @matt40k to keep the size down

 

Thanks for this, actually dbattach, scripts and scheduled tasks were all used together somehow on the old 2003 server (then Cobian copied it all onto a USB drive), but I didn't write or configure any of it, so I don't really understand how it all came together.

 

Unfortunately, I can't read the words 'simple' and 'command line script' in the same sentence without a look lol. I know I am limited with Server 2008 as to my backup location and type of backup, but can I take it from these posts that I can just set the inbuilt server backup utility to backup the relevant folders (SIMS and FMS databases) to a local USB drive that I can swap out everyday?

 

Kol.

Edited August 20, 2013 by Koldov

[Koldov]

One more thing to add - why not talk to Capita and see what they recommend/support?

 

Last time I talked to Capita, they were horrified I was using USB drives to backup to and not tape (isn't support for tape being dropped for new Server OS'?)...

 

Worth a try I guess.

 

Kol.

[Koldov]

However, reading that other thread, this worried me:

 

I've also got a tool somewhere to transfer the passwords from the MASTER database to the actual database (ie SIMS), so when you backup the SIMS database, it includes all the passwords.

 

As I've moved the SIMS and FMS databases, am I going to have trouble with people logging in now if I didn't move the passwords too?

 

Kol.

[vikpaw]

Use teh built-in backup, with SQL Management studio. You can setup a maintenance plan and get it to email you when done. then use Matt's tidy backups to keep the store in order.

No need to stop services or detach data.

That's if you have full SQL, if not, then there is a third party option i came across on a google search the other day, or script it.

[matt40k]

I've also got a tool somewhere to transfer the passwords from the MASTER database to the actual database (ie SIMS), so when you backup the SIMS database, it includes all the passwords.

 

You'll be pleased to know that SQL2012 allows you to store the user\pass in the database rather then in the local MASTER db... Assuming Capita enables it

Until then - SIMS Transfer Tools - https://simstransferlogins.codeplex.com/

 

And a bit of a plug...

 

TidyBackups - TidyBackups - Home

[Koldov]

Aaaaaaagh! I wasn't a fan before, but this is driving me nuts!

 

Anyway, my Business Manager has just popped in and I asked him to try to log in to SIMS. He logged on fine, so what am I missing here?

 

Below is a screenie of my MSSQL DATA folder contents:

 

 

Does that look about right? I don't remember copying the 'Master Database' and I've looked through the thread that helped me and I didn't see it mentioned, so I doubt I did... As far as I can remember I just used dbattach to put the SIMS and FMS databases on.

 

Kol.

Edited August 21, 2013 by Koldov

[creese]

Aaaaaaagh! I wasn't a fan before, but this is driving me nuts!

 

[ATTACH=CONFIG]20158[/ATTACH]

 

 

Sorry not had chance to read the thread... but your Sims.ldf looks rather large and needs shrinking.

[vikpaw]

You don't need to worry about passwords if they seem to be working.

Usually the detach process copies the passwords into the sims file for you, then when you attach they get sent to the master table. In fact I believe this happens when you just restore a backup but it will only push back whatnot had in their already.

 

I'D be more worried about your huge transaction log file. You need to back it up then shrink it. Either using the shrink command in SQL management studio or try a system manager backup in sims and see if that does the trick.

[Koldov]

I'D be more worried about your huge transaction log file. You need to back it up then shrink it. Either using the shrink command in SQL management studio or try a system manager backup in sims and see if that does the trick.

 

Well, I'm pretty sure it's never been done as nobody here (including me ) knows anything much about SIMS...

 

I do a SYSTEM MANAGER backup pretty much everyday during term time and I've just run one now and the .LDF file hasn't changed size at all.

 

Kol.

[vikpaw]

If you did a full SQL install ie not express then in SQL management studio you can right click on the db and shrink files from the tasks list. But back it all up first

[creese]

There is a ShrinkDBLog.bat file available from Capita if all else fails.

[matt40k]

@Koldov be worth getting your LA\Capita\who ever to remote in. Worth them spending an hour or so doing a bit of a heath check and giving you a few points. I used to find it was worth doing for the new IT staff as they tend to fine afterwards.