+ Post New Thread
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 35
MIS Systems Thread, Backing up SQl transaction logs (starting to panic) in Technical; Personally, I prefer to adding a single SATA drive to the server in secondary school that is dedicated for doing ...
  1. #16

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,425
    Thank Post
    368
    Thanked 642 Times in 524 Posts
    Rep Power
    159
    Personally, I prefer to adding a single SATA drive to the server in secondary school that is dedicated for doing backups. Means you can do full backups in mins, also remember SQL backups can run with users still using the system.

    Don't get me wrong, FULL+logs have their place, the guys behind me swear by them, but they're a team of three dedicated to SQL backups, managing databases in their hundreds and are many times bigger then a schools "big" 5gb database.

  2. #17

    Join Date
    Apr 2010
    Posts
    2,047
    Thank Post
    83
    Thanked 188 Times in 155 Posts
    Rep Power
    84
    I have had a chat with the LA and they say you do not need the transaction logs to import a Sims / FMS database if you use the DBatach tool from Capita. They have said they have done this lots of times without any issues.

    I am just testing

  3. #18

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,983
    Thank Post
    113
    Thanked 503 Times in 339 Posts
    Blog Entries
    2
    Rep Power
    285
    On the face of it, I would suggest that they've made a mistake. They've chosen a model (Full) which implies the use of logs, but their recovery method suggests that they should be using the Simple model.
    Last edited by jinnantonnixx; 24th June 2014 at 01:19 PM.

  4. #19

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,425
    Thank Post
    368
    Thanked 642 Times in 524 Posts
    Rep Power
    159
    Quote Originally Posted by jinnantonnixx View Post
    On the face of it, I would suggest that they've made a mistake. They've chosen a model (Full) which implies the use of logs, but their recovery method suggests that they should be using the Simple model.
    Yup. Might be worth changing tack, perhaps raise it as my .ldf is rather large and let them follow the breadcrumbs.

  5. #20

    Join Date
    Apr 2010
    Posts
    2,047
    Thank Post
    83
    Thanked 188 Times in 155 Posts
    Rep Power
    84
    On the face of it, I would suggest that they've made a mistake. They've chosen a model (Full) which implies the use of logs, but their recovery method suggests that they should be using the Simple model.
    Apparently the info they are using has come directly from Capita.

  6. #21

    Join Date
    Apr 2010
    Posts
    2,047
    Thank Post
    83
    Thanked 188 Times in 155 Posts
    Rep Power
    84
    This is confusing me. Just had this detailed reply from the LA.

    We follow guidelines from Capita for backups of SIMS and FMS. Capita recommends a .bak file for each database – SIMS and FMS – and this is the method that we use in our schools and also advise technicians to employ. We have fully tested this method on our school servers, with regard to both backups and restores. We also recommend restoring using the Capita DBAttach utility which is located via the Start menu in the SIMS Applications folder or in the binn folder of the SQL installation path.

    Transaction logs are part of a much more complicated method of backup that is usually implemented for large organisations that have to restore to a specific period of time during a given day. These are not required for a backup system such as SIMS in schools, which are set up for full backups generally once at the end of the day.

    Here is a link to the Capita documents on SIMS and FMS backups:

    http://www.capita-sims.co.uk/files/s...simsandfms.pdf

    Capita’s preference is generally backing up again via their DBAttach tool, which is an alternative option to the Maintenance Plan route. However the DBAttach backup must either be run manually or from a command line, usually via a scheduled task. We tend to use Maintenance Plans as they are a bit simpler to implement and easy to automate. Both the Capita tool and SQL Maintenance Plans produce the required .bak files for each database.

    So your current backup system, if set according to the LA guidelines, is correct for backing up the SIMS and FMS databases in your schools.


    I hope this information is helpful. Thanks very much.
    I am still concerned as I remember reading on support net that there is a difference in the way dbattach and sql manager backup the database.

  7. #22

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,983
    Thank Post
    113
    Thanked 503 Times in 339 Posts
    Blog Entries
    2
    Rep Power
    285
    Quote Originally Posted by edutech4schools View Post
    This is confusing me. Just had this detailed reply from the LA.



    I am still concerned as I remember reading on support net that there is a difference in the way dbattach and sql manager backup the database.
    .bak is just a file extension. It gives you no clue as to the nature of the backup file.

    I'm 100% certain they meant to use simple rather than full.
    Last edited by jinnantonnixx; 24th June 2014 at 04:16 PM.

  8. #23

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,007
    Thank Post
    679
    Thanked 1,396 Times in 1,156 Posts
    Rep Power
    353
    I agree with the others, proceed with caution. It's possible you are in Full mode because at some point, someone has run a script which puts the db into Full! I think this was a side effect of both the shrinklog batch file and the reindex patch.

    Or someone just set it up wrong. The important part of their reply is
    if set according to the LA guidelines
    Check what they think they are , and i'm sure they'll come back and say you should be in Simple.

    Find out who supports you, or who will support you in a disaster recovery scenario, and confirm the changes you will be making to the system. Namely, changing the recovery model to Simple, and ensure they agree with it. I'm pretty sure this will be your LA, so you need them to check your backup setup and compare to their guidelines and Capita's.

    Once you change to Simple mode, your transaction log file will be redundant with regards to the backups, and if you do want/need to shrink it, you can do so after backing it up. There are instructions for this on here and on SupportNet.

    On Capita's SupportNet - have a read of resource 14931 it will help explain how the passwords are stored, and also about backing up in various ways, though probably not the Management Studio method you want. It's v.useful though and also highlights the need to also backup docstorage and other files that are required to get a fully working SIMS setup, not just the db!

    Strangely, when i went to check the above resource number i came across a patch which has been withdrawn, which was apparently created yesterday with this note:
    Patch 14463 - This was a Datafix created to truncate the log file. This patch is structured incorrectly. The patch puts the database into simple recovery mode, shrinks the log file and then always returns the database back to full recovery mode meaning the log file grows again The school run out of space.


    EDIT: just saw their link, and it's the same resource i referenced. I don't think it mentions the recovery model in there, but i'm sure ages and ages ago they publicised that Simple was the way to go.
    Last edited by vikpaw; 24th June 2014 at 03:15 PM. Reason: addition

  9. #24

    Join Date
    Apr 2010
    Posts
    2,047
    Thank Post
    83
    Thanked 188 Times in 155 Posts
    Rep Power
    84
    So what is this simple method? It is not an option I can see in SQL manager.

  10. #25

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,007
    Thank Post
    679
    Thanked 1,396 Times in 1,156 Posts
    Rep Power
    353
    It is accessible from SQL Management Studio
    It's a recovery model. Right click the DB, select properties then look at the options panel.

  11. #26

    Join Date
    Apr 2010
    Posts
    2,047
    Thank Post
    83
    Thanked 188 Times in 155 Posts
    Rep Power
    84
    Just called the LA with all your posts in hand and they say that Capita 100% wants the database set to Full as a recovery model. If anyone can locate any info from Capita that says anything different please post, otherwise I will try and do a disaster recovery test next week.

  12. #27

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,007
    Thank Post
    679
    Thanked 1,396 Times in 1,156 Posts
    Rep Power
    353
    They probably don't, they just 'recommend'.
    KB78225 , KB104766 and actually looking at their new shrinkdblog tool, it doesn't change the recovery mode back to Full.
    There's no point arguing with them. Just get them to check that the setup is as they want it. There's no use us saying one thing if they are adamant they want something else. Just make sure they tell you the setup is okay. Check the size of your LDF i imagine it's probably pretty big, if they aren't backing it up or truncating it.

    Disaster recover will work to a degree up to the point of the backup, but you won't have things between backups unless the logs are taken. That's my understanding anyway, @Jinn' will confirm. So for a proper test, you want to restore a backup the way they do it, and compare it to the live system as late as possible after the last backup was taken, to see if you're catching changes made during the day.

    I know some people, for ease, just do a series of Full, Simple backups through the day. Say, hourly, so the most info they will lose is the last hour.

  13. Thanks to vikpaw from:

    edutech4schools (25th June 2014)

  14. #28

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,425
    Thank Post
    368
    Thanked 642 Times in 524 Posts
    Rep Power
    159
    Quote Originally Posted by edutech4schools View Post
    Just called the LA with all your posts in hand and they say that Capita 100% wants the database set to Full as a recovery model. If anyone can locate any info from Capita that says anything different please post, otherwise I will try and do a disaster recovery test next week.
    Do you have Attix 5 \ RedStor \ RBUSS installed? That needs it to be FULL.

  15. Thanks to matt40k from:

    edutech4schools (25th June 2014)

  16. #29

    Join Date
    Apr 2010
    Posts
    2,047
    Thank Post
    83
    Thanked 188 Times in 155 Posts
    Rep Power
    84
    Do you have Attix 5 \ RedStor \ RBUSS installed? That needs it to be FULL.
    @matt40k Yes RBUSS. Thanks.

  17. #30

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,425
    Thank Post
    368
    Thanked 642 Times in 524 Posts
    Rep Power
    159
    Ah, then that's right. Not 100% sure how RBUSS\Attix 5 works, I just know it needs it set to FULL and it uses dbattach to backup and your LA has a dashboard to check the status of all the backups. It checks for diff at a bit level of the .bak file backup and then backups only those changes. If you set it to SIMPLE it sends the whole thing rather then just the changes, same thing if you compress (ie zip) it, so just leave it and it does it's magic. Clearly whoever made it is very clear and has some deep understanding of MS-SQL backup format - it really does just work.

    The password problem is prob what I detailed above, might be worth reseting the sysman password on that backup copy you restored just to confirm - also undo any changes you may have done.

  18. Thanks to matt40k from:

    edutech4schools (25th June 2014)

SHARE:
+ Post New Thread
Page 2 of 3 FirstFirst 123 LastLast

Similar Threads

  1. Backing up Microsoft SQL
    By RabbieBurns in forum Enterprise Software
    Replies: 10
    Last Post: 5th March 2012, 12:18 PM
  2. Backing up SQL 2008
    By glennda in forum Windows
    Replies: 5
    Last Post: 6th August 2010, 12:19 PM
  3. Backing up SQL database ?
    By Kyle in forum Windows
    Replies: 4
    Last Post: 8th June 2006, 09:36 PM
  4. Replies: 11
    Last Post: 26th May 2006, 10:01 AM
  5. Mac software for backing up DVDs
    By dagza in forum Mac
    Replies: 14
    Last Post: 23rd May 2006, 11:29 AM

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
  •