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 |
|
bobbabuoy
Starting Member
33 Posts |
 Posted - 2006-07-01 : 11:47:08
|
| What is the best way to back up sql server to a remote site so that if we had total data loss locally I would be able to restore and get back up and running as quickly as possible. I have a remote file storage option that I use and I currently store a MSAccess version of the db there. I assume there is a much better solution to this. Can someone "upgrade my knowledge" please?Thanks a ton~ |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-01 : 15:11:30
|
| Without getting into SQL Server Replication, you might do something like this:Setup a SQL Server job to:1. Backup the databases as normal to disk.2. (Optional) compress the backup files with a command line utility, like PKZip, to reduce the size of the files to be copied over the network.2. Use a command line utility program, like ROBOCOPY, to copy the backup files over the network to a remote file share.If you are making transaction log backups, setup another SQL Server job to copy those over the network several times each day. ROBOCOPY is very good tool for this, because it can be used to maintain the local and remote backup directories in-sync.This assumes that you have a network with adequate bandwidth to handle the data tranfer.CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-02 : 07:35:02
|
Log Shipping - which is basically what MVJ is saying. In essence:0) Set your database to Recovery Model = FULL 1a) Take a full backup2b) Restore onto standby server (in STANDBY mode)3a) Set up scheduled task to backup the database TLog frequently (how much can you lose? 10 minutes? 1 minute? Choose something sensible)3b) Copy the TLog backup file to the Standby Server3c) Restore the TLog backup onto the Standby Server4) In a disaster change the Standby Server database from "Standby" to "Recovered" - i.e. it becomes Live.(4) is probably not so relevant to you, but it would be for a live running 24/7 system. I assume you just don't want to lose your data, rather than have a fail-over system, and after a disaster you would record times etc. on paper, and feed them in later on.So you could probably get away with changing step (3b) to copy the TLog backups from the SQL Server machine to A.N.Other machine on your race-side network. If the Server Machine catches fire! the A.N.Other machine will have backups to within 10 minutes, or 1minute, etc. of the failure.Just pursuing that a little further, if your A.N.Other machine had SQL Server running on it you could then immediately restore those TLog files, and have an up-to-date SQL Server database running on that machine pretty quickly. You are going to be pretty distracted from what you normally do whilst that is all going on though!You will then have to change all the Client PC configurations to use the A.N.Other PC address/database, rather than the original SQL Serve box (obviously your application could ahve a "Connect to Server-A" or "Server-B" option) - or you change the name/IP address of A.N.Other so that it takes over the name of the main SQL box.All this you need to carefully rehearse at home - and write up a step-by-step Operating Procedure. On the blue moon occasion when it happens at a race time will be of the essence and you need to be able to work through your steps methodically, and unflustered.And, unfortunately, you need to rehearse the Disaster Recovery often - otherwise something will change such that the old plan no longer works, and on Disaster Day your carefully constructed, but unrehearsed plan, will let you down  And if all that seems like a PITA then perhaps it explains why "5 x 9s" systems (with 99.999% up-time - i.e. 5 minutes a year downtime) are jolly expensive!Kristen |
|
|
|
|
|
|
|
|
SQL Server Remote Back-Up