October 1, 2008 at 2:23 am
Hi Friends,
I just got an idea to compress the backup file of SQL 2000 and SQL 2005 to get some free space in some of our servers and along with the methods I have, searched through internet and got below methods... Now this post is for two things...
1) If you have any thing to share related to this topic, Please reply so that it will help all
2) Incase you are not aware of this methods, You can very well try it...
Method 1 (SQL 2000 and SQL 2005 both)[/b]
There is a option in NTFS drives and folder to make it as compressed folders / drives so which ever the files placed in that folder, Filesystem will automatically compress the files.
Disadvantages:
--> Resource hungry
--> Takes more time since backup and then compress need to happen.
Method 2 (SQL 2000 and SQL 2005 both)[/b]
Use zipping utilities like GZip, WinZip, 7-Zip, WinRar etc which supports command line version to compress the bkp files once created by SQL Server and then once compressed file is created then delete the normal file so save space.
Disadvantages:
--> During the time of compression we need space for both normal file and compressed file.
--> Some of above tools are freeware so cannot be used in production environment.
--> Need to purchase licensed version we opt for Winzip etc.
Method 3 (SQL 2000 and SQL 2005 both)[/b]
Once you confirm that SQL backup files are taken into Tapes then we can delete the backup file in harddisk using Post backup script options available in backup client.
Disadvantages:
--> We will not have atleast most recent backup in harddisk so incase of any urgent restore, It will take time.
Method 4[/b]
Use SQL 2008 which comes with Backup compression technology by default else use Third-Party softwares like Red Gate Software’s Lite Speed, HyperBac for SQL Server, SQLSafe etc
Disadvantages:
--> Need to buy license for each server, With this cost we can buy extra disks itself...
Method 5 (Applicable only for SQL 2000)[/b]
Backup directly to PIPE and run zipping utilities to compress from pipe so that Backup + Compression can be done on the fly!
Disadvantages:
--> Using Pipe has security threat because of which it is disabled in SQL 2005.
--> It needs more Memory to perform the backup + compress operation than SQL Server Memory consumption...
Please let me know incase any of the above methods works perfectly in your environment (incase VLDB or light DB's).. Any other method you know.. etc.. 🙂
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
October 1, 2008 at 2:39 am
2 additions:
- SQLSafe has a freeware version, which does 95% of your backup tasks.
- use MSSQL2008 😀
Wilfred
The best things in life are the simple things
October 1, 2008 at 2:47 am
I'd stay away from doing method 1 (NTFS compression)if you can as it does slow down SQL Server backup and recovery and also the offline backup to tape.
It's good to have the latest backup if possible although for huge databases this may not be possible as they may need to go straight to tape (method 3)
Method 4 - SQL 2008 only has compression with Enterprise Edition. We've used LiteSpeed with 2000 and 2005 and have found that both backup times and sizes are reduced by around 60%.
How big are you databases and what hardware are they running on (SAN etc)?
October 1, 2008 at 5:47 am
Your options are very correct but needs to find out the best options as per your environment and license availability.
We are having 20-25 GB of Data in SQL 2000 and doing winzip and compress the backup up to 2-3 GB. Than use robocopy to move compress file to other server or drive for XX days retention.
But this won't work for medium or large DB due to time constraint.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply