x

Corruption in a logshipped database

Suppose a primary database(which is logshipped) gets corrupted. Does it follow that the logs shipped to the secondary database also gets corrupted once it gets restored via a job step in a log shipping process? Is there a way to stop the restore job once the database corruption is detected? What other options are available once corruption rears its ugly head in a log shipped environment?

more ▼

asked Nov 09, 2009 at 06:58 AM in Default

optradba gravatar image

optradba
11 3 3 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The secondary database will import logs up until the last usable point. If the corruption is file / server based, then the log import will not work on the secondary database, and that will be close to the 'last known good state'.

However, if your corruption is 'a user decided to delete all the rows by accident' then that will be merrily shipped across to your secondary location.

more ▼

answered Nov 09, 2009 at 08:14 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

In addition to @Matt Whitfield's answer, though, you can mitigate (to some extent) against these issues by putting a delay on the restores into the target server. If you know that your user-deleting-everything corruption has just occurred, then you can (hopefully) stop the log restore, and extract what you need to rebuild the data, and then carry on.

Assuming your users are honest enough to tell you within the log ship/restore timeframe, that is.
more ▼

answered Feb 25, 2011 at 04:11 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

Adding to @ThomasRushton - make sure the copy is not delayed and the logs are off the originating server.
Feb 25, 2011 at 05:21 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x64

asked: Nov 09, 2009 at 06:58 AM

Seen: 2883 times

Last Updated: Nov 09, 2009 at 06:58 AM