How to zip a backup file using WinRar? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to zip a backup file using WinRar?

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2010-03-29 : 09:30:14

Hello All,

I got a script from the net and it is not working for me. The following is what it suppose to do:

1. Backup the database
2. Zip up the backup database using WinRar
3. Delete the backup .bak file

Everything works except for number 2, does everyone what wrong? I have tried different variation of single quotes and double quotes around the path name.

Below are the scripts

Please help.

--BACKUP

BACKUP DATABASE [DARS] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CARS_BACKUP.BAK'

--COMPAC RAR

DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(100), @FILE VARCHAR(100)

SET @SERVER = @@SERVERNAME

SET @COMMAND = '"C:\Program Files\WinRAR\RAR.exe" A "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CARS_BACKUP.BAK"'

EXEC XP_CMDSHELL @COMMAND

--EXECUTE FILE BACKUP

EXEC XP_CMDSHELL 'DEL "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CARS_BACKUP.BAK"'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-29 : 12:42:52

use winrar, not rar. also need to specify destination archive file. and "program files" needs double quotes around it.

i'd do this in a SQL Agent job with 3 steps:
1. Execute backup -- T-SQL
2. zip it -- os cmd
3. delete bak file -- os cmd

here's a command file i use to zip up a bunch of txt files i generate with BCP

del dr.rar
C:
cd C:\"Program Files (x86)"\WinRAR|
winrar a c:\xfer\dr c:\xfer\*.txt

that red pipe is actually a backslah (\), but the forum software keeps messing with it

pras2007
Posting Yak Master

216 Posts

Posted - 2010-03-29 : 16:25:47

Thanks Russell for the response, your suggestion works but it is not zipping the back file in the correct location. Also I will try to zip up the backup files after maintenance job finish... Since I will need help with that particular task I posted another question.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-30 : 10:11:55

winrar a c:\xfer\dr c:\xfer\*.txt

winrar a pathToArchiveFile locationOfFile(s)ToZip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources