How can I do this to a Maintenance Plan

Hi folks,

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.

From serverfault Pure.Krome

• 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.)

  From Richard

• 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.

  Pure.Krome : 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).

  mrdenny : 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.

  nray : @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.

  Pure.Krome : @Nray, what scripting language is this in the example?

  nray : @Pure.Krome - This is just the normal Windows shell, CMD.EXE.
  From nray

• 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.

  From micahwittman