+ Post New Thread
Page 1 of 4 1234 LastLast
Results 1 to 15 of 50
MIS Systems Thread, SIMS Tidy Backups - SQL 2008 - Autumn release in Technical; Hi All, As you are all hopefully aware, Capita will be releasing SIMS Autumn release on the 9th November, with ...
  1. #1

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158

    Smile SIMS Tidy Backups - SQL 2008 - Autumn release

    Hi All,

    As you are all hopefully aware, Capita will be releasing SIMS Autumn release on the 9th November, with any lucky. Included in this is support for MSSQL 2008. There are a number of changes that will affect your setups, such as transaction log being replaced with Simple recovery model, so no more shrinking the log file! However on a more negative point you will lose tidy backups from System Manager and more importantly, compression from the backups. (See Resource: 16028)

    I know this may sound bad, however, I think is a positive step. Have you ever had a problem in SIMS and you've had the head or such breathing down your neck to fix it, so you log a call with Capita or your Local Authority only to have them say, it's a known bug and there is a patch. So you decide to do it during the day, only to have dbupgrade take what seems like a lifetime to run a ruddy backup, and it appears to have crashed, at this stage you start to ask yourself, do I quit and use the /nobackup option? Well if you've ever looked into this, Capita did a while ago create a patch that removes compression, and OMG, it does help, I've seen backups take seconds (if the database is loaded in RAM and you have fast discs).

    So this is good right? We'll how many of you routinely check you MSSQL\backup folder? Now you need start to look at compression as well. Lovely. Well I didn't like the idea of doing this, so I've created a program that will do this for you. I've uploaded it onto SupportNet, ID 688, failing that, it's attached on here too.

    Overview.
    It's entirely written in C#, hopefully well. It contains a built-in ZIP library - SharpZip, which in my opinion appears to be quicker then the Microsoft ZIP library. It only requires .NET Framework 2.0 - or it should . It's a small little app, 134KB in total, digitally signed to ensure it's unmodified. It will only compress uncompressed MSSQL backups (.bak) and will only delete compressed files (.zip) and MSSQL backups (.bak). It deletes before it compresses, so you don't waste time compressing only to delete. You can set the maximum age of the backup and it will delete them older then the date specified, it uses the date created. Compression is optional, and if you do choice to, it will set the creation date to the same as the MSSQL backup (.bak).

    Usage
    Download the program, save it to the local disc on the (SIMS) MSSQL server, I generally say create a batch file, but you can do whatever, run from command line…

    Tidybackups.exe /PATH:[PATH TO YOUR MSSQL\BACKUP] /DAYS:[NO OF DAYS] [/ARCHIVE]

    For example:
    Tidybackups.exe /PATH:"C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" /DAYS:30 /ARCHIVE

    This will delete any compressed files (.zip) and MSSQL backups (.bak) over 30 days old, any remaining MSSQL backups will be compressed, and the uncompressed version deleted.

    If you want to do logging, you can ofcourse redirect the output to a file, so add >>log.txt
    This will write any files that are compressed and\or deleted.

    Once you have create a batch file, I would then schedule it to run, daily, just to keep on top of things. Hopefully you'll find this useful, just remember it's not just limited to SIMS!

    EDIT: Now on sourceforge at: tidybackups | Get tidybackups at SourceForge.net

    Matt
    Attached Files Attached Files
    Last edited by ZeroHour; 11th May 2010 at 03:49 PM.

  2. 12 Thanks to matt40k:

    Arthur (23rd October 2009), bossman (22nd October 2009), Grommit (10th November 2009), Jobos (22nd October 2009), Rawns (6th November 2009), Rick2134 (10th February 2010), Sivadam (21st October 2009), sjatkn (8th November 2009), skunk (22nd October 2009), splattthecat (26th July 2010), timzim (9th November 2009), vikpaw (24th October 2009)

  3. #2
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    This sounds good Matt.

    'Cos I ain't technically minded (well not to any great degree) I'll alert my colleague, who is, so that he can look into this for us.

    I would have thought that Capita would have been provided something similar though.

  4. #3
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    Quote Originally Posted by matt40k View Post
    However on a more negative point you will lose tidy backups from System Manager and more importantly, compression from the backups.
    Eugh

  5. #4

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Tidy backups still needed you to do it manually and compression was rubbish. They used Dynazip, and the company that made it went under, it couldn't support anything over 4gb and made the whole backup process waaay longer that it needed to be.

  6. #5
    DMcCoy's Avatar
    Join Date
    Oct 2005
    Location
    Isle of Wight
    Posts
    3,432
    Thank Post
    10
    Thanked 488 Times in 428 Posts
    Rep Power
    111
    Ah yes, my archives are corrupt if I let system manager do it. I just have an export running from MSSQL instead. That file then goes on to tape each day. Just one file that gets overwritten, I do make a copy when running patches or dangerous operations though.

    What I'd really like to see is working auditing.

  7. #6
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    SM Backups and Tidying Backups works ok for me.
    And YES they are viable backups.
    They regularly get sent down to Capita for UAT purposes and I fully working for me when I get there!

  8. #7

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    @Sivadam
    Re-read my first post, Tidy backups is going in Autumn as part of SQL 2008, as per Jon Wood's presentation at the SIMS Update Semiar, you can get the sides from SupportNet -16028.

    The issue DMcCoy is refering to is an known bug with large database files or the issue with the CRC. Both issues with Dynazip, which Capita will iron out by removing it.

  9. #8
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Hi Matt40k,

    First off, this looks like it will be very useful! However, while testing, I have come across an issue.

    It seems that compressing the file does not function correctly. A zip file of the same name as the *.bak file is created, but the *.bak file is not added to the zip file before it is deleted. It leaves us with no *.bak file and a zip file 1k in size. When ran from a command line, the script completes in second, not near long enough to compress a file.

    Example from my test:

    Code:
    C:\Backup>tidybackups.exe /PATH:"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" /DAYS:7
     /ARCHIVE
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ccsfms_20091022_140740.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ccsfms_20091022_143508.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_20091022_140735.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_20091022_141333.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_20091022_143501.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_20091022_144008.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_20091022_140738.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_20091022_141334.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_20091022_143503.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_20091022_144010.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\sims_cp361_mnw_20091016_093113.zip
    Compressed - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ccsfms_20091106_143508.bak
    Deleted - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ccsfms_20091106_143508.bak
    Any advice you could offer to get this working would be appreciated as I would really like to use this!

  10. #9

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,675
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    45
    Never had a proble with compression or the length of time it took to create them. I am not happy backup compression has gone

  11. #10

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Quote Originally Posted by Stuart_C View Post
    Never had a proble with compression or the length of time it took to create them. I am not happy backup compression has gone
    Glad it's gone, backups now take seconds not hours. Compression can be done later when the system is not use.


    @Rawns

    So you have a file called ccsfms_20091106_143508.bak and a file called ccsfms_20091106_143508.zip? Mmm... not actually thought of this scenario as it shouldn't happen automatically, I assume someone has uncompressed the file from the zip. I'll have a think how I can deal with this and make new release. Thanks for the feedback.

  12. #11

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158

    Talking

    Updated version
    • Output date\time (good if your logging)
    • If .bak exists and a same named .zip exists, it'll search inside the zip for the same file, if it has the .bak already, it'll just delete the .bak. Otherwise it'll .zip it. (For example, you extract the .bak file and forget to clean up afterwards)
    • I've added the source code as well
    Attached Files Attached Files

  13. Thanks to matt40k from:

    Rawns (9th November 2009)

  14. #12
    Jobos's Avatar
    Join Date
    Apr 2007
    Posts
    1,133
    Thank Post
    178
    Thanked 49 Times in 42 Posts
    Rep Power
    24
    Can't open the zips, both files are corrupt.

  15. #13

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,253
    Thank Post
    181
    Thanked 375 Times in 304 Posts
    Rep Power
    173
    Quote Originally Posted by Jobos View Post
    Can't open the zips, both files are corrupt.
    Same here.

  16. #14

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Works ok here???

  17. #15

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,253
    Thank Post
    181
    Thanked 375 Times in 304 Posts
    Rep Power
    173
    Is it geographical?

SHARE:
+ Post New Thread
Page 1 of 4 1234 LastLast

Similar Threads

  1. [SIMS] SIMS 2010 Autumn Pre-Release Note
    By matt40k in forum MIS Systems
    Replies: 8
    Last Post: 28th September 2010, 09:06 PM
  2. Move to SQL 2008 and backups
    By thesthelensman in forum MIS Systems
    Replies: 2
    Last Post: 13th April 2010, 02:16 PM
  3. SIMS Autumn 09 Release - Experiences?
    By pete in forum MIS Systems
    Replies: 35
    Last Post: 26th December 2009, 02:34 PM
  4. SIMS.net Autumn release - Options 5.7
    By localzuk in forum MIS Systems
    Replies: 0
    Last Post: 1st December 2009, 03:37 PM
  5. Error Doing Tidy Backups in Sims.Net
    By tech_guy in forum MIS Systems
    Replies: 0
    Last Post: 7th January 2008, 01:58 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
  •