SQL Backups in Compressed Folder

Question

We are looking for a free solution to compress our SQL Server backups for SQL Server 2005. I am aware of SQL Safe freeware edition, but I was wondering what others thought of storing backup files in compressed folders to achieve the desired result. This would allow us to use the native SQL backup tasks and native windows compression, thereby eliminating the need for third-party products.

I have not used compressed folders for anything in the past. Is this a viable idea? Are there any foreseeable issues with the integrity of the backups in a compressed folder?

Answer 1

Quick hint from google: backing-up-to-a-ntfs-compressed-folder. Seems that database size may create problems.

What about freeware solutions then you can run 7zip in scheduled batch after backup, should handle big files correctly. You should move backups outta production server anyway :)

Answer 2

Thanks. That was exactly the type of "gotcha" I was looking for. I did consider a batch job for compression, and it may come down to that.

We do have a whole backup infrastructure with a tape library and all, but we keep one or two nights worth of backups on a separate drive on the server. This way, if our other backup system fails to complete the backups, we still have the backup files until we get the primary straightened out.

Answer 3

I use Syncback Freeware to automatically copy and compress the files to another folder. You save tons of space this way. I have been doing this for several years and it has worked perfectly.

Answer 4

We have in the region of 100 databases on servers at our ISP

The ISP has comprehensive backups to tape, but they take a long time to restore anything ... so we need to keep backups "online" for a reasonable length of time so we can recover from them first-and-foremost.

We produce Full backups once a week, and Differential backups daily. We keep Full backups for a month and Differential backups for seven days We produce TLog backups every 10 minutes, but only store them (online) for two days.

So we can recover to point-in-time for the previous 2 days, to "midnight" for the last 7 days, and to "Sunday before" for the last 4 weeks.

The use of Differential backups reduces the total size of our backups, and has enabled us to keep more "elapsed time" online.

All backups are stored in NTFS compressed folders. We've never had a problem, but I don't think we have any databases > 30GB on these "shared DB servers"

During database maintenance (Index defrag etc.) we increase the frequency of our TLog backups to every minute. We found that the one thing which extended the DB size was the DB maintenance, so by doing this we have been able to reduce the operating size of our databases, and in turn free up some disk space for more backups. (Backups generally on different drives to MDF or LDF, but Batch operations copy older, stale, backup files to drives containing MDF / LDF just so we can keep them "online")

P.S. SQL 2008 has inbuilt compressed backup capability, in case you are planning to upgrade

Answer 5

I'm the author of an open source project (GPL v3) that does what you are looking for. It is a command line tool (to schedule in Scheduled Tasks in Windows) and backs up to gzip, zip (specifically zip64 due to the 4GB limitation with regular zip files), or bzip2 files. You can use the tool to restore directly from the compressed files, or you can uncompress the compressed files (which are standard *.bak files) and then use the SQL Server GUI to restore.

It's called SQL Server Compressed Backup, found here: http://sourceforge.net/projects/mssqlcompressed/

A sample command would be:

msbp.exe backup "db(database=model)" "zip64" "local(path=c:\model.full.bak.zip)"