I am developing a transactional replication plan. I have one table
that most of the time I do not want to replicate deletions. There is
an exception to this rule. If the status_id of another table is false,
then the deletion should be replicated.
I have been having difficulty trying to put this condition in place. I
have tried modifying the sp_MSdel_dboTable stored procedure. I have
removed the if @.@.rowcount check and replication errors with the
messages:
-- Error executing a batch of commands. Retrying individual commands.
Eventually, it will fail with the message: if @.@.trancount > 0 rollback
tran
Any ideas on how to get around this issue?
Publisher: SQL 2000
Distributor: On a separate server
Subscriber: SQL 2005
Thanks for any help I can get.
You need to replicate an indexed view as a table on the subscriber. The
indexed view would only "show" rows which match the status_id in the other
table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emreeves" <em_reeves@.lycos.com> wrote in message
news:1143754411.192208.291820@.i40g2000cwc.googlegr oups.com...
>I am developing a transactional replication plan. I have one table
> that most of the time I do not want to replicate deletions. There is
> an exception to this rule. If the status_id of another table is false,
> then the deletion should be replicated.
> I have been having difficulty trying to put this condition in place. I
> have tried modifying the sp_MSdel_dboTable stored procedure. I have
> removed the if @.@.rowcount check and replication errors with the
> messages:
> -- Error executing a batch of commands. Retrying individual commands.
> Eventually, it will fail with the message: if @.@.trancount > 0 rollback
> tran
> Any ideas on how to get around this issue?
>
> Publisher: SQL 2000
> Distributor: On a separate server
> Subscriber: SQL 2005
> Thanks for any help I can get.
>
|||Thanks! This sounds better than the next approach I was going to
try... creating a 1st Insert/Update trans replication, and a 2nd Delete
only with row filtering.
BTW - Thanks very much for your replication book. It has helped loads
this past few weeks.
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
condition with group by
Data:
PROJ PLAN TOTTIME UNIT
A P1 10 DAY
A P2 10 HOUR
A P3 1 MONTH
WHEN I'M DOING GROUP BY ON PROJ
AND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTH
I WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.
10 DAY=10 DAYS
10 HOUR=1.25 DAYS
1 MONTH=20 DAYS
THE RESULT SHOULD BE LIKE THIS:
PROJ PLAN TOTTIME (IN DAYS)
----------
A ALL 31.25
----------
THANKS IN ADV.
T.S.NEGI
tilak.negi@.mind-infotech.comtilak.negi@.mind-infotech.com (T.S.Negi) wrote in message news:<a1930058.0401052155.53e0e35a@.posting.google.com>...
> Data:
> PROJ PLAN TOTTIME UNIT
> A P1 10 DAY
> A P2 10 HOUR
> A P3 1 MONTH
>
> WHEN I'M DOING GROUP BY ON PROJ
> AND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTH
> I WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.
>
> 10 DAY=10 DAYS
> 10 HOUR=1.25 DAYS
> 1 MONTH=20 DAYS
> THE RESULT SHOULD BE LIKE THIS:
> PROJ PLAN TOTTIME (IN DAYS)
> ----------
> A ALL 31.25
> ----------
>
>
> THANKS IN ADV.
> T.S.NEGI
> tilak.negi@.mind-infotech.com
Here is one way to do the summing using a CASE statement
SELECT
SUM(CASE UNIT
WHEN 'DAY' THEN TOTTIME
WHEN 'HOUR' THEN TOTTIME * 0.125
WHEN 'MONTH' THEN TOTTIME * 20
END)
FROM
PLANSAMPLE
WHERE
<CONDITION>
GROUP BY
<Grouping>
Tuesday, February 14, 2012
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?
>> >> >
>> >>
>> >>
>> >>
>>
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?
>> >> >
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)