Steps to Fix a suspect Database
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure ‘allow updates’, 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = "
– note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = "
– Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log(",")
where is the name of the database and is the physical path to the new log file, not a logical file name. If you do not specify the full path, the new log is created in the Windows NT system root directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency:
sp_dboption ", ’single user’, ‘true’
OR
Alter Database databasename SET SINGLE_USER
DBCC checkdb(")
Go
begin tran
update sysdatabases set status = where name = "
– verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure ‘allow updates’, 0
reconfigure with override
Go
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure ‘allow updates’, 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = "
– note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = "
– Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log(",")
where is the name of the database and is the physical path to the new log file, not a logical file name. If you do not specify the full path, the new log is created in the Windows NT system root directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency:
sp_dboption ", ’single user’, ‘true’
OR
Alter Database databasename SET SINGLE_USER
DBCC checkdb(")
Go
begin tran
update sysdatabases set status = where name = "
– verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure ‘allow updates’, 0
reconfigure with override
Go
Posts
I have heard about another way of sql server 2000 recovery database. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues
ReplyDeleteFor settling out troubles with invalid mdf files you can use repair mdf. It uses contemporary ways of restoring dbx files, the application starts under all PC software configuration and Windows OS. It demonstrates results of working with bad .mdf files.
ReplyDelete