Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i've got a pretty simple maintenance plan that does a full backup of my db to a network'd location. works perfectly.

I'm wanting to 7zip the backup file AFTER the backup was done.

Is there a way i can do this in a maintenance plan? maybe using powershell?

EDIT:

i don't have os access to the network drive. just think of it as another hard disk, not a server. it's technically my mapped Z:\ drive.

share|improve this question
    
I know how I'd attack it under Linux :) –  warren Sep 20 '09 at 9:19
    
@warren, how do you know the network location isn't Linux? –  nray Sep 20 '09 at 11:21
    
@nray - I don't, but since he says he doesn't have OS access to the share, I'm presuming that my solution wouldn't work :) –  warren Sep 20 '09 at 15:12
    
I don't see how saying that you know how to attack it under Linux contributes anything to the question, really. –  Darth Satan Sep 20 '09 at 16:05
    
@Pure.Krome - nray's example script is just the normal Windows shell, CMD.EXE, you might call it a batch file or a command script or the DOS prompt. –  nray Oct 1 '09 at 13:50

3 Answers 3

A SQL Agent job can execute cmd.exe scripts.

A maintenance plan can execute SQL Agent jobs.

(Just create the agent job without a schedule.)

share|improve this answer

Scripting %ProgramFiles%\7-Zip\7z.exe in a script as @Richard mentions was my first thought, but you said you're streaming your backup over the network.

This means you'd want to execute the compression on the file server, not the SQL server (where SQLAgent runs), otherwise you'll be streaming all that data back to %TEMP% on the SQL server in order to compress it, just to return the compressed bits to the file server.

If that's true, then your options are to execute 7z.exe on the file server at the end of backup, perhaps by one of these methods:

  • psexec \\FILESERVER C:\7-Zip\7z.exe a todays-full-sql-backup.7z 200909201800_db.bak or something like that. You need to define the name of the .bak file you want to compress somehow.

  • A scheduled task on the file server, set to run after the backup has had plenty of time to finish.

  • PowerShell 2 remoting, but this isn't anything I've tried - way too new.

Other ideas - why do you need a 7z archive? Is it a solution to compress the directory you're backing up to? Do you have space to back up locally on the sql server, 7zip the file and then send it over the network?


Edit: To determine the name of the backup file you can cheat quite a bit. You don't need to know the whole name. You can try something like "for each file named 2009MMDD*_MyDatabaseName_DB.bak, zip this file, copy it to \server\share, check for errors, delete the file".

Here's the barest bones of an UNTESTED shell script, something like,

set DB_NAME=MyDatabaseName
set BAK_DIR=<wherever your backup gets created>
set NET_SHARE=\\Server\Share

:GET_NAME_DATE_PART
rem   #This depends entirely how Windows writes dates in your part of the world
rem   #if I were you I'd probably get some utility (now.exe?) to extract yyyymmdd 
rem   #from today's date. On our locale %date% looks like 2009-09-20
set YYYY=%date:~0,4%
set MM=%date:~5,2%
set DD=%date:~8,2%

:ARCHIVE_BAK_FILE
pushd "%BAK_DIR%"
for %%A in (%YYYY%%MM%%DD%*_%DB_NAME%_DB.BAK) do (
    "C:\Program Files\7-Zip\7z.exe" a %%A %%A.7z
    move %%A.7z "%NET_SHARE%\%%A.7z"
    rem   #CHECK FOR ERRORS MAYBE?
    del /q %%A
)
popd

The for statement should only ever find one file per day to zip up and delete. That funky %date:~0,4% syntax is called variable substrings, if you haven't seen it before - it's in the help. You might well prefer forfiles.exe to for.

share|improve this answer
    
Yep. I can definately backup the solution to my local sql drive. i have '3' disks on the server. OS (raid 1), log file (raid 1), db (raid 5). so i can throw the backup on a temp location in the OS drive, 7z, then move it to the mapped network drive (ie. Z:\Backup). With that idea, how could I achieve this? How do i know the name of the backup (it's derived from the date, like u noted, above). –  Pure.Krome Sep 20 '09 at 13:20
    
You can query the msdn database to get the name of the file that you backed up to. Look in the tables which start with backup% in the table name. Then use this filename to dynamically create the 7zip command to run. –  mrdenny Sep 20 '09 at 20:02
    
@Pure.Krome - You only need to know as of the file name as to make it unique, not the hour-minutes bit. You know the date, and the pattern SQL Server uses to generate names. –  nray Sep 20 '09 at 22:17
    
@Nray, what scripting language is this in the example? –  Pure.Krome Oct 1 '09 at 0:53
    
@Pure.Krome - This is just the normal Windows shell, CMD.EXE. –  nray Jan 8 '10 at 15:52

Combining what Richard and nray have said, initiate a 7zip-based archiving script at the end of the SQL backup (via an SQL Agent job).

You can use the follow CMD/batch file as a reference or even adapt it as necessary (it's opensource - and blame any bugs on me ;)

Ziparcy is a WINNT shell script that can be scheduled to create/update an encrypted zip/archive of your files (and directory structure).

This script leverages two other free utilities: 7-zip and wget.

share|improve this answer

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.