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.
| Author |
Topic |
|
Mmats
Starting Member
47 Posts |
 Posted - 2005-07-12 : 11:21:16
|
| I notice that backups of transaction logs can be reduced to about 1/10 of their normal size by using winzip. Is there any method in transact-sql that can perhaps reduce the size so its easier on bandwidth? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-12 : 13:30:09
|
| We do FULL backup once a week, DIFF backup daily and TRANSACTION backup hourly (or more often in some cases).We store the backups on a compressed folder (i.e. Windows Compressed)This is to reduce the amount of storage we use locally on the machine in general, and tapes etc. overall, and to allow us to keep more backups online - i.e. to be able to restore without having to resort to Tape [which generally requires finding the right tape, getting someone to restore it, and other time comsuming measures!]If we move a backup from A to B regularly (e.g. to a stand-by server, or from Production to Test / Dev or somesuch) we usually ZIP the backups to reduce bandwidth, and time, in transmisssion [yes, we get about 90% size saving too].We do this using a command line Zip tool from PKWARE - its available options are a minefield!!, but once mastered there are some particularly useful ones (e.g. setting the date/time on the Zip file from the most recent file in the zip, rather than the time at which the Zip was made).I am pretty sure that WinZip have a command line tool too.WinZip has an, optional, proprietary compression method which results in even greater savings - I haven't checked how much - and "strong encryption"; these could be useful for transmitting files between machines, but OTOH I am against Zipping if it uses truckloads of CPU resources to make the Zip if that in turn slows down the SQL box - so doing the ZIPping at quiet times gets my vote.Kristen |
 |
|
|
Mmats
Starting Member
47 Posts |
Posted - 2005-07-12 : 13:55:12
|
| Ahh good ideas. I hadnt thought of using windows compression.I have a followup question then. If the backup log is stored on one computer in a compressed folder, and then you copy it to an uncompressed folder on another machine, how much bandwidth is used. In other words, if the file was 3mb compressed and 6mb uncompressed, would the bandwidth used be 3 or 6? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-12 : 14:17:16
|
| 6mb :-(Kristen |
|
|
|
Mmats
Starting Member
47 Posts |
Posted - 2005-07-12 : 14:24:07
|
that is pretty horrible. thanks |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-07-12 : 16:52:28
|
Ohh my god how do you manage the restore if the db goes down at the end of the week24 * 7 = 168 incrementals wow you really like to work under pressure. You can do anything at www.zombo.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-13 : 14:03:40
|
| "how do you manage the restore"Me?Restore last Sunday's FULL ... then last night's DIFF ... then each TRANS thereafter.Actually, I have an SProc that I use:EXEC USP_MyRestore 'MyDatabaseName'and it spits out the complete RESTORE script which I can hand edit for any variations.But going into Enterprise Manager and ticking the boxes would be pretty much as easy!Kristen |
|
|
|
|
|
|
|
|
Shrinking Backups of T Logs