SO FAR VISITED

SO FAR VISITIED
please visit postalguide100.blogspot.in - Solved question Paper IPO 2012 Paper III and model question papers for various examination

Saturday, July 21, 2012

Restoring/Repairing suspected database in Sql 2005


SQL server database can go in suspect mode for many reasons; some of them are given below:
Improper shutdown of the database server
Corruption of the database files
Unavailable device files
Unavailable database files
Database resource used by operating system
SQL Server incorrectly asserts free data page space when a row is inserted

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS
Output of the above query will give the errors in the database.
To repair the database, run the following queries in Query Analyzer,
EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
and you are done. 
You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

No comments:

Post a Comment

LinkWithin

Related Posts Plugin for WordPress, Blogger...