Database is in suspect or emergency mode. Can a native SQL Server backup or LiteSpeed backup be ran for Full, Differential or Log backup?
A database can be marked for many reasons:
- A database or log file is missing.
- The database could be corrupt.
- SQL Server does not have enough space to recover the database on startup.
A log backup can be attempted using the "NO_TRUNCATE" option. It is likely that the corruption will not re-surface when replaying the actions in the log (restoring that log backup). If a log back is successful using the NO_TRUNCATE option, it is likely that single committed transactions will not be lost.
After performing the log backup (if possible), restore the latest database backup, possibly the latest differential backup, and all subsequent log backups. If possible, do the restore to a new database name and verify this before switching over production to the new database. The database can be renamed using the "sp_renamedb" stored procedure.
For example:
1. Review the SQL Server and NT error logs to see if the problem can be identified.
2. Start SQL Server in single user mode.
3. Go to Control Panel | Services and stop SQL Server.
4. Add the -m switch in the parameters pane.
5. Start SQL Server.
6. Run sp_resetstatus with the @dbname parameter. (sp_resetstatus @dbname = "pubs")Perform detailed DBCC checks (CHECKDB, CHECKALLOC, etc).
7. Run a few random queries to see if any problems are experienced.
8. If no problems occur, stop and start the SQL Server and open the database to production.
As an absolute last resort, place the database in emergency mode. By placing the database in emergency mode, you will be allowed to copy data out of the database, even if the data is corrupt. To place the database in emergency mode, use the following command:
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases set status = -32768 WHERE name = 'pubs'
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
You can then BCP data out and place it into a different database.
Refer to the following article on the SQL Server Central website "Help! My Database is Marked Suspect", for additional details:
http://www.sqlservercentral.com/columnists/bknight/unmarksuspect.asp
