Recover SQL Server Database from Suspect Mode

If your Database goes to Suspected Mode you can try below queries to retrieve the database in normal mode.

Reason for database to go into suspect mode:

1.    Data files or log files are corrupt.

2.    Database server was shut down improperly

3.    Lack of Disk Space

4.    SQL cannot complete a rollback or roll forward operation.

1. Open SQL Server Management Studio 
2. Open new query window and execute some queries:
   
   
   • use master
   • Alter Database TestDataBase Set Emergency
   • ALTER DATABASE TestDataBase  SET SINGLE_USER
   • DBCC CHECKDB (TestDataBase , REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
   • ALTER DATABASE TestDataBase SET MULTI_USER
   • ALTER DATABASE TestDataBase SET ONLINE
   • SELECT STATE_DESC FROM SYS.DATABASES WHERE NAME='TestDataBase'
     
3. If all queries executed successfully, then database is recovered from suspected mode (some data may be loss after completion of this process. Ask the store to do PI after this process.).

Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly.

Read More

Detach, Attach and Restore the Database with Transact-SQL

Detach, Attach and Restore The Database with Transact-SQL using a test Database. Here i'm using 'MyDataBase' as Test DB.

Prerequisites

• The database must first be detached. Attempting to attach a database that has not been detached will return an error.
• When you attach a database, all data files (MDF and LDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.
• When you attach a database, if MDF and LDF files are located in different directories and one of the paths includes \\?\GlobalRoot, the operation will fail.

Detach The Database

USE master
EXEC sp_detach_db 'MyDataBase', 'true';

Attach The Database

USE master
CREATE DATABASE MyDataBase
ON 
(FILENAME = 'D:\DataBase\MyDataBase.mdf'), -- Location where to store Data File
(FILENAME = 'D:\DataBase\MyDataBase_log.ldf') -- Location where to store Log File
FOR ATTACH;

Restore The DataBase (.Bak File)

USE master
RESTORE DATABASE MyDataBase
FROM DISK = 'D:\DATABASE\MyDataBase'  -- Location where Bak file resides
WITH 
MOVE 'MyDataBase(ThisFashion)_Data' TO 'D:\DataBase\MyDataBase.mdf'  -- Location where to store Data File
MOVE 'MyDataBase(ThisFashion)_Log' TO 'D:\DataBase\MyDataBase_log.ldf'  -- Location where to store Log File

Read More