How to Recover from Suspect Database
Many DBAs would have seen the database marked as suspect. If a database is marked suspect it cannot be accessed and hence nothing can be performed in the database. In this article I am going to explain how to overcome this and bring the database back to normalcy. There are three possible Workarounds to bring the database online and to retrieve the data. First try the Workaround 1 and if it fails go to Workaround 2 if that does’nt yield the desired result try the Workaround 3.
WORKAROUND 1:
Step 1 : If your database is marked suspect execute the below query,
Use Master
Go
Sp_resetstatus 'Database Name'
The above command will reset the status flag of the suspect database.
Step 2: Once the command is executed you need to restart the SQL services and check
the status of the database.
Step 3: Execute the below query to check the integrity of the database
Dbcc Checkdb('Database Name')
WORKAROUND 2:
Refer this posting by Paul Randal on suspect databases. We should NEVER detach a suspect database. Refer,
In most cases we would never be able to reattach a suspect database and hence we should never detach a suspect database but in case if you had already done it by mistake try the following options to attach it.
Sp_attach_single_file_db 'Database Name','Path of the databasefile'
WORKAROUND 1:
Step 1 : If your database is marked suspect execute the below query,
Use Master
Go
Sp_resetstatus 'Database Name'
The above command will reset the status flag of the suspect database.
Step 2: Once the command is executed you need to restart the SQL services and check
the status of the database.
Step 3: Execute the below query to check the integrity of the database
Dbcc Checkdb('Database Name')
WORKAROUND 2:
Refer this posting by Paul Randal on suspect databases. We should NEVER detach a suspect database. Refer,
In most cases we would never be able to reattach a suspect database and hence we should never detach a suspect database but in case if you had already done it by mistake try the following options to attach it.
Sp_attach_single_file_db 'Database Name','Path of the databasefile'
But the above procedure can be used only if there is single data file and single log file present. It will automatically build a new log file. If you have multiple data and log files proceed with Step 2.
If the above sp didn't work try the below steps:
- Create a database with the same name in another directory as the one you're trying to attach.
- Re-create all filesgroups and files as necessaryShutdown the server.
- Swap in the old mdf file and any ndf files.
- Bring up the server and let the database attempt to be recovered and then go into Suspect.
- Put the database in single-user mode.
- Run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
- Return database to online, multi-user mode.
Labels: DATABASE
0 yorum:
Yorum Gönder