CODEWITHSUNDEEP
sql-serversql-server-2005backup
DCNYAM
DCNYAM

Reputation: 12126

SQL Backups in Compressed Folder

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?

Upvotes: 2

Views: 5260

Answers (5)

Dave Markle
Dave Markle

Reputation: 97691

While I don't recommend backing up directly to a compressed folder, you can certainly backup to a normal folder, then use a PowerShell script to compress each file individually when the server isn't too busy. Here's a snippet of what I use. It basically recurses through by backup directory, compressing each file using NTFS compression, up to a certain size. It's not the greatest solution; my preferred solution will be to use SQL 2008/2012 backup compression instead when my upgrade is performed.

$max_compressible_size = 10*1024*1024*1024 #10 gigs 

$files = Get-ChildItem -recurse | where {$_.Extension -match "(trn)|(diff)|(bak)"}

foreach ($file in $files) {    
    $attr = (Get-ItemProperty $file.fullname).Attributes

    if ($attr.ToString() -notmatch ".*Compressed.*" -and $file.Length -le $max_compressible_size) {
        write-output $file.FullName
        compact /C $file.FullName
    }
}

Upvotes: 0

Clay Lenhart
Clay Lenhart

Reputation: 1606

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)"

Upvotes: 3

Kristen
Kristen

Reputation: 4291

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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.

Upvotes: 0

Arvo
Arvo

Reputation: 10570

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 :)

Upvotes: 1

Related Questions