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 |
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
 Posted - 2006-05-03 : 13:10:10
|
| I'm in charge of a remote database, roughly 11gb in size, that requires a backup to be offsite - in case of disaster.I'd like to be able to offer a few options to taking the .bak file offsite that DON'T involve trying to shove 11Gb across a WAN!!!Cheers,Jaybee. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-05-03 : 13:47:54
|
| If you don't want to transport this data over the WAN, there's only one solution that I'm aware of. You'll need some sort of portable drive or disk array (Lacie makes a portable RAID 5 solution), a very secure case for said drive (pelican I think would be a good option), and your local FedEx / UPS guy. Depending on how often you need to do this, get three setups like this. Send one off, then at the next interval, send the next drive, then when it is time for the third, send the first drive back and send the third drive away. Make sense?Another option would be log shipping or replication. This would allow you to "stream" the data to the remote site without needing to transport all 11GB.Yet another option would be to use SQL LiteSpeed backup which should give you a much smaller backup file size. Once the file is smaller, sending it across the WAN may be a bit more feasible.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-04 : 02:24:49
|
| Using Winrar, we compress the SQL backup files appx 6x.And you can split it into convenient 100-200 MB chunks during the compression phase.(we use low compression factor, so that compression is reasonably fast)rockmoose |
|
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-05-04 : 06:28:24
|
quote:
Originally posted by MichaelP
If you don't want to transport this data over the WAN, there's only one solution that I'm aware of. You'll need some sort of portable drive or disk array (Lacie makes a portable RAID 5 solution), a very secure case for said drive (pelican I think would be a good option), and your local FedEx / UPS guy. Depending on how often you need to do this, get three setups like this. Send one off, then at the next interval, send the next drive, then when it is time for the third, send the first drive back and send the third drive away. Make sense?
Perfectly! quote:
Another option would be log shipping or replication. This would allow you to "stream" the data to the remote site without needing to transport all 11GB.
I THINK we have that now...we've got 2 local databases of the same name, structure, and similar sizes, but on different servers, on this site as we do on the remote site.My question now (and this should probably be in the 'Replication' section, if any!) is how do I determine IF it is actually replicating, and how often, given that the copy on subscribing server is only 7.5Gb? Sorry if it's an obvious question, been a long time since I studied Replication!!Thanks,Jaybee. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-04 : 14:04:22
|
| Define: Disaster.ie Server Room Loses all Power. Ok, where are you going to load the data you have offsite now? ie SQL Server power supply/cpu goes out. Ok, where are you going to load the data you have offsite now?ie Controller Card fails. Ok, where are you going to load the data you have offsite now?ie Hard drive fails. Ok, with RAID you are covered on that and don't need it offsite.A disaster recovery plan has to have a "recovery" piece as part of the plan as well. How you save your data is only half of the equation. What you will need to do to restore it is the other half, and likely the more important 90% half should a disaster occur. Option 1:If the data is the only issue and nobody cares how long it takes to get the data reloaded or where it will go, you can consider a product called Double Take which will MIRROR your database files and can be done across a WAN. It takes awhile initially to get it started, but then it simply mirrors all changes so your data load won't be massive at 1 time. You do need a decent sized pipe to keep up if you have an active server. It's basically like a Mirror RAID solution, it's just that the other disk being mirrored to is offsite. You can either have a SQL Server at the other end that you are mirroring to that is shutdown but could be fired up with your data, or just a dumb server with a hard drive if all you care about is the data.Option 2:If you are thinking that you want to have a server available to recover quickly you can also consider LOG SHIPPING to the second server. Again it will ship in time increments that will enable you to lessen the load. Then if something happens you simply bring up the second server and it is ready to go with your data. Option 3:As you've indicated Replication is obviously an answer. It wasn't for us as we have tables with more than 255 columns and that blows Replication as an option. Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
|
|
|
|
Best way to backup offsite?