October 28, 2007 at 2:03 am
Downloand 7-Zip command line utility from the below link and make use of it. The utility is totally free and im using in my environment.
http://downloads.sourceforge.net/sevenzip/7za442.zip
Regards..Vidhya Sagar
SQL-Articles
October 29, 2007 at 4:09 am
I think you need to talk to your management about cost and risk.
Using standard SQL backup followed by a Zip is potentially greater cost and risk than using a 3-party tools such as RedGate SQL Backup or Quest Litespeed.
Using normal SQL Server backup means you use a lot of disk space. You then need more disk space while the zip is running. This costs $$$. Eventually you can delete the original backup, but in the meantime you need a lot of work space on your disks. When you unzip you also need the disk space for the zip file and the original file.
Using SQL backup followed by a zip followed by deleting your original backup adds risk. Your backup process is now multiple-stage, which in itself adds risk. If for some reason the zip does not work correctly in a way your script does not recognise, then you have a non-working zip file and a deleted backup file.
The 3-party tools above (there are others, some just as good, some maybe not so...) all run in far less time than a standard SQL backup and produce a compressed output file. These files do not need to be de-compresed for a restore. In many cases the disk space savings in just having compressed backups pay for the tool. Having a one-step backup using a reliable tool is much less risky than the scripting you are proposing.
By all means create a script that zips SQL Server backups, but make sure your management know this has higher risk and possibly cost than the alternatives.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 29, 2007 at 8:10 am
EdVassie (10/29/2007)
I think you need to talk to your management about cost and risk.Using standard SQL backup followed by a Zip is potentially greater cost and risk than using a 3-party tools such as RedGate SQL Backup or Quest Litespeed.
Using normal SQL Server backup means you use a lot of disk space. You then need more disk space while the zip is running. This costs $$$. Eventually you can delete the original backup, but in the meantime you need a lot of work space on your disks. When you unzip you also need the disk space for the zip file and the original file.
Using SQL backup followed by a zip followed by deleting your original backup adds risk. Your backup process is now multiple-stage, which in itself adds risk. If for some reason the zip does not work correctly in a way your script does not recognise, then you have a non-working zip file and a deleted backup file.
The 3-party tools above (there are others, some just as good, some maybe not so...) all run in far less time than a standard SQL backup and produce a compressed output file. These files do not need to be de-compresed for a restore. In many cases the disk space savings in just having compressed backups pay for the tool. Having a one-step backup using a reliable tool is much less risky than the scripting you are proposing.
By all means create a script that zips SQL Server backups, but make sure your management know this has higher risk and possibly cost than the alternatives.
I couldn't agree more with you Ed...I guess you have to see the environment here...this is my first position as a DBA so I can't compare it to anything previously...
Prior to me coming to this position there was no standard for disaster recover and process improvement. The company is in the middle of a migration to SAP so the need for the current system to be developed and improved is non existent...I am in the realm of supporting the old system while the new system is put in place.
In the context of this forum post my question was for a test system so I would not be too worried about backups/zips/ect...and having problems if something goes wrong as it is just test and I was looking for a way to automate the workload.
In a production environment I agree that it probably isn't the best process and some third party tool would be ideal to use...I have never used RedGate or any other tool...are they available for a demo?
For **** & Grins I attached the script I came up with using the ideas and help of others from this post...the only application you will need is the gzip utility...hopefully the comments will help explain the logic and work flow. Once you enter the variables for the script you should run it on the test sql server and it will generate the output script which you can copy and run in a new window...
October 29, 2007 at 11:15 am
Jo Pattyn (10/13/2007)
http://www.7-zip.org/%5B/quote%5D
I'll give my stamp of approval on 7-zip. I have used it for years now. Not only does it have the command-line aspect that you need, but it's compression algorithms are superior to most zip formats if you use the 7z formats. (you can still use compatible modes too).
October 29, 2007 at 11:28 am
Scott Coleman (10/11/2007)
WinZip Pro costs a whopping $50....... If you want to risk your job over putting shareware on the company servers, that is.
WinZip is really becoming BloatWare now so I'm not sure I'd want that on a server either. gzip would get my vote for something like this on a server
James Horsley
Workflow Consulting Limited
October 31, 2007 at 8:46 am
We have been using RedGate's SQL Backup here at work for a few years now and have had no problems with it. On average, we see around 70% compression on our backup files. For example (real data), we had a 240 GB backup file compressed to a 94 GB backup file in 43 minutes.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply