Suspect production database

Question

Post reply

Suspect production database

coolchaitu

SSChampion

Points: 10334
More actions
September 11, 2017 at 11:56 pm

#402480

Good Morning Experts,
We have a production database that has gone into suspect state. What are the step-by-step process to fix it without any data loss. Thanks in advance

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

First of all: keep calm
- What's registered in the SQLServer errorlog file ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 2

Do you have good backups including transaction logs?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Restore from backups (full + log should get you back up with no data loss)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 4

GilaMonster - Tuesday, September 12, 2017 1:17 AM
Restore from backups (full + log should get you back up with no data loss)

Hi Gail,
I am not able to take tail log backup. So , there will be data loss, correct?

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Potentially. If you can't take a tail log backup, any changes not in data files will be lost.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

coolchaitu - Tuesday, September 12, 2017 1:21 AM
GilaMonster - Tuesday, September 12, 2017 1:17 AM
Restore from backups (full + log should get you back up with no data loss)
Hi Gail,
I am not able to take tail log backup. So , there will be data loss, correct?

Why not. What's the problem there?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

coolchaitu SSChampion Points: 10334 More actions · Answer 7

Jeff Moden - Tuesday, September 12, 2017 8:47 AM
coolchaitu - Tuesday, September 12, 2017 1:21 AM
GilaMonster - Tuesday, September 12, 2017 1:17 AM
Restore from backups (full + log should get you back up with no data loss)
Hi Gail,
I am not able to take tail log backup. So , there will be data loss, correct?
Why not. What's the problem there?

Hi Jeff,
Can we take tail log backup when the database is in suspect state?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Have you tried?

Or tried a search? https://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

coolchaitu - Tuesday, September 12, 2017 8:56 AM
Jeff Moden - Tuesday, September 12, 2017 8:47 AM
coolchaitu - Tuesday, September 12, 2017 1:21 AM
GilaMonster - Tuesday, September 12, 2017 1:17 AM
Restore from backups (full + log should get you back up with no data loss)
Hi Gail,
I am not able to take tail log backup. So , there will be data loss, correct?
Why not. What's the problem there?
Hi Jeff,
Can we take tail log backup when the database is in suspect state?

A really old post from Gail Shaw appears to suggest that you can (I've not had and am not looking forward to the opportunity to test it 😀 ). I hope she shows up on this thread to confirm. The thread is 7 years old.
https://www.sqlservercentral.com/Forums/1027027/Tail-log-backup-is-failing

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Heh... I posted while Gail was posting. Great minds think alike.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

coolchaitu SSChampion Points: 10334 More actions · Answer 11

GilaMonster - Tuesday, September 12, 2017 9:15 AM
Have you tried?
Or tried a search? https://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

I read the article you posted. But, I am unable to come to a conclusion. Could you please confirm we can take tail log backup when the database is in suspect state

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 12

We can't confirm anything. We don't have your server.

If you read the link, Case 2 from Paul is likely what you need to do, if you cannot follow Case 1. You'll have to determine if those work for you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

coolchaitu - Tuesday, September 12, 2017 10:00 AM
I read the article you posted. But, I am unable to come to a conclusion. Could you please confirm we can take tail log backup when the database is in suspect state

Did you try?

What was the result? Did it succeed, or throw an error? If error, which one?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 14

GilaMonster - Tuesday, September 12, 2017 1:38 PM
coolchaitu - Tuesday, September 12, 2017 10:00 AM
I read the article you posted. But, I am unable to come to a conclusion. Could you please confirm we can take tail log backup when the database is in suspect state
Did you try?
What was the result? Did it succeed, or throw an error? If error, which one?

I dont have an opportunity to try. There are no test databases and I cant even create one, not allowed. So, could you please help me in understanding if we can take tail log backup when the database is in suspect state.