EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'Munim001'
IF @@ROWCOUNT = 1
BEGIN COMMIT TRAN
RAISERROR('Emergency Mode Successfully Set', 0, 1)
END
ELSE
BEGIN ROLLBACK
RAISERROR('Setting Emergency Mode Failed', 16, 1)
END
DBCC REBUILD_LOG('Munim001','D:\MSSQLDATA\Munim001.ldf')
--ALTER DATABASE 'Munim001' SET MULTI_USER
alter database Munim001 set multi_user with rollback immediate
DBCC CHECKDB ('Munim001')
Here are the steps (without details):
1. Rename existing .mdf file to .mdf_old
2. Create a new database with same .mdf and .ldf file as old one.
3. Stop the sql server
4. Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
5. Rename .mdf_old to .mdf
6. Start sql server
7. You should see db in suspect mode
8. Change the database context to Master and allow updates to system tables
9. Set the database in Emergency (bypass recovery) mode.
10. Stop and restart SQL server.
11. Rebuild the log.
12. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency.
13. Turn off the updates to system tables.
link|flag
Query Example
Sp_configure 'allow update', 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = 'Munim001'
go
Sp_configure 'allow updates', 0
go
Reconfigure with override
GO
DBCC REBUILD_LOG('Munim001','D:\MSSQLDATA\Munim001.ldf')
sp_resetstatus 'munim001'
