Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.