Search This Blog


Recover a database out from Suspect mode

>> Saturday, February 25, 2012

What is database suspect mode?

Database suspect mode is situation where the database is not available for use and a worst situation for DBA’s to get the business continue by bringing the database online as early as possible..

During this “code red” situation don’t ever panic and be calm, the first step you need to do is to inform your customers about the outage before they come back to you. This step will be a life saver for you as you will not be questioned for the time you will spend to bring the database online.

Then you can refer the SQL-server error logs and find out the root cause of the error, I recommend to use a filter while viewing the log and use "Database name" as the filter criteria. In this way you will only see the logs related to that particular database and then refer to the latest log/s for the root cause.

Why database goes into suspect mode?

Well there are many causes which will move the database to suspect mode, in this article we will discuss some of them.

1)      Some one deleted or misplaced a data/log file when SQL Server was offline and the database can not start because of missing file.
2)      SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically this may happen when SQL-sever is shared with some other tools like antivirus etc..
3)      And the worst one is when the transactions have been corrupted. This is most common in OLTP databases. The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back, SQL server could not complete (commit/rollback) the transaction.
4)      If the data file corrupted due to Hardware or OS level failure.

How to come out of this situation?

Firs you need to check which of your database have gone into suspect mode, for this you can see in the object Explorer or you can query the below scripts.

USE master

After this you need check the sql server error logs for the exact reason why this has happen.

1)      If the reason was due to some deleted or misplaced data or log file the you can simply place the missing file in proper location, the exact location you can get in the sql-server error logs.
2)      If the reason was due to locks on data or log file while the database is coming online, then you can use the process explorer and kill the file handler which has placed the locks on the file. You may require the help of your system admin for this.
3)      If the reason was due to corrupted transactions on abrupt restart of server, then think for a while before doing any step because this it will result in data loss .The best way to come out of this is to restore form latest backup available. This also saves your time and downtime.
In the worst case if you don’t have the option to restore then you can call Microsoft for help or you can execute the below steps but be cautious this steps are extremely dangerous to execute and may lead to data loss. Before executing these you may take the help of Microsoft support help

EXEC sp_resetstatus 'yourDBname';

DBCC checkdb('yourDBname')



Please note that as stated above this is an extremely dangerous command to execute. It is a one- way command (that is you can not rollback back this execution once you started it) which can cause loss in data or database integrity. Technically, by executing this command you are actually authorizing SQL Server to force transactional recovery to run and skip the errors. If it faces errors, this operation scavenges as much out of the transaction log as it can and then rebuilds the transaction log from scratch. So taking this step is really the last resort you should try after every other attempts fails.

4)      Lastly if the problem is due to Hardware or OS failure then you need to restore from the latest backups. 


1)      Always ensure that you have good backup strategy

2)      Do not detach the database while the database is in suspect mode.
3)      Read and understand the error logs and do not panic.


Myths around Restore operations...

>> Friday, October 28, 2011

Myths around restore operations...

All of them are FALSE!

a) it is possible to do a point-in-time restore using WITH STOPAT on a full or differential backup

No. The syntax looks like it allows it, but it's just a syntactical nicety to allow you to do the best practice of using WITH STOPAT on every restore operation in the point-in-time restore sequence so you don't accidentally go past it.

b) it is possible to continue with a restore sequence after having to use WITH CONTINUE_AFTER_ERROR

No. If a backup is corrupt such that you must use WITH CONTINUE_AFTER_ERROR to restore it, that's restore terminates your restore sequence. If you're restoring a bunch of transaction log backups and one is corrupt, you may want to think carefully on whether you want to force it to restore or not. Forcing a corrupt log backup to restore could mean you've got inconsistent data in the database, or worst case, structural corruption. I'd most likely recommend not restoring it.

c) it is possible to restore different parts of a database to different points-in-time

No. A portion of the database cannot be brought online unless it is at the same point in time as the primary filegroup. The exception, of course, is a read-only filegroup.

d) it is possible to restore filegroups from different databases together in a new database

No. All the files in a database have a GUID in the fileheader page. Unless the GUID matches that of data file ID 1 in the database, it cannot be restored as part of the same database.

e) restore removes index fragmentation (or updates statistics, etc)

No. What you backup is what you get when you restore.

f) it is possible to shrink a database during a restore

No. This is an often-requested feature in SQL Server - be able to restore a very large, but mostly empty, database on a dev or QA server and have it only be the size of the data in the original database. But you can't.

g) you can restore a database to any downlevel version of SQL Server

No. This is one of the most pervasive myths. SQL Server cannot understand databases from more recent versions (e.g. SQL Server 2005 cannot understand a SQL Server 2008 database).

h) you can always restore a database to any edition of SQL Server

No. In SQL Server 2005, if there's an table/index partitioning in the database, it can only be restored on Enterprise (or Enterprise Eval or Developer) Edition. On SQL Server 2008 the list is partitioning, transparent data encryption, change data capture, and data compression.

i) using WITH STANDBY breaks the restore sequence

No. The WITH STANDBY option allows you to get a read-only transactionally-consistent look at the database in the middle of the restore sequence. As far as the restore sequence is concerned, it's as if you used WITH NORECOVERY. You can stop as many times as you like using WITH STANDBY. This is what log shipping uses when you ask it to allow access to a log-shipping secondary between log backup restores. Beware though, that using WITH STANDBY might cause some seemingly-weird behavior

j) instant file initialization during a restore doesn't work if the database wasn't backed up on a server with instant file initialization enabled

No. Whether instant file initialization is used is entirely dependent on whether the SQL Server instance performing the restore has it enabled. There is nothing in the backup itself that controls this.

k) restore is the best way to recover from corruption

No, not necessarily. Depending on what backups you have, restore may be the best way to recover with zero or minimal data loss, but it may be way slower than running a repair and accepting some data loss, or pulling damaged/lost data back from a log shipping secondary. The best way to recover from corruption is the one that allows you to best meet your downtime and data-loss service level agreements.

l) you can take a tail-of-the-log backup after starting a restore sequence

No. As soon as you start to restore over a database you lose the ability to backup the tail-of-the-log. The very first thing in a disaster recovery plan should always be to check whether a tail-of-the-log backup needs to be taken, just in case.

m) you can always do a point-in-time restore to a time covered by a log backup

No. If the log backup contains a minimally-logged operation then you cannot stop at a point in time covered by that log backup. You can only restore it in its entirety. This is because a log backup following a minimally-logged operation must include the data extents that were changed by the operation, but there's nothing in the backup that says *when* the extents were changed (that would be the transaction log - that wasn't generated because the operation was minimally logged!).

n) as long as the backup completes successfully, the restore will work too

No, no, no, no. A backup file is just like a data file - it sits on an I/O subsystem. And what causes most corruptions? I/O subsystems. You must periodically check that your backups are still valid otherwise you could be in for a nasty surprise when disaster strikes. See Importance of validating backups. The other thing to consider is that an out-of-band full or log backup could have been taken that breaks your restore sequence if it's not available.

o) all SQL Server page types can be single-page restored

No. Various allocation bitmaps and critical metadata pages cannot be single-page restored (or fixed using automatic page repair with database mirroring in SQL Server 2008).

p) using RESTORE ... WITH VERIFYONLY validates the entire backup

No. Using VERIFYONLY only validates the backup header looks like a backup header. It's only when you take the backup using WITH CHECKSUM and do RESTORE ... WITH VERIFYONLY *and* using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.

q) it is possible to restore a backup of an enrypted database without first having restored the server certificate

No. That's the whole point of transparent data encryption. Lose the server certificate, lose the database.

r) a restore operation performs all REDO and UNDO operations when the restore sequence is completed

No. The REDO portion of recovery is performed for each restore operation in the restore sequence. The UNDO portion is not done until the restore sequence is completed.

s) a compressed backup can only be restored using Enterprise Edition in SQL Server 2008

No. All editions can restore a compressed backup. New in SQL Server 2008 R2, Standard Edition can create a compressed backup as well as Enterprise Edition.

t) the restore of a database from an earlier version of SQL Server can be made to skip the upgrade process

No. It is not possible to skip any necessary upgrade or recovery during a database restore or attach.

u) a backup taken on a 32-bit instance cannot be restored on a 64-bit instance, and vice-versa

No. There is not difference in the database format on different CPU architectures.

v) restoring the database is everything the application needs to continue

No. Just like with a high-availability failover to a database mirror or log shipping secondary, everything in (what I call) the application ecosystem must be there for the application to work. That may include ancillary databases, logins, jobs, stored procedures etc.

w) to restore a damaged file from a multi-file filegroup you must restore the entire filegroup

No. This used to be the case before SQL Server 2000, but not any more.

x) you can restore a backup to any uplevel version of SQL Server

No. You can only restore a database from two versions back (i.e. you cannot directly restore a SQL Server 7.0 database to SQL Server 2008).

y) a restore operation will always take the same time as the backup operation

No. There are a ton of things that can affect restore time - like whether there's a long-running transaction that need to be rolled back, or whether the database files need to be created and zero-initialized. There's no guarantee.

z) you should always drop a database before restoring

No. If you drop the database then the database files need to be created and zero initalized (or at least the log file does if you have instant file initialization enabled). Also, you should *always* have a copy of the damaged database just in case the restore fails for some reason.

This myth is one of the most common and I've come across very few people who know the truth.

a1) after breaking the log backup chain, a full database backup is required to restart it.


A normal transaction log backup contains all the transaction log generated since the previous log backup (or since the first ever full backup if it's the first ever log backup for the database). There are various operations that will break the log backup chain - i.e. prevent SQL Server from being able to take another log backup until the chain is restarted. The list of such operations includes:

Switching from the FULL or BULK_LOGGED recovery models into the SIMPLE recovery model
Reverting from a database snapshot
Performing a BACKUP LOG using the WITH NO_LOG or WITH TRUNCATE_ONLY (which you can't do any more in SQL Server 2008 )

Here's an example script that shows you what I mean:

BACKUP DATABASE LogChainTest TO DISK = 'C:\SQLskills\LogChainTest.bck' WITH INIT;
BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log1.bck' WITH INIT;

Processed 152 pages for database 'LogChainTest', file 'LogChainTest' on file 1.
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.088 seconds (14.242 MB/sec).
Processed 2 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP LOG successfully processed 2 pages in 0.033 seconds (0.341 MB/sec).

I created a database, put it into the FULL recovery model, started the log backup chain, and then momentarily bounced it into the SIMPLE recovery model and back to FULL.

Now if I try to take a log backup:

BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

SQL Server knows that I performed an operation which means the next log backup will NOT contain all the log generated since the previous log backup, so it doesn't let me do it.

The myth says that a full database backup is required to restart the log backup chain. In reality, all I need is a data backup that bridges the LSN gap. A differential backup will do:

BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;

Processed 40 pages for database 'LogChainTest', file 'LogChainTest' on file 1.
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.083 seconds (4.040 MB/sec).
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.010 seconds (0.768 MB/sec).

This is really cool because you don't need to take a (potentially very large) full database backup to be able to continue with regular log backups.

If you have a backup strategy that involves file or filegroup backups as well as database backups, you can even restart the log backup chain after a single file differential backup! Take note, however, that to be able to restore that database, you'd need to have a data backup of each portion of it that bridges the LSN gap (i.e. a file or filegroup full or differential backup) but that's more complicated than I want to go into in this post.

Note:-This article I have took from other site for the purpose of spreading the knowledge.
You can download full pdf version of this article here:


Total Pageviews

There was an error in this gadget

Share it

  © Blogger template Webnolia by 2009

Back to TOP