How can i compress when I create SQL backup file in a folder?

Question

I use SQL Server Express and I create backup files for all databases every day using Time Scheduling script. One script Like that;

BACKUP DATABASE [Jira]
TO DISK = N'C:\Backup\Jira.bak'
WITH COMPRESSION,INIT
GO

And they made 3 .bak (backup) file in my C:/backup folder.

What I want is, when I create these .bak files, I want also compress them automaticly (Maybe using with Winrar, Winzip, 7zip or SQL Express Server properties)

Is there any way that I can do that ?

Answer 1

You could alternatively use a batch script.

Here is an example one, it will need to be tweaked a little. In this example I use 7zip, which is free.

@echo off

CLS

SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-%TIME:~3,2%

echo %backuptime%

echo Running dump ...

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S 
(local)\SQLExpress -i D:\dbbackups\bk_%SQLExpressBackups.sql

echo Zipping ...

"C:\Program Files\7-Zip\7z.exe" a -tzip "D:\dbbackups\zipped\bk_%backuptime%.zip" "D:\dbbackups\bk_%SQLExpressBackups.sql"

echo Deleting the SQL file ...

del "D:\dbbackups\bk_%SQLExpressBackups.sql"

echo Done!

Or if you want to just zip up the back up foler, after the back up is done you could do the following:

@echo off

CLS

SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-%TIME:~3,2%

echo %backuptime%

echo Zipping ...

"C:\Program Files\7-Zip\7z.exe" a -tzip "C:\dbbackups\zipped\bk_%backuptime%.zip" "C:\Backup\Jira.bak"

echo Done!

Save this as sqlbackup.bat and schedule it to be run everyday.