How to zip up a back file after a Maintenance job? - 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 up a back file after a Maintenance job?

Author

Topic

pras2007
Posting Yak Master

216 Posts

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

Hello All,

I have a maintenance job that backs up the following databases in the below directory:

S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_1\Database_1_backup_201003291100.bak
S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_2\Database_2_backup_201003291100.bak
S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_3\Database_3_backup_201003291100.bak
S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_4\Database_4_backup_201003291100.bak

The goal is to add additional module to the SSIS package to zip up the following databases using Winrar:

Database_1
Database_2
Database_4

Is this doable?

The script below is what I have researched to do it but, it zips up the file in the below directory:

- S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_1\Database_1_backup\ Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_1\Database_1_backup

How can I make it save under this directory?

S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_1\ Database_1_backup_201003291100.rar
DECLARE @COMMAND VARCHAR(1000)

SET @COMMAND = 'C:\"Program Files"\WinRAR\winrar.exe A S:\"Microsoft SQL Server"\MSSQL.1\MSSQL\Backup\Database_1\Database_1_backup_201003291100.rar S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Database_1\Database_1_backup_201003291100.bak'

EXEC XP_CMDSHELL @COMMAND

Please advice.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-30 : 10:30:30

see here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142171

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 11:07:52

From Books Online (xp_CmdShell):

command_string cannot contain more than one set of double quotation marks.

Why it has this limitation, I have no idea, but... Yours has 2 sets of double quotes, and would have needed 3 (one in the bak file path).

Perhaps it would be easier to write a .bat file that zips the backup, and call that from xp_CmdShell.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources