I've my database, testdb, ended up in Suspect state. The SQL log shows " I/O error 38(Reached the end of the file.) detected during read at offset xxxxxxxxxx in file '\testdb_Data.MDF'" during recovery. I do not have backup to restore the database from.
So to run DBCC CHECKDB, I tried to put the database in emergency(bypass recovery) mode using
update sysdatabases set status = 32768 where name = 'testdb'
DBCC CHECKDB showed some allocation and consistency errors and suggested "repair_allow_data_loss" as minimum repair level.
Now to run
DBCC CHECKDB('testdb', repair_allow_data_loss)
I've to put database in SINGLE USER mode. For that I started SQL server by command
sqlservr.exe -c -m
Now when I try to run DBCC CHECKDB with repair option it says "Attempt to BEGIN TRANSACTION in database 'testdb' failed because database is in BYPASS RECOVERY mode."
COMANDI CHANGE STATUS
USE [master]
GO
ALTER
DATABASE <dbname>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
And do remember to make the database MULTI_USER after the restore.
USE [master]
GO
ALTER
DATABASE <dbname>
SET MULTI_USER