question

Leo avatar image
Leo asked

Is that right way to do?

I am middle of setting up the Co-Location Server and stuck with transfering the DB Backup file acrosss the VPN.

We have one dedicated VPN channel over 20MB Broadband with 3.5MB Upload Speed. A plan is before I setup for the Log shipping, I want to test a copy of Full Backup Transfer over night and restore onto Co-Location Server. I have 6 production servers with handfull of Databases. For Instanse - one backup DB size is **30GB** (After Compressed backup through ENT Edt..) and it will takes ages to transfer to Co-Lo Site. It might takes days to transfer.

I don't think it works over 3.5MB upload speed internet. What do you do if you are in the same situation like me? I can try another way. I have another secondary server in my office. I can Backup and Restore onto Secondary Server first then run the script below to remove the unuse space (to cut down the DB Size) (that query might took 3 hours) and backup again on Secondary with compressed backup and transfer to Co-Lo site. --Shrink DB with left 10% of free space DBCC SHRINKDATABASE ([TMS-ManagementData], 10); GO Do you think that will work? I checked my DB log by using DBCC SQLPERF (LOGSPACE) it come up with 99887.43 (Log Size MB) / 0.024 (Log Space Used %) So I am pretty sure I can cut down nearly 100GB of unused space. Any suggestion? Thanks in advanced.
sql-server-2008backupscriptbackup-compression
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Kev Riley avatar image
Kev Riley answered
The size of the backup doesn't necessarily tally with the size of a database. You can have a database of 100TB, but if you only have 1MB of data in it, then the backup will be approx 1MB. When I originally moved my databases from internal to an external data centre, it was quicker to burn the backups to DVD, drive to the data centre and copy them directly onto the server!
10 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image Leo commented ·
Kev - do you applied the log ship after you restored the full back up straight away?
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Ah, to only have a DVD's-worth of data... ;-)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@Thomas : was quite fortunate on that part!
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@Leo : this wasn't a log-shipping db
0 Likes 0 ·
Leo avatar image Leo commented ·
@Kev : I can see your point but Co-Lo Site is about 100miles far from my office so that copy and restore from DVD is not practical. Do you drive every day to Data Center and restore from DVD? I don't think you do..do you?
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
How far apart are these data centres? As the old saw says, "never underestimate the bandwidth of a stationwagon loaded with mag tape driving at 50mph..." I'm looking at doing just that, albeit as a belt-and-braces backup plan...
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
Though it only happens once in a blue moon (major upgrades or something in this effect), we still have occasions when we have to send the dump by mail to the shore, often in the different country, and then hire a chopper to get it to the final destination. Even this beats the option of attempting to transfer the data over network when the latter is slow or worse still not available, which happens when the final location is moving :)
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered
I would try other compression methods. RedGate's [SQLBackup][1] will compress files better than zip/rar in my experience. It took 30m off my transfer time using the top level compression - we had a 2MB line over which I had to set up mirroring. Try zipping the compressed file, SQLBackup,sitting on it, anything to make it as small as possible. Then test it. Then consider driving it to the other site... [1]: http://www.red-gate.com/products/dba/sql-backup/
8 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
yup, it wont be a lot but if you save the time it takes to ship 1GB then its all to the good . . . Purely advocating testing everything. It may turn out these tests make the core of a case for a bigger line, or a 2nd one ...
2 Likes 2 ·
WilliamD avatar image WilliamD commented ·
Leo - remember, changing the recovery mode will require a full backup afterwards to ensure consistancy. I would suggest changing to full recovery, making the initial backup for log shipping and then doing the tran log backups after that. Is it possible for you to drive the full backup up to the other location to reduce the bandwidth headache?
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
He says 30Gb after native compression from sql server. SQLBackup is good, but I don't think he'll see too much of a difference, maybe 1-2GB less. Still much too much for a 2MB connection though.
0 Likes 0 ·
Leo avatar image Leo commented ·
John & Will, I tried using RedGate Backup As well. Compress files are not much difference around 2GB less then normal backup. Shall I transfer the backup up file across to Co-Lo site any way? It might take a day to transfer if I am lucky. Then I will change my Database to Full Recovery Model and get the log backup every 4 hours or something and set the Log Shipping. But I do Full Backup every night, I don't think that will effect the Log Seq Id, do they?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Leo, here is a great link for estimating times to transfer data http://web.forret.com/tools/bandwidth.asp?speed=3.5&unit=Mbps - its showing 37GB/day on 3.5Mbps.

Is there any chance of having the channel bandwidth increased over night/out of hours?

Note, that 2GB saving equates to over an hour saving in transfer time according to their estimate ...
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.