How to repair a Suspect Database in SQL Server?

Suspect Database:
 Suspect Database is nothing but Corrupted database .it shows suspect massage when you log in to MS-SQL server. 

SOLUTION :

• Connect to your database server using Microsoft SQL Server Management Studio.

• Execute the following SQL script  :

  
//sp_resetstatus turns off the "suspect" flag on a database

EXEC sp_resetstatus [DatabaseName]

/* Marking database READ_ONLY, disable logging,and limiting
access only to members of the sysadmin fixed server role */

ALTER DATABASE [DatabaseName] SET EMERGENCY

  
/* Checks the logical and physical integrity of all the objects in the specified database */



DBCC checkdb([DatabaseName])

  
/* This query will rollback any transaction which is running on
that database and bring SQL Server database in a "single user" mode */
 

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS)

/* Set database accessibility to it's original state,
allowing all logins */



ALTER DATABASE [DatabaseName] SET MULTI_USER

Replace [DatabaseName] with your Database Name

How To Change Default Path of MS SQL Server Database

When MS-SQL Server installed by default DATABASE is stored in C. but you can change default path.
To change default path please follow steps :

• Login with "sa" user or "Windows Authentication"

• Right click on Server =====> Click on Properties.====> Now click on Database Settings.

 

• You can change the default location of the database. Now all database file will store in this Location.