Backup question

We are using SQLServer 2005.
I am looking for a good advice to do our database backup.
For our database, I have created a full backup that is done every day at 12
am. Then daily every 1 hour I backup the Transaction log. Then I have a
backup file cleanup (maintenance cleanup task) to delete files that are
older than 3 days. Is this backup plan good enough ? Shall I also di a
differential backup ?
Thank you.fniles wrote:
> We are using SQLServer 2005.
> I am looking for a good advice to do our database backup.
> For our database, I have created a full backup that is done every day at 12
> am. Then daily every 1 hour I backup the Transaction log. Then I have a
> backup file cleanup (maintenance cleanup task) to delete files that are
> older than 3 days. Is this backup plan good enough ? Shall I also di a
> differential backup ?
> Thank you.
Good enough for what?
You need to answer your own question by determining your Recovery
Point Objective and Recovery Time Objective. That is, the latest point
to which your business must be able to recover its data (RPO) and the
downtime you can afford in which to do it (RTO). These are business
considerations, not technical ones. Start talking to your business
stakeholders.
--
David Portas|||I guess my question is, if I already do Full backup once a day, and
transaction log backup every hour, do I still need to do "Differential"
backup ?
What is the difference between backing up (full backup and transaction log)
and (the differential backup) ?
Say I do a full backup at 12 am and has transaction log every hour after
that.
Say it is 9 am now, and I need to restore my data, do I need to restore the
full backup and restore every transaction log from 12 am until 9 am ?
If I do differential backup every 4 hours, is my differential backup at 8 am
contains only those data from 4 am to 8 am, or does it contain data from 12
am to 8 am ?
Thank you.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||Hi fniles
A differential backup contains all the changes since the last full database
backup. So your 8 AM differential would contain all the changes since
midnight.
This can make restore much faster. After restoring the full database, you
then only need to restore the most recent differential and any log backups
from after that differential.
So in your case, assuming you have already made your 9am log backup, you
could either restore 9 log backups, or 1 differential and 1 log backup.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"fniles" <fniles@.pfmail.com> wrote in message
news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I guess my question is, if I already do Full backup once a day, and
>transaction log backup every hour, do I still need to do "Differential"
>backup ?
> What is the difference between backing up (full backup and transaction
> log) and (the differential backup) ?
> Say I do a full backup at 12 am and has transaction log every hour after
> that.
> Say it is 9 am now, and I need to restore my data, do I need to restore
> the full backup and restore every transaction log from 12 am until 9 am ?
> If I do differential backup every 4 hours, is my differential backup at 8
> am contains only those data from 4 am to 8 am, or does it contain data
> from 12 am to 8 am ?
> Thank you.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>|||Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uc9teo4MIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi fniles
> A differential backup contains all the changes since the last full
> database backup. So your 8 AM differential would contain all the changes
> since midnight.
> This can make restore much faster. After restoring the full database, you
> then only need to restore the most recent differential and any log backups
> from after that differential.
> So in your case, assuming you have already made your 9am log backup, you
> could either restore 9 log backups, or 1 differential and 1 log backup.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>>I guess my question is, if I already do Full backup once a day, and
>>transaction log backup every hour, do I still need to do "Differential"
>>backup ?
>> What is the difference between backing up (full backup and transaction
>> log) and (the differential backup) ?
>> Say I do a full backup at 12 am and has transaction log every hour after
>> that.
>> Say it is 9 am now, and I need to restore my data, do I need to restore
>> the full backup and restore every transaction log from 12 am until 9 am ?
>> If I do differential backup every 4 hours, is my differential backup at 8
>> am contains only those data from 4 am to 8 am, or does it contain data
>> from 12 am to 8 am ?
>> Thank you.
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day
>> at 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>>
>|||I like the way David explains the concept of RPO and RTO as this will define
what backup procedures you will need to do. Even if you understand the
concpets behind FULL, DIFFERENTIAL, FILEGROUP or TRANSACTION LOG backups,
your RPO and RTO will determine what you need to use to fulfill your
requirements
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||Yes, I backup to a drive.
We do have another backup procedure outside the SQL Server Management Studio
that will backup to a tape.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:e0a1l3915upjr0kss3hk87273vrh24tm6t@.4ax.com...
> How do you backup the backups? Do you have an Enterprise back system
> like Tivoli that backs up to a drive not on the SQL server or to tape?
> On Sat, 1 Dec 2007 09:57:56 -0600, "fniles" <fniles@.pfmail.com> wrote:
>>We are using SQLServer 2005.
>>I am looking for a good advice to do our database backup.
>>For our database, I have created a full backup that is done every day at
>>12
>>am. Then daily every 1 hour I backup the Transaction log. Then I have a
>>backup file cleanup (maintenance cleanup task) to delete files that are
>>older than 3 days. Is this backup plan good enough ? Shall I also di a
>>differential backup ?
>>Thank you.|||Regarding Recovery Time Objective (RTO), which backup will take the least
time to restore ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||It depends. A very simplified example:
You have a db backup and then either a diff or a log backup. You wonder whether a diff backup or a
log backup will take longer time.
Say you've done a *lot* of modifications of the same row (pretty extreme, but just as an example).
If you now restore a log backup, then all those modification will be re-done when you restore. If
you restore a diff backup, then only one extent (8 pages) will be restored.
OTOH, say you've modified a lot of data (say 10,000 modifications), each modification spread over
different extents. Diff restore will restore 10,000 extents. Log restore will re-do those 10,000
modifications.
In general, it isn't one or the other. I tend to have db and log backup as a basic setting. I
complement with diff backup when I realize that restoring all log backups since last db backup takes
too long. So, I restore db backup, last diff backup and all subsequent log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:%230L7QecNIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Regarding Recovery Time Objective (RTO), which backup will take the least time to restore ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>|||I had a client hit by this very issue. They had very bad (i.e. bloated,
non-normalized) data structures as well as ugly ADO code, among other
issues. Differential backups were huge compared to transaction log backups
on average.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B927BB46-3329-4E78-A75D-CDE8ED0D8091@.microsoft.com...
> It depends. A very simplified example:
> You have a db backup and then either a diff or a log backup. You wonder
> whether a diff backup or a log backup will take longer time.
> Say you've done a *lot* of modifications of the same row (pretty extreme,
> but just as an example). If you now restore a log backup, then all those
> modification will be re-done when you restore. If you restore a diff
> backup, then only one extent (8 pages) will be restored.
> OTOH, say you've modified a lot of data (say 10,000 modifications), each
> modification spread over different extents. Diff restore will restore
> 10,000 extents. Log restore will re-do those 10,000 modifications.
> In general, it isn't one or the other. I tend to have db and log backup as
> a basic setting. I complement with diff backup when I realize that
> restoring all log backups since last db backup takes too long. So, I
> restore db backup, last diff backup and all subsequent log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230L7QecNIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Regarding Recovery Time Objective (RTO), which backup will take the least
>> time to restore ?
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day
>> at 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>>
>