Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
    
I already create .bak files mine C:/backup folder. Just i want to zipped that files. As I understand, I just only create new notepad, write "C:\Program Files\7-Zip\7z.exe" a -tzip "D:\dbbackups\zipped\bk_%backuptime%.zip" "D:\dbbackups\bk_%SQLExpressBackups.sql", Than save as .bat, and run everday in task scheduler. Right ? – Soner Gönül Jul 19 '11 at 10:32
    
Yep. If you schedule it for after the backup is scheduled then it should run and zip up your files. I'll amend the question. The zip file will be named with a timestamp. – slotishtype Jul 19 '11 at 11:04
    
You will need to make sure that the backup does not overlap with the zip, that's why the first approach is probably better. – slotishtype Jul 19 '11 at 11:10
    
How can i put this .bat file into Time Scheduler ? – Soner Gönül Jul 19 '11 at 11:48
1  
Great! It perfectly works. – Soner Gönül Jul 19 '11 at 13:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .