DB in Recovery Pending

Question

Post reply

DB in Recovery Pending

SQL Bandit

SSCommitted

Points: 1757
More actions
April 13, 2018 at 3:01 am

#337158

Hi Guys/Gals
I thought I'd just come straight to the experts. I have a important DB in a Recovery Pending state, but I have a fullback from before this undiagnosed issue took place.
Can I just simply attempt to restore the DB from backup through SSMS?
Ta
April 13, 2018 at 7:06 am

This was removed by the editor as SPAM
June 27, 2018 at 10:52 pm

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

Yes you can but you'll lose any data since the full backup was taken unless you also have the log backups.

Do you know what the problem with your database is and why it won't come online?

Thanks

SQL Bandit SSCommitted Points: 1757 More actions · Answer 2

Should I try the following and see the outcome ?

ALTER DATABASE 'DATBASE NAME' SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE 'DATBASE NAME' SET ONLINE WITH ROLLBACK IMMEDIATE

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

Is there enough space on the disk for your database to come online? Was the database shutdown? Have you tried to move the log file recently?

You need to find out why your database won't come online.

Thanks

SQL Bandit SSCommitted Points: 1757 More actions · Answer 4

I Did find this...

I deleting a Certificate yesterday. Basically yesterday I disable a mirror and tried clearing out all previous setup I'd done for TDE and I wanted to run though the process of Setting up TDE on mirror again. I wanted to do another practice run. But I didn't think I done anything enough to break the DB. H has anough 56GB free and the .ndf exists

04/13/2018 09:45:17,spid15s,Unknown,A file activation error occurred. The physical file name 'H:\Databases\captaincaveman.ndf' may be incorrect. Diagnose and correct additional errors<c/> and retry the operation.
04/13/2018 09:45:17,spid15s,Unknown,Error: 5105<c/> Severity: 16<c/> State: 1.
04/13/2018 09:45:17,spid15s,Unknown,Cannot find server certificate with thumbprint '0xEC17A18EAC8462765A63D08CF606E7BACA542E74'.
04/13/2018 09:45:17,spid15s,Unknown,Error: 33111<c/> Severity: 16<c/> State: 3.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 5

Right so it looks as though it won't come online because the certificate was deleted. What steps did you take yesterday to remove the encryption?

Thanks

SQL Bandit SSCommitted Points: 1757 More actions · Answer 6

Hi Northern Soul.

USE master;
GO
-- drop certificate
DROP CERTIFICATE captaincaveman

I have the original Certificate and password.

I take it there is some restore command I need to run ?

SQL Bandit SSCommitted Points: 1757 More actions · Answer 7

or alternatively I want to remove TDE completely so I can have another try at configuring it. Could I remove what ever sql server is expecting.

My DB backup won't be enough will it because the certificate is in Master? I'm not sure. I'm new to setting up TDE. That's why I wanted to perform a few run-throughs

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 8

It's good you still have the certificate. There is a guide to removing TDE here:

http://www.sqlservercentral.com/articles/Security/76141/

Thanks

SQL Bandit SSCommitted Points: 1757 More actions · Answer 9

Thank you so much !!!!!!!!!!!!!!!!!!!

I ran the create certificate command. Restarted sql server and the DB recovered and went back online. Northern Soul know that if you do nothing else today that you really helped someone out today. Thank you so much.

create certificate captaincavemaTDE
from file = N'c:\captaincavemaTDE.cer'
with private key
( file = N'c:\captaincavemaTDE.pvk'
, decryption by password = N'UngaBunga'
);

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 10

NorthernSoul

SSCertifiable

Points: 6869

April 13, 2018 at 4:34 am

#1986728

You're welcome.

Techno-Umesh Grasshopper Points: 18 More actions · Answer 11

Follow the given command below.

ALTER DATABASE [DBName] SET EMERGENCY;

GO

ALTER DATABASE [DBName] set single_user

GO

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

GO

ALTER DATABASE [DBName] set multi_user

GO

I believe it will fix your error

This reply was modified 1 year, 11 months ago by Techno-Umesh.