June 29, 2004 at 11:57 am
I am looking for a way to zip up the backup files and logs as a daily set. Has anybody set this up before?
TIA
Jack
July 1, 2004 at 3:26 am
Hello Jack,
I've done it in the past and I will attach a script which shows the basic techniques. Anyhow be aware of a couple of drawbacks.
First of all most compression tools can not handle files bigger that 2Gb when you run them from the command line.
Second there can be quite a performance impact if you zip the bacjkup files on the same machine as you SQL server.
Third in case of a restore it takes longer to get the database online again.
If your company is ready to invest some money try out SQLLitespeed or SQLZip. Both tools compress backup files and they are much fatser than standard SQL backup.
/*BackupDB procedure
Purpose:
Makes a backup of a defined database and copies the backupfile to another location. Optionally the backupfile can be zipped wich will reduce the filesize with about 80%.
The filename is build from the currentdate + description. For instance: "Feb_24_1999MasterBackup.zip"
Example calls:
"BackupDB "Master", "<description>" ,"C:\temp\Masterbackup.BAK", "c:\temp", 1 Master will be backed up and zipped to C:\temp
"BackupDB "Master", "<description>" ,"C:\temp\Masterbackup.BAK", "\\Server1\Backup", 0 Master will be backed up and copied to a servershare
For the zip-part I use a commandline zipper from PkWare wich can be found at http://www.pkware.com. Put the following line in the batchfile (or customize) and
change the pathname in the batchfile and in this procedure to match your locations of Pkzip25.exe and the batchfile.
D:\Pkzip\Pkzip25.exe -add -silent -path=root %1 %2
Replace "-add" with "-move" to get rid of the big backupfile
*/
CREATE PROCEDURE BackupDB
@Db VARCHAR(30) ,
@Description VARCHAR(50) , -- Backuplabel
@LocalDisk VARCHAR(50) , -- Location and filename for the backupfile
@DestDisk VARCHAR(50), -- Location where the file will be copied to
@Zip BIT = 0 -- 1 = Zip file after backup.
AS
SET NOCOUNT ON
DECLARE @cmdline VARCHAR(100)
DECLARE @zipfile VARCHAR(75)
/* Backup database*/
BACKUP DATABASE @Db
TO DISK = @Localdisk
/* Options */
WITH DIFFERENTIAL, --makes a differential backup
WITH INIT , --overwrites existing backupfile
NAME = @Description,
NOSKIP ,
STATS = 10,
DESCRIPTION = @Description, NOFORMAT
IF @Zip = 1
BEGIN
/* Zip backupfile to destinationsdisk */
SELECT @Zipfile = @DestDisk + "\" + LEFT(REPLACE(GETDATE(),' ','_'),11) + @Description
SELECT @cmdline = "D:\SQL\Zipbackups.cmd " + @Zipfile + ' ' + @Localdisk -- Change the location of your batchfile in this line
EXEC master..xp_cmdshell @cmdline, NO_OUTPUT -- You may want to remove the NO_OUTPUT for testing
END
[font="Verdana"]Markus Bohse[/font]
July 1, 2004 at 9:36 am
Zipping backup file(s) can sometimes be inconvenient because you need additional space for the zip files, both during the backup and restore process. Some compression applications require additional workspace while compressing the files.
If you are using the zip format, look for those supporting the ZIP64 format. These handle large files.
And aside from SQLLiteSpeed and SQLZip, there's also MiniSQLBackup from our company, that performs backup and restores directly to/from compressed files.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply