Concern about backup plan

I inherited this backup strategy, and I do not have authority to change it. I
am concerned about how I will handle this if there is a crash and I need to
do a point in time restore.
Full backups are done 6 days a week, Sunday through Friday.
The transaction log is backed up once a week, on Sunday.
In the event that the system crashed on Wednesday, I have the previous
night's backup, but what about transactions during the day. If I run a
transaction log backup at the point of the crash, it would give me all
transactions since Sunday. Is there a way to specify only transactions for
the current day?Bev
>Is there a way to specify only transactions for
> the current day?
No, as you say , you need to do a point in time restore
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it.
>I
> am concerned about how I will handle this if there is a crash and I need
> to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions
> for
> the current day?
>|||Given the backup plan that I have been saddled with, what would you recommend
I do in such a case?
"Uri Dimant" wrote:
> Bev
> >Is there a way to specify only transactions for
> > the current day?
> No, as you say , you need to do a point in time restore
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
> >I inherited this backup strategy, and I do not have authority to change it.
> >I
> > am concerned about how I will handle this if there is a crash and I need
> > to
> > do a point in time restore.
> > Full backups are done 6 days a week, Sunday through Friday.
> > The transaction log is backed up once a week, on Sunday.
> > In the event that the system crashed on Wednesday, I have the previous
> > night's backup, but what about transactions during the day. If I run a
> > transaction log backup at the point of the crash, it would give me all
> > transactions since Sunday. Is there a way to specify only transactions
> > for
> > the current day?
> >
>
>|||Bev
If you are unable to change it, i'd suggest to do some testing with what you
are having right now
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
> Given the backup plan that I have been saddled with, what would you
> recommend
> I do in such a case?
> "Uri Dimant" wrote:
>> Bev
>> >Is there a way to specify only transactions for
>> > the current day?
>> No, as you say , you need to do a point in time restore
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>> >I inherited this backup strategy, and I do not have authority to change
>> >it.
>> >I
>> > am concerned about how I will handle this if there is a crash and I
>> > need
>> > to
>> > do a point in time restore.
>> > Full backups are done 6 days a week, Sunday through Friday.
>> > The transaction log is backed up once a week, on Sunday.
>> > In the event that the system crashed on Wednesday, I have the previous
>> > night's backup, but what about transactions during the day. If I run a
>> > transaction log backup at the point of the crash, it would give me all
>> > transactions since Sunday. Is there a way to specify only transactions
>> > for
>> > the current day?
>> >
>>|||I should admit that this is the weirdest backup strategy I've ever seen.
First, I want you to take a look at the following strategy which is a
classic:
- Take a Full backup once a week
- Take Differential backups once a day
- Take Transaction Log backups every 30 mins \ 10 mins everyday.
This plan suits most of the environments. Of course every environment is
particular and needs special care. However, Transaction Log backup's aim is
make you able to restore your database to the point of failure or restoring
your database to a specific time.
After taking a full backup, your transaction backup would be useless because
you would not need it. For example in your situation you say that you take
tran backup every Sunday. On Monday you'll be taking a full backup and when
your database crashes on Tuesday, then you'd use only your full backup to
restore your database because your full backup would include the records
that your tran backup has.
According to your current plan, if you take your tran backup at Sunday
midnight and taking your full backup Monday midnight, then your tran backup
would be useful only during this period. If your database crashes on Monday
afternoon, then you'd restore your full backup that you took on Friday and
then you'd restore your tran backup that you took on Sunday. However, your
database would be restored until Sunday midnight which is the moment that
you took the tran backup.
In the short, probably only your full backups would be useful for you in
case of a database crash. I highly recommend you to review your backup
strategy immediately.
You may want to take a look at the following documents from BOL to learn
more about backup in SQL Server. I believe they'll be useful for you.
Backup Overview
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Working with Transaction Log Backups
http://msdn2.microsoft.com/en-us/library/ms190440.aspx
--
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it.
>I
> am concerned about how I will handle this if there is a crash and I need
> to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions
> for
> the current day?
>|||Hi Bev,
You need tp explain that to be able to restore to a failure point, you must
have a full backup AND an unbroken chain of transaction log backups,
preferably off the machine being backed up (tape, NAS, or some other copy).
It's a business decision that they have made, you need to explain what they
actually decided.
FWIW, the maximum time I will allow to go by without a transaction log
backup is 60 minutes, the minimum is 1 minute (and in some narrow cases, I'm
thinking about doing it more often, I haven't, but I'm thinking about it).
Jay
(PS. this is an example of what I call the BS you have to deal with in IT.)
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
> Given the backup plan that I have been saddled with, what would you
> recommend
> I do in such a case?
> "Uri Dimant" wrote:
>> Bev
>> >Is there a way to specify only transactions for
>> > the current day?
>> No, as you say , you need to do a point in time restore
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>> >I inherited this backup strategy, and I do not have authority to change
>> >it.
>> >I
>> > am concerned about how I will handle this if there is a crash and I
>> > need
>> > to
>> > do a point in time restore.
>> > Full backups are done 6 days a week, Sunday through Friday.
>> > The transaction log is backed up once a week, on Sunday.
>> > In the event that the system crashed on Wednesday, I have the previous
>> > night's backup, but what about transactions during the day. If I run a
>> > transaction log backup at the point of the crash, it would give me all
>> > transactions since Sunday. Is there a way to specify only transactions
>> > for
>> > the current day?
>> >
>>|||Question: Once you have crashed, CAN you run a transaction log backup? I
would have thought no.
If you can't then the tlog run on Sunday is useless as soon as the next full
backup is done.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it.
>I
> am concerned about how I will handle this if there is a crash and I need
> to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions
> for
> the current day?
>|||> Question: Once you have crashed, CAN you run a transaction log backup? I would have thought no.
It would depend on what the crash mean.
If the SQL Server instance is running but the database in inaccessible (say for instance that the
mdf file is lost), then you can do a log backup using the NO_TRUNCATE option (assuming the log
file(s) are there, of course).
If the SQL Server instance cannot be started but the ldf file(s) are available, you can copy them to
another machine. Create a "dummy" database there. Stop that instance. Delete the database files for
the newly created database. "Slide in" the ldf file from the crashed database where the ldf file
were for the newly created database. Start the instance. And then backup the log using NO_TRUNCATE.
If the whole machine is gone, well, say no more... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23OmsEskFIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Question: Once you have crashed, CAN you run a transaction log backup? I would have thought no.
> If you can't then the tlog run on Sunday is useless as soon as the next full backup is done.
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>>I inherited this backup strategy, and I do not have authority to change it. I
>> am concerned about how I will handle this if there is a crash and I need to
>> do a point in time restore.
>> Full backups are done 6 days a week, Sunday through Friday.
>> The transaction log is backed up once a week, on Sunday.
>> In the event that the system crashed on Wednesday, I have the previous
>> night's backup, but what about transactions during the day. If I run a
>> transaction log backup at the point of the crash, it would give me all
>> transactions since Sunday. Is there a way to specify only transactions for
>> the current day?
>|||> Is there a way to specify only transactions for
> the current day?
Only if you first can produce a log backup (for the crashed database). The restore would then be:
Restore the most recent database backup done before your Sunday log backup.
Restore the Sunday log backup.
Restore the log backup you produce after the crash.
I agree with others that this is among the weirdest backup strategies I've heard of. I hope you can
communicate this with proper persons so you can change it to a reasonable backup strategy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>I inherited this backup strategy, and I do not have authority to change it. I
> am concerned about how I will handle this if there is a crash and I need to
> do a point in time restore.
> Full backups are done 6 days a week, Sunday through Friday.
> The transaction log is backed up once a week, on Sunday.
> In the event that the system crashed on Wednesday, I have the previous
> night's backup, but what about transactions during the day. If I run a
> transaction log backup at the point of the crash, it would give me all
> transactions since Sunday. Is there a way to specify only transactions for
> the current day?
>|||I am happy to say that I finally got approval for the new backup strategy,
which I first proposed three months ago and which is very close to what Ekrem
suggested.
I suspect that much of the heel-dragging stemmed from the fact that I am
relative novice while the person who recommended the current plan is a guru.
But then he's not around anymore to clean up the mess if we do crash.
"Jay" wrote:
> Hi Bev,
> You need tp explain that to be able to restore to a failure point, you must
> have a full backup AND an unbroken chain of transaction log backups,
> preferably off the machine being backed up (tape, NAS, or some other copy).
> It's a business decision that they have made, you need to explain what they
> actually decided.
> FWIW, the maximum time I will allow to go by without a transaction log
> backup is 60 minutes, the minimum is 1 minute (and in some narrow cases, I'm
> thinking about doing it more often, I haven't, but I'm thinking about it).
> Jay
> (PS. this is an example of what I call the BS you have to deal with in IT.)
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
> > Given the backup plan that I have been saddled with, what would you
> > recommend
> > I do in such a case?
> >
> > "Uri Dimant" wrote:
> >
> >> Bev
> >> >Is there a way to specify only transactions for
> >> > the current day?
> >> No, as you say , you need to do a point in time restore
> >>
> >>
> >> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> >> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
> >> >I inherited this backup strategy, and I do not have authority to change
> >> >it.
> >> >I
> >> > am concerned about how I will handle this if there is a crash and I
> >> > need
> >> > to
> >> > do a point in time restore.
> >> > Full backups are done 6 days a week, Sunday through Friday.
> >> > The transaction log is backed up once a week, on Sunday.
> >> > In the event that the system crashed on Wednesday, I have the previous
> >> > night's backup, but what about transactions during the day. If I run a
> >> > transaction log backup at the point of the crash, it would give me all
> >> > transactions since Sunday. Is there a way to specify only transactions
> >> > for
> >> > the current day?
> >> >
> >>
> >>
> >>
>
>|||> I suspect that much of the heel-dragging stemmed from the fact that I am
> relative novice while the person who recommended the current plan is a guru.
I think you meant "...who recommended the current plan is *considered* a guru". Big difference. ;-)
Glad you got the approval. Good luck!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:CE00E476-0C1A-4E2B-88C7-CF78C31607DF@.microsoft.com...
>I am happy to say that I finally got approval for the new backup strategy,
> which I first proposed three months ago and which is very close to what Ekrem
> suggested.
> I suspect that much of the heel-dragging stemmed from the fact that I am
> relative novice while the person who recommended the current plan is a guru.
> But then he's not around anymore to clean up the mess if we do crash.
> "Jay" wrote:
>> Hi Bev,
>> You need tp explain that to be able to restore to a failure point, you must
>> have a full backup AND an unbroken chain of transaction log backups,
>> preferably off the machine being backed up (tape, NAS, or some other copy).
>> It's a business decision that they have made, you need to explain what they
>> actually decided.
>> FWIW, the maximum time I will allow to go by without a transaction log
>> backup is 60 minutes, the minimum is 1 minute (and in some narrow cases, I'm
>> thinking about doing it more often, I haven't, but I'm thinking about it).
>> Jay
>> (PS. this is an example of what I call the BS you have to deal with in IT.)
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:F11E52A8-E55F-4CB9-BA35-3842F0BA2FAB@.microsoft.com...
>> > Given the backup plan that I have been saddled with, what would you
>> > recommend
>> > I do in such a case?
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Bev
>> >> >Is there a way to specify only transactions for
>> >> > the current day?
>> >> No, as you say , you need to do a point in time restore
>> >>
>> >>
>> >> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> >> news:FC6F122F-9FA0-486A-80AB-FB15D936EB69@.microsoft.com...
>> >> >I inherited this backup strategy, and I do not have authority to change
>> >> >it.
>> >> >I
>> >> > am concerned about how I will handle this if there is a crash and I
>> >> > need
>> >> > to
>> >> > do a point in time restore.
>> >> > Full backups are done 6 days a week, Sunday through Friday.
>> >> > The transaction log is backed up once a week, on Sunday.
>> >> > In the event that the system crashed on Wednesday, I have the previous
>> >> > night's backup, but what about transactions during the day. If I run a
>> >> > transaction log backup at the point of the crash, it would give me all
>> >> > transactions since Sunday. Is there a way to specify only transactions
>> >> > for
>> >> > the current day?
>> >> >
>> >>
>> >>
>> >>
>>