Solved

Compressing backup files with WINRAR

Posted on 2011-03-23
5
2,521 Views
Last Modified: 2012-06-27
I have defined maintenance plans on my SQL SERVER 2005 which creates backups of the data and log files. I want to be able to free space on my system always so i want to compress the backup of the log and data files using WINRAR. My question is how do i go about this using the New Maintenance Plan....   designer.

Thanks
0
Comment
Question by:mamlaide
  • 4
5 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 35201436
If you really want to use the SQL Maintenance Plan Designer then you should add a "Execute T-SQL Statement Task" at the very end where you will need to add the SQL Commands to execute a cmdshell and run a WINRAR cmd string to archive your database "bak" backup files and if WINRAR archive successfully completed delete the original after that. I suggest you build a timestamp in the RAR filename and do it from one LUN to ANOTHER otherwise IO may kill your box
0
 

Author Comment

by:mamlaide
ID: 35201661
Could you help me with the script to do that?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35201910
Sure - you will need to check more on http://www.respower.com/page_tutorial_unrar for the rar.exe and unrar.exe hints to accomodate your needs but basicaly you will need to execute something like SQL below to do the RAR part, then another step to check the file and delete all backups if ok - please make sure you test/tune it all before just let it run against production:

RAR 3.70 beta 7   Copyright (c) 1993-2007 Alexander Roshal   16 Apr 2007
Registered to Dias

Usage:     rar <command> -<switch 1> -<switch N> <archive> <files...>
               <@listfiles...> <path_to_extract\>

<Commands>
  a             Add files to archive <------------------------------------------------ Look here
  c             Add archive comment
  cf            Add files comment
  ch            Change archive parameters
  cw            Write archive comment to file
  d             Delete files from archive
  e             Extract files to current directory
  f             Freshen files in archive
  i[par]=<str>  Find string in archives
  k             Lock archive
  l[t,b]        List archive [technical, bare]
  m[f]          Move to archive [files only]
  p             Print file to stdout
  r             Repair archive
  rc            Reconstruct missing volumes
  rn            Rename archived files
  rr[N]         Add data recovery record
  rv[N]         Create recovery volumes
  s[name|-]     Convert archive to or from SFX
  t             Test archive files
  u             Update files in archive
  v[t,b]        Verbosely list archive [technical,bare]
  x             Extract files with full path

<Switches>
  -             Stop switches scanning
  ac            Clear Archive attribute after compression or extraction
  ad            Append archive name to destination path
  ag[format]    Generate archive name using the current date
  ao            Add files with Archive attribute set
  ap<path>      Set path inside archive
  as            Synchronize archive contents
  av            Put authenticity verification (registered versions only)
  av-           Disable authenticity verification check
  c-            Disable comments show
  cfg-          Disable read configuration
  cl            Convert names to lower case
  cu            Convert names to upper case
  df            Delete files after archiving <--------------------------------------- And here
  dh            Open shared files
  ds            Disable name sort for solid archive
  e[+]<attr>    Set file exclude and include attributes
  ed            Do not add empty directories
  en            Do not put 'end of archive' block
  ep            Exclude paths from names
  ep1           Exclude base directory from names
  ep2           Expand paths to full
  ep3           Expand paths to full including the drive letter
  f             Freshen files
  hp[password]  Encrypt both file data and headers
  id[c,d,p,q]   Disable messages
  ieml[addr]    Send archive by email
  ierr          Send all messages to stderr
  ilog[name]    Log errors to file (registered versions only)
  inul          Disable all messages
  ioff          Turn PC off after completing an operation
  isnd          Enable sound
  k             Lock archive
  kb            Keep broken extracted files
  m<0..5>       Set compression level (0-store...3-default...5-maximal)
  mc<par>       Set advanced compression parameters
  md<size>      Dictionary size in KB (64,128,256,512,1024,2048,4096 or A-G)
  ms[ext;ext]   Specify file types to store
  mt<threads>   Set the number of threads
  n<file>       Include only specified file
  n@            Read file names to include from stdin
  n@<list>      Include files in specified list file
  o+            Overwrite existing files
  o-            Do not overwrite existing files
  oc            Set NTFS Compressed attribute
  or            Rename files automatically
  os            Save NTFS streams
  ow            Save or restore file owner and group
  p[password]   Set password
  p-            Do not query password
  r             Recurse subdirectories
  r0            Recurse subdirectories for wildcard names only
  ri<P>[:<S>]   Set priority (0-default,1-min..15-max) and sleep time in ms
  rr[N]         Add data recovery record
  rv[N]         Create recovery volumes
  s[<N>,v[-],e] Create solid archive
  s-            Disable solid archiving
  sc<chr>[obj]  Specify the character set
  sfx[name]     Create SFX archive
  si[name]      Read data from standard input (stdin)
  sl<size>      Process files with size less than specified
  sm<size>      Process files with size more than specified
  t             Test files after archiving
  ta<date>      Process files modified after <date> in YYYYMMDDHHMMSS format
  tb<date>      Process files modified before <date> in YYYYMMDDHHMMSS format
  tk            Keep original archive time
  tl            Set archive time to latest file
  tn<time>      Process files newer than <time>
  to<time>      Process files older than <time>
  ts<m,c,a>[N]  Save or restore file time (modification, creation, access)
  u             Update files
  v             Create volumes with size autodetection or list all volumes
  v<size>[k,b]  Create volumes with size=<size>*1000 [*1024, *1]
  vd            Erase disk contents before creating volume
  ver[n]        File version control
  vn            Use the old style volume naming scheme
  vp            Pause before each volume
  w<path>       Assign work directory
  x<file>       Exclude specified file
  x@            Read file names to exclude from stdin
  x@<list>      Exclude files in specified list file
  y             Assume Yes on all queries
  z[file]       Read archive comment from file
 
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35201959
OK so sql code to run will be like the one below and if you look at the switches I used -mf and -m5 means MOVE FILES to archive and m5 = max compresion:

declare @crtdate varchar(255)
declare @sqlcmd varchar(4000)

set  @crtdate = CAST(YEAR(getdate()) AS VARCHAR(4))+
case when MONTH(getdate()) < 10
      then '0'+CAST(MONTH(getdate()) AS VARCHAR(2))
      else CAST(MONTH(getdate()) AS VARCHAR(2))
end +
case when DAY(getdate()) < 10
      then '0'+CAST(DAY(getdate()) AS VARCHAR(2))
      else CAST(DAY(getdate()) AS VARCHAR(2))
end
--print @crtdate


--real ZIP command
set @sqlcmd = 'EXEC xp_cmdshell ''rar.exe a -mf -m5 "C:\SQL_BACKUP\SQL_BACKUP_'+@crtdate+'.zip" "E:\SQL_BACKUP\*_backup_'+@crtdate+'*.bak"'''

if (select DATENAME ( dw , getdate())) = 'friday'
--exec (@sqlcmd)
print @sqlcmd
GO
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35201971
obviously you need to check your drives and folder names and replace in code above not just run it in your environment and also muat be sure you have rar.exr in your OS path
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now