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

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

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

Get the tables.

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.

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

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]