+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
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. ...
  1. #1
    CadlaM's Avatar
    Join Date
    May 2008
    Location
    Hertfordshire
    Posts
    55
    Thank Post
    10
    Thanked 1 Time in 1 Post
    Rep Power
    0

    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.

  2. #2
    CadlaM's Avatar
    Join Date
    May 2008
    Location
    Hertfordshire
    Posts
    55
    Thank Post
    10
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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?

  3. #3
    ass17's Avatar
    Join Date
    Feb 2013
    Posts
    326
    Thank Post
    5
    Thanked 35 Times in 34 Posts
    Rep Power
    26
    Doesn't BUE have an SQL module (purchased separately) that allows backups of live DBs?

  4. #4
    CadlaM's Avatar
    Join Date
    May 2008
    Location
    Hertfordshire
    Posts
    55
    Thank Post
    10
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  5. #5

    Join Date
    Feb 2012
    Location
    London
    Posts
    30
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    6
    how big is your database?

    i do full hourly backups for our sims database.
    It is 9gig, gets done in about 4 mins.

  6. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  7. #7
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,765
    Thank Post
    898
    Thanked 417 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    87
    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.

  8. #8

    Join Date
    Feb 2007
    Location
    Kent
    Posts
    634
    Thank Post
    273
    Thanked 51 Times in 43 Posts
    Rep Power
    40
    Quote Originally Posted by zag View Post
    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.
    just testing this before migrating to sql 2012 (which our current version of backup exec doesn't support)
    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?

  9. #9
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,765
    Thank Post
    898
    Thanked 417 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    87
    Quote Originally Posted by Greenbeast View Post
    just testing this before migrating to sql 2012 (which our current version of backup exec doesn't support)
    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?
    Yes it zips them. You can turn that setting off I believe.

    The professional versions also do encryption so you could backup to a cloud drive directly if wanted.

  10. #10

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    Quote Originally Posted by vikpaw View Post

    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.
    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'.

    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 12:47 PM.

  11. #11

    Join Date
    Feb 2007
    Location
    Kent
    Posts
    634
    Thank Post
    273
    Thanked 51 Times in 43 Posts
    Rep Power
    40
    Quote Originally Posted by zag View Post
    Yes it zips them. You can turn that setting off I believe.
    doh! I saw the files were zipped but figured it would never compress that much, just unzipped one and all is well

    Quote Originally Posted by jinnantonnixx View Post
    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'.
    With sql/sims what would be a good restore test? (i.e. without disrupting a live system)

  12. #12

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    Quote Originally Posted by Greenbeast View Post

    With sql/sims what would be a good restore test? (i.e. without disrupting a live system)
    For DR, assume you have nothing to work with.
    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 10:49 AM.

  13. Thanks to jinnantonnixx from:

    zag (16th December 2013)

  14. #13

    Join Date
    Feb 2007
    Location
    Kent
    Posts
    634
    Thank Post
    273
    Thanked 51 Times in 43 Posts
    Rep Power
    40
    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

  15. #14
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,765
    Thank Post
    898
    Thanked 417 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    87
    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.

  16. #15

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,970
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [SIMS] SIMS SQL Databases and Maintenance Plans
    By Olliesaurus in forum MIS Systems
    Replies: 12
    Last Post: 15th November 2013, 03:42 PM
  2. Backups, QNAP and Backup Exec
    By alexsanger in forum Wireless Networks
    Replies: 1
    Last Post: 12th December 2011, 02:43 PM
  3. Oracle S7000 and Backup Exec 2010
    By Duke in forum Hardware
    Replies: 3
    Last Post: 26th October 2010, 08:39 AM
  4. Windows 2008, SQL 2008 and Backup Exec 12
    By joe90bass in forum Windows
    Replies: 2
    Last Post: 5th May 2010, 02:49 PM
  5. Server 2003 and backup exec 12
    By Halfmad in forum Windows Server 2000/2003
    Replies: 1
    Last Post: 23rd March 2009, 10:38 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •