One of the relatively common situations during disaster recovery is a suspect database. The suspect flag is a mode which is set to the database by SQL Server in several cases. When it comes to disaster recovery, mainly databases are marked as suspect in case of a hardware failure, improper shutdown of the DB server, DB files corruption, etc. Strangely enough, it can happen when you restore the entire DB server from a valid snapshot (it happened to me).

In such cases, you will need to bring the database back to online mode. At this point you need to be aware that during the process there may be some data loss. This can occur if there are incomplete transactions which will need to be rolled back. In general,

What you will need for the procedure is the SQL Server Management Studio. I will use the Prime database which I corrupted in the previous post 🙂

The first thing you need to do is to turn off the suspect flag (which does not mean that the DB is fixed). You will need to execute the following command:

EXEC sp_resetstatus ‘Prime’

You will get the following message:

The next step is to set the database into emergency mode, which will make it a read-only with the following command:

ALTER DATABASE Prime SET EMERGENCY

Then, perform a consistency check. The output will display any possible errors.

DBCC checkdb(‘Prime‘)

In the next step we do a rollback of any pending transactions. This step also brings the DB in a single user mode.

ALTER DATABASE Prime SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Finally, we will correct any reported errors. Keep track of the time, since this may be a lengthy process.

DBCC CheckDB (‘Prime‘, REPAIR_ALLOW_DATA_LOSS)

To wrap up the procedure, we will need to enable the multi user access and bring it online.

ALTER DATABASE Prime SET MULTI_USER

If we check the DB status now, it will be shown as online. We can browse through the tables and query data:

select DATABASEPROPERTYEX(‘Prime‘, ‘status’)

Jana Georgievska,

DBA

In the highly unlikely world of DBA testing scenarios, sometimes you will need to corrupt your MS SQL database. One of these scenarios could be that you want to check how long it would take to recover the database during disaster recovery. What will be described here is only for testing purposes and MUST NOT be done on any production system. The entire process was conducted on a local test environment.

There are several procedures which you can use, mainly through updating the status of the database in the system tables, which does not always work. There are cases where the needed options are deprecated in the SQL version, for example, in MS SQL Server 2005, the extended option that would allow running modification queries against the system tables was removed. I ran into this particular case when I tried to make a SUSPECT database on a MS SQL Server 2008 R2. Even though the statement to allow updates went smoothly:

sp_configure “allow updates”, 1

When I tried to change the status of the database named Prime:

UPDATE sysdatabases SET STATUS = 320 WHERE name = ‘Prime’

I received the following error:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

In that particular moment, I did not have the time to troubleshoot this error, and I needed the Prime database marked as SUSPECT.

As it turns out, the easiest way to get a SUSPECT database is to mess up the files. This works regardless of the MS SQL version.

First, restore a database from a good backup. In my Management Studio, I have the Prime database which I intend to place in a SUSPECT status:

You can check the status of the database using the statement:

select databasepropertyex(‘Prime’, ‘status’)

Or see if you can list and query the tables. So my Prime database is now ONLINE, which means that it is alive and well.

Next, shut down the MS SQL services from the Services panel in Windows.

Locate the .ldf file of your database. In my case, this is the Prime_log.ldf file. Open the file for editing (I used Notepad):

And make an adjustment. I added a new row in the file and typed 12345:

Save the file and start the MS SQL services again. Open the Management Studio and execute the statement to check the database status again:

As you can see, the Prime database is now a SUSPECT 😀

If you try to expand the database node, you will get the following error:

Congratulations, you have successfully corrupted your database 😀

Jana Georgievska,

DBA