[sims] SIMS Backup (Idiotic Question)

[Gongalong]

Hi folks,

 

I know that backing up SQL Server (and therefore SIMS) isn't that straightforward.

 

Is it possible to backup the SIMS database from one folder to another while it's in use?

 

TIA

[Greg]

You can use

OSQL -S -E -Q "BACKUP DATABASE SIMS TO DISK = '\SQLSIMS_SCH.bak'"

Put your required info between the <>'s

 

I always prefer all users out though

[Gongalong]

Thanks. Presumably it could be setup as a scheduled task over the weekend, when it's likely not to be in use.

[glennda]

You should be able to setup a SQL agent job to run a backup to a local drive partition - back this up using any other backup software

 

 

 

You then need to take a copy of the Simsroot folder (say once a week as not much changes) and the docstorage share. The doc storage is more important,

[twin--turbo]

it was once part of sims anyway to do it's own SQL backup. have they got rid of that ( it did cause some trouble with disk space )

 

Rob

[glennda]

it was once part of sims anyway to do it's own SQL backup. have they got rid of that ( it did cause some trouble with disk space )

 

Rob

 

You can still use DBattach I think (although been out of the loop with sims for a while.) I'll see if I have the dbattach script on my email somewhere

[Gongalong]

There is the folder MSSQL10.SIMS\MSSQL\Backup. For us this currently has 10 .BAK files, all around 3GB each. 5 of them are dated 13th Sep, 3 are dated 17th Aug, 1 is 30th Jul, and the last is 18th May

 

Edit: there's also D:\SIMS\SIMS\SQL DATA BACKUP which has an EXE file in (dated 22 Oct) around 500MB. There are other folders though with lots of data e.g. D:\SIMS\SIMS\DOCSTORAGE, which has around 6GB. Our SIMS.LDF file in D:\Microsoft SQL Server\MSSQL10.SIMS\MSSQL\DATA is 58GB (!).

Edited October 23, 2012 by Gongalong

[glennda]

There is the folder MSSQL10.SIMS\MSSQL\Backup. For us this currently has 10 .BAK files, all around 3GB each. 5 of them are dated 13th Sep, 3 are dated 17th Aug, 1 is 30th Jul, and the last is 18th May

 

That would be the backups Sims is auto-doing when you perform and upgrade or from inside System Manager.

 

Attached is a script which uses DBAttach. I've taken this from a friend and accept no responsibility.

dbattach.zip

[jinnantonnixx]

As @Greg has said, you can script a backup with code like his or use SQL Management Studio to configure a regular job to backup the database.

 

There's absolutely no need to get anyone out of the system, though. The SQL server is designed to be backed up while it's in full flow with no problems. The only reason to do it out of hours is that the backup might have a noticeable impact on performance.

[Gongalong]

We already backup SIMS with DPM, but this is a further offsite backup which we'd like to do via CrashPlan. We're likely to just use the file/folder backup of CrashPlan, but of course I'd need to backup SIMS in such a way that we could be sure of restoring the content.

 

I thought it best to also check this with our CC IT team, as they installed the server. I'll feed back their advice here.

[GREED]

+1 for SQL backup via SSMS. Although remember due to SIMS' weird way of managing permissions if you restore using this backup usually user account passwords are screwed up. Peeps on here know a script to fix this though.

[vikpaw]

I use a backup job i've setup in SQL Management Studio, and this has a linked task to email me either success or failure. The only time it ever fails is because there is not enough room on the share i'm backing up to.

 

Your LDF file is massive, and you should ask your CC team about their implementation method and what they suggest. You can shrink this, after doing a transaction log backup. It might be the case that you switch to Simple Mode, which will prevent this growth. You don't really want an LDF that big. How big is the MDF? Around 3GB?

You can also alter the growth rate to control it more.

 

It might be worth looking on Capita's SupportNet for their best practice guides as well.