how to zip the last backup file from a device

[ersatz]

Hi,

I have a maintenance plan and implicit a daily backup, at 22 PM.
I want to take this backup (the last backup from device), to zip and to send the file to a second server, unzip and restore the file.
My problem is that I don’t know how to put this file into my variable.
Any idea?
Thanks for the help

 

[NoCoolHandle]

I would tend to script the whole action via tasks,steps and schedules.

You could then simply Backup the database with a fixed nameing convention @Database + @yymmdd

On completion of that step
master.dbo.xp_Cmdshell 'C:\zippingprogram.exe /switchs'

on completion of that step
master.dbo.xp_cmdshell 'copy c:\myzippedfile.zip \\somepc\someshare'

My 1c

 

[mrdenny]

I wouldn't bother zipping up the file. The file shouldn't compress all that much. And the time you save by making the file slightly smaller will be less than the time needed to compress the file.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[NoCoolHandle]

Mr Denny, have you tried zipping a sql backup?
you might be very surprised 11 mb to 7 mb is worthwhile if you ask me (and I have seen better)

 

[donutman]

Especially good if you set the output of the zip to the 2nd server so that you can compress and copy at the same time. However, if you have a fast connection to the 2nd server, then I'd agree with Denny...why bother?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

Hugh, That is suprissing that it compresses that much. Truth be told, I've always been plessed with all SQL Servers on the same LAN with 100 base or Gig networks between machines so I've never had to worry about it.

It you are moving large files look at robocopy.exe to move the files. It is MUCH more effecient when it comes to moving files across machines. When moving a copy file using copy.exe of the GUI to copy/move the file my network usage went to like 10%. With robocopy it went to like 95%+.

It was much faster. You can find robocopy via google or resource kit for windows (I think it has a different name these days).

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[ersatz]

Hi all,
Thanks very much for your help.
I will use robocopy command and I will not zip the file.
But I have another question: how can I delete the backup files older then 7 days.
Thanks again.

 

[mrdenny]

Robocopy can again come is handy for this as well. Create a ToBeDeleted folder on the drive with the backups on it. Then have robocopy move all files older than 7 days into that folder, then delete everything in that folder. There are dozzens of switches for robocopy, one of which will tell it to move or copy all files older than X days. When you have robocopy run "robocopy /???" from the command line to get all the switches.

Denny

--Anything is possible. All it takes is a little research. (Me)