RSS

Repairing Suspect SQL Database

14 Feb

Before you run this script

  • Kill all connections and stop SQL Server
  • Perform a Checkdisk with repair option
  • Perform a Defragmentation
  • Now copy the files necessary (MDF, LDF and Backups)
  • Perform a backup
  • Restore the backup
  • Repair the Database (DBCC CHECKDB)
  • Backup the repaired DB

USE [master]
GO
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <dbname> SET SINGLE_USER
GO
DBCC CHECKDB <dbname>, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS
GO
ALTER DATABASE <dbname> SET MULTI_USER
GO

Or

Take Care From this Script becouse it will make repaire and may be some Data will loss

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

 
2 Comments

Posted by on February 14, 2012 in Administration, Errors

 

2 responses to “Repairing Suspect SQL Database

  1. Adam Gorge

    January 28, 2013 at 11:00 AM

    The database got suspected due to power failure, sudden system shutdown, database corruption etc. After using REPAIR_ALLOW_DATA_LOSS command, you may also lost your precious data. You can use one another most secured solution without fear of data lost that is Stellar Phoenix SQL Recovery Software. This software repairs corrupt SQL Server database even all SQL Server database inbuilt utilities get failed to repair database. The software works under all corruption errors.

     
  2. Eng.Motafa Elmasry

    May 6, 2013 at 10:42 AM

    Thanks Adam for your good Comment

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s