Corrupt Database – I need that data now!

My database is in the recovery pending state and I want to get in and extract the data out into a new database, I have NO BACKUPS available so some data (even if it is dodgy) is better than nothing.

So I have the following issue.

 DBCC CHECKDB ('DWHDB')

Msg 945, Level 14, State 2, Line 1 Database ‘DWHDB’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Checking the status gives me recovery pending:

 SELECT name, state_desc FROM sys.databases

pending

I need to get to the data, what good is the application without data? I do not want to use a repair command. If you do then see an older post of mine: https://blobeater.blog/2017/03/14/extreme-situations-require-extreme-commands/

Let’s try switching it online (which fails).

ALTER DATABASE [DWHDB] SET ONLINE;

Could not restart database “DWHDB”. Reverting to the previous status. Msg 5069, Level 16, State 1, Line 11 ALTER DATABASE statement failed.

The error log isn’t very nice to read either – The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Enter emergency mode, when in this mode you can access the database (read-only) assuming you are sysadmin.

ALTER DATABASE [DWHDB] SET EMERGENCY;
GO
SELECT name, state_desc FROM sys.databases

emer

So now is the time to extract as much of the data out as possible and I use BACPACs.

bacpac

Get the tables.

tables101

operation.JPG

I am going to create a new database with the contents of the BACPAC file – don’t forget recovery never ran on the source database so we may have data that is not fit for purpose, but something is better than nothing.

Work your way through the import process.

New1

Here is the copy of the source database in read only mode called DWHDB_emergency.

ssms0

You can access the tables but it’s up to you to deduce whether or not the data is logically correct.

 SELECT * FROM [DWHDB_emergency].[dbo].[DimCustomer]

GetData

Advertisements

3 thoughts on “Corrupt Database – I need that data now!

  1. Pingback: Corrupt Database – I need that data now! - SQL Server Blog - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s