Backup Strategy

Hi,
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
Jerry
You are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>
|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copied
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
spam999free@.rrohio.com
remove 999 in order to email me