MIS Systems Thread, SIMS/SQL and Backup Exec plan in Technical; I'd be very interested in SIMS current thinking on SQL backups. We had SIMS engineers set up SQL and SIMS. ...
21st November 2013, 01:16 PM #1
SIMS/SQL and Backup Exec plan
I'd be very interested in SIMS current thinking on SQL backups. We had SIMS engineers set up SQL and SIMS. They left it 2 years a go running an MS schedule to kick of a SQL maintenance plan. We just updated our Symantec Backup Exec and I need to know the correct/best way to back up as BUE complains it cannot do incremental backup. this seems to be due to the SQL Maint Plan reseting archive state, but BUE not being able to do Inc backup of since last full from Itself.
22nd November 2013, 11:01 AM #2
I've asked the SIMS support desk the same question and they have not come back with anything yet. Only thing I could find was a 2010 document regarding dbattach manual backups.
Anyone any ideas?
22nd November 2013, 11:36 AM #3
Doesn't BUE have an SQL module (purchased separately) that allows backups of live DBs?
22nd November 2013, 11:51 AM #4
Yes BUE bought with SQL module. In fact it works if I do a full backup as SQL Management Plan also does a full 5 day a week bu to file on disk. BUE then complains when it wants to do an incremental b/u as it realises some sort of back up has been carried out since its last Full, so fails. Currently I am setting up a plan to do Weekly Full and Daily Incr BUE b/u Excluding SQL. SO that grab the SIMS files that they b/u via MP. Then at Weekend doing a Full BUE with no incrementals set of SQL only. I think that will give me belt and braces backup of SIMS requirement and a full system b/u.
FYI it seems recent BUE selling and licensing is same Agent cost for server, or Exchange, or SQL. So you just tell them you have, say, 8 agents including Exch and SQL.
22nd November 2013, 01:25 PM #5
- Rep Power
how big is your database?
i do full hourly backups for our sims database.
It is 9gig, gets done in about 4 mins.
26th November 2013, 09:23 AM #6
Never have multiple backup systems running like that, we had once ages ago and it breaks the chain of integrity. You're lucky it's complaining and not going ahead with the incremental.
If you did corrupt the chain like that, you'd not be able to use the incrementals only the Full ones.
Just stick to one system, and save it to disk or other location, then back up that location with the other system so they don't clash. I wouldn't have one method to do Full, and then another system to do Incrementals.
The main thing to think about is how do you want to restore and what are you willing to lose. I've never been convinced by differential / incremental on a database file. I'd rather have a full backup. We do a daily full backup. If you want more you can do them more frequently, or look at backing up transaction logs, but it's a bit overkill unless you know what you are doing and how to restore them. Plus some routines in SIMS will break the integrity, e.g. the shrinkfile routine to reduce your ldf file, the re-index patch also does something funny i think.
In terms of restore, if you use a full backup file or whatever you've got, then you will lose all data between that and the previous one. So we backup daily, and have to be prepared to lose a day's work. This is clearly unacceptable, i'd never ever want to restore to a backup, unless there was some major crazy issue.
It's more likely that you'll take your last good backup and restore to a test server and then extract manually or by report the bits of data you need and import them or manually enter them into the live system.
So the question is really - how likely is it that data created during the day, is subsequently deleted or corrupted before that night's backup. Usually not that likely.
Similar to regular file backups, if you do them nightly, then you're prepared to not handle files created and lost/deleted on the same day, unless you are also running shadow copies, versioning or undelete on the server.
We have an SQL plan to do nightly backups to disk, then the network guy points Backup Exec to the backup location and pulls them off using their grandfather differential strategy. As i'm doing full dailys it picks up each new file daily, and this goes to tape.
If you're confident with your BUE then use that and turn off the SQL stuff. I prefer to stick to doing it via SQL as it's built-in and i control it, i also have other maintenance tasks that i run around them, and i get emails when the task is successful etc. The main BUE stuff is across the system and a full backup at end of week can take over a day to do , so i let the NM worry about that side of it and whether it's successful in the long list of other backups it does.
The other thing with the backups is, make sure you do a test restore every now and then, to be sure whatever system you have is working and the files aren't corrupt.
26th November 2013, 10:01 AM #7
We just use Free SQL backup software for MS SQL Server 2008, 2012 and 2005 (Express) databases - SQLBackupFTP
Very cheap and does a perfect job of backing it up on a schedule.
the professional version also encrypts the data files which is very useful.
16th December 2013, 09:49 AM #8
just testing this before migrating to sql 2012 (which our current version of backup exec doesn't support)
Originally Posted by zag
The full backups (of the sims db) are only ~640Mb in size whereas the mdf file is 2.9Gb, do you know why this might be?
16th December 2013, 09:50 AM #9
Yes it zips them. You can turn that setting off I believe.
Originally Posted by Greenbeast
The professional versions also do encryption so you could backup to a cloud drive directly if wanted.
16th December 2013, 10:05 AM #10
Great advice from @vikpaw. His last point is important - until you perform a test restore and check that your system works, all you have at the moment are 'alleged backups'.
Originally Posted by vikpaw
As Vik also mentions, some SIMS tools will ruin your backups if you use incremental/differential backups, as the tools change your recovery model without asking permission or warning you.
Last edited by jinnantonnixx; 16th December 2013 at 01:47 PM.
16th December 2013, 10:50 AM #11
doh! I saw the files were zipped but figured it would never compress that much, just unzipped one and all is well
Originally Posted by zag
With sql/sims what would be a good restore test? (i.e. without disrupting a live system)
Originally Posted by jinnantonnixx
16th December 2013, 11:27 AM #12
For DR, assume you have nothing to work with.
Originally Posted by Greenbeast
Ideally, on an isolated network, build a new server and restore the full system to that. You can do it all virtually, if you like, creating a virtual isolated network. Tune a SIMS client to the new server and check that it works. This way you will be able to prove that you can replace your SQL server with a slot-in replacement and still function with no tweaking (changing server names, instance names, connect.ini, Docstorage file structure, etc).
For a casual test (to simulate data loss, rather than server loss), you can restore a SIMS backup to a new database (e.g. TESTSIMS) using your existing SQL instance. Change the client's connect.ini to point to the new test database. Because you've effectively changed the name of the database, you'll need to do a few things to get it to work.
Cannot login after SIMS SQL restore << read this, it covers a lot of good stuff
Check you can log in, check it works. Now is the time to find problems, not when you're under pressure.
Oh yes, and make proper, clear instructions. The last thing you want when you're under pressure is to forget vital steps and make mistakes. And print the notes. On paper. Not left on a computer that won't start up.
Last edited by jinnantonnixx; 16th December 2013 at 11:49 AM.
Thanks to jinnantonnixx from:
16th December 2013, 11:32 AM #13
Ok good plan, we're just going virtual bit by bit at the moment, i'm migrating to a new virtual sims machine, hence the need for a short term backup solution (until we buy into a more comprehensive/updated solution for all our servers)
So I will experiment with that in the new year
16th December 2013, 12:20 PM #14
If you use Veeam for backups they have a really good sandbox mode that you can use to test your backup strategies.
Although saying that, their VM backup is so good I've never needed it
The days of having to individually restore Sims data files or server OS's are long gone with Veeam! Thats the real power of snapshots and virtualization.
16th December 2013, 12:54 PM #15
I've had a few issues with backups that are stored locally, especially with a VM, as it gets incorporated into the snapshots, and so total size can quickly grow.
Also it's not easy to clone / move a hulking great VM with snapshots. Admittedly I use them as a fallback to different versions, as it's much easier to restore a March backup to the 'just before you ran the spring update' version of sims server, than have to deal with the horror of working out how to downgrade the system.
I'd say if you create the backups to file, then redirect them to a fileserver / share. Not just a local drive.
By Olliesaurus in forum MIS Systems
Last Post: 15th November 2013, 04:42 PM
By alexsanger in forum Wireless Networks
Last Post: 12th December 2011, 03:43 PM
By Duke in forum Hardware
Last Post: 26th October 2010, 09:39 AM
By joe90bass in forum Windows
Last Post: 5th May 2010, 03:49 PM
By Halfmad in forum Windows Server 2000/2003
Last Post: 23rd March 2009, 11:38 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)