Backup strategy

Hello,
I manage a SQL Server that requires frequent backups to insure not
much data is lost in the event of a failure. Here is how backups are
performed currently:
Sunday, 1:00am
== * Full database backup of ALL databases
* Transaction log backup WITH INIT
Mon-Sat, 1:00am
== * Differential database backup of ALL databases
Daily, Hourly
== * Transaction log backup
I am currently having 2 problems. The first problem is that sometimes
on Sunday mornings when the job attempts to backup the transactions
logs WITH INIT the job will fail because the Daily, Hourly process is
also backing up at the same time. The second problem is that the
transaction log backups seem to grow larger and larger and take longer
and longer to complete. Is it really necessary to store the entire
week of transaction log backups when I'll always have a differential I
can apply for each day? The only purpose of the transaction log
backups is to insure we can restore to the latest point in time before
a failure occurs. There isn't a need to revert back to more than the
previous day's transaction log backup.
Is what I'm doing the best way? How can this be improved upon?
Thank you in advance for your time and efforts.
ShawnHi,
> Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
Based on these requirements you do not need an entire week of transaction
log backups. You just need the transaction log backups taken after your last
differential backup.
But most important, test your strategy restoring your database to another
database server using this combination of full, differential and transaction
log backups.
Hope this helps,
Ben Nevarez
"ITistic" wrote:
> Hello,
> I manage a SQL Server that requires frequent backups to insure not
> much data is lost in the event of a failure. Here is how backups are
> performed currently:
> Sunday, 1:00am
> ==> * Full database backup of ALL databases
> * Transaction log backup WITH INIT
> Mon-Sat, 1:00am
> ==> * Differential database backup of ALL databases
> Daily, Hourly
> ==> * Transaction log backup
> I am currently having 2 problems. The first problem is that sometimes
> on Sunday mornings when the job attempts to backup the transactions
> logs WITH INIT the job will fail because the Daily, Hourly process is
> also backing up at the same time. The second problem is that the
> transaction log backups seem to grow larger and larger and take longer
> and longer to complete. Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
> Is what I'm doing the best way? How can this be improved upon?
> Thank you in advance for your time and efforts.
> Shawn
>