June 22, 2009 at 9:44 pm
Hi,
We are using sql server 2005 EE x64 with SP3.We want to take the log backups simultaneously to 2 drives. One drive will be local to the server and another will be in a different server.
Is this posible sql server 2005?? If possible plz tell me how to do that?
thanks
June 22, 2009 at 9:54 pm
You can do stripped backup. Backup data will be written to two backup devices simulateneously.
backup database test
to disk='c:\test1.bak',
disk='c:\test2.bak'
2nd path can be your network path.
During restoration too, you need to provide both backup files simultaneously.
restore database test
from disk='c:\test1.bak',
disk='c:\test2.bak'
with norecovery, replace
June 22, 2009 at 10:44 pm
Do you mean the same backup to 2 locations (having a copy), or striped as in 1/2 on one drive, 1/2 on another?
Either way, running a backup to a remote drive is a great way to get into trouble, especially with striping. The remote backup is likely to fail as any network hiccup will fail it. And then you've lost your backup.
Run backups locally, copy to remote drives.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 23, 2009 at 11:05 pm
Do you mean the same backup to 2 locations (having a copy)
Yes Steve, Can we backup the same database to two different servers at the same time?
June 23, 2009 at 11:11 pm
Not in 2000, and it's a bad idea. Even the mirror'ed backups in 2005 have issues. If one fails (one drive, network, etc.) both fail.
Don't do this. Back up locally, then copy remotely.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 23, 2009 at 11:14 pm
Can we backup the same database to two different servers at the same time?quote]
you can achieve so by using mirror to clause of backup statement
backup database test
to disk='c:\test1.bak'
Mirror to disk='c:\test\test2.bak'
with format
Edit:- Qutoed the wrong post!!:w00t:
June 25, 2009 at 1:00 am
If you are uncertain of your network connection and want to ensure the backup doesn't fail because of it, use a sql agent job where step 1 backs up to a drive on the sql server and step 2 is an operating system command step that copies that backup file across the network to another location/site etc.
This way the backup is done and if the step fails, it can re-try or the file can be copied manually.
June 25, 2009 at 3:02 am
Personally, I would use P Jone's suggestion.
As he says, this way you guarantee that taking a backup is not affected by network issues. The copy step could get affected by network issues but you can set up the job step to retry automatically a set number of times.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 25, 2009 at 7:24 pm
If one of the mirror backups fails, both fail.
Go with P Jones suggestion.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 26, 2009 at 7:20 am
If you absolutely must backup remotely, use a third party backup utility (we use Litespeed from Quest, but SQL Backup Pro from Redgate is also good). The backup is compressed and faster. Depending on your network settings, you may benefit from it.
Just remember to have whatever job you eventually create to backup regularly email the DBA on call should it fail. A failed backup is almost as severe as a downed server and corrupt database, and is not something to be delayed until the next day.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
June 26, 2009 at 8:29 am
I agree with Gaby. The day your backup fails is likely the same day your disk array will fail. Don't play around. Make sure these work locally and reliably.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 26, 2009 at 9:19 am
Steve Jones - Editor (6/26/2009)
I agree with Gaby. The day your backup fails is likely the same day your disk array will fail. Don't play around. Make sure these work locally and reliably.
And the corrollary to this, when backing up locally, DO NOT backup to the same physical disk as your SQL data files. There should be absolutely no compromise on this in a production environment. If your backup drive is toast, you can always backup to a new drive (or remotely if not possible locally) and vice versa, if your database is corrupt, those full and transaction log backups sitting safely on another physical location will save your career.
If your company can manage it, have a way to copy all of the local backups be archived offsite for X months/years as you don't want to keep your previous backups on the drive forever. If a database anomally occurred a few days ago but only caught recently, you can get the archived backups. This is firsthand information and trust me, it's a lifesaver.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply