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

windows backup script compression sql-server

13,241

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.

13,241

Soner Gönül

Software Architect at Akbank. I love solving problems with writing code. Always have, always will. I solve LeetCode and Hackerrank problems, and other algorithms on my Youtube channel. I also stream on Twitch. Tiktok: tiktok.com/@soner_gonul Instagram: instagram.com/sonergonul/

Updated on September 18, 2022

Comments

Soner Gönül over 1 year
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 ?
Soner Gönül almost 13 years

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 ?
slotishtype almost 13 years

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 almost 13 years

You will need to make sure that the backup does not overlap with the zip, that's why the first approach is probably better.
Soner Gönül almost 13 years

How can i put this .bat file into Time Scheduler ?
slotishtype almost 13 years

Check this out. It details how to run a batch at a specific time.
Soner Gönül almost 13 years

What is SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-‌%TIME:~3,2% for ?
slotishtype almost 13 years

It puts a timestamp on the zip file something like bk_10-07-1978.zip so that the zip file is not constantly overwritten.
Soner Gönül almost 13 years

Great! It perfectly works.
slotishtype almost 13 years

Good to hear. ...