How to Repair a Suspect Database in SQL Server

How to Repair a Suspect Database in SQL Server

here can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the steps which you need to follow to recovery a database which is marked as SUSPECT. The steps mentioned in this article works on SQL Server 2005 and higher versions.

Some of the reasons why an SQL Server database can be marked as SUSPECT

1. 1. Database could have been corrupted.
   2. There is not enough space available for the SQL Server to recover the database during startup.
   3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
   4. Database files are being held by operating system, third party backup software etc.
   5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.

Steps to Recover a Database Marked as SUSPECT

1. Execute the below mentioned TSQL code to identify all the databases which are marked as SUSPECT.

USE master
GO

SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'GO

2. Open the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. You can open SQL Server Error Log by expanding Management Node à SQL Server Error Logs. In my server I could find below mentioned entries in SQL Server Error Logs.

Sample Error Messages within SQL Server Error Log when database is marked as SUSPECT

Starting up database 'BPO'.
Error: 9003, Severity: 20, State: 9.

The log scan number (189624:16:2) passed to log scan in database 'BPO' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Error: 3414, Severity: 21, State: 1.

An error occurred during recovery, preventing the database 'BPO' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

CHECKDB for database 'BPO' finished without errors on 2009-12-15 11:30:28.320 (local time). This is an informational message only; no user action is required.

3. When a database is in SUSPECT mode you will not be able to get connected to the database. Hence you need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.

USE master
GO 

ALTER DATABASE BPO SET EMERGENCY
GO

Once the database is in EMERGENCY mode you will be able to query the database.

5. Next step will be to bring the user database in SINGLE_USER mode by executing the below mentioned TSQL code.

ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO

6. Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database. When you repair your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data. Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command then there is no way to go back to the previous state.

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS) 
GO

7. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE BPO SET MULTI_USER
GO

Copying Database Files from SQL Server 2005 to Higher Versions

Copying Database Files from SQL Server 2000 to Higher Versions

Try making a backup of your database, and restoring it into a brand new database.

• Create new DB.
• Make a full backup of your original.
• Right click on your new DB, hit restore.
• Navigate to your .BAK, and ensure the .mdf and .ldf match the new.

Note:In SQL Server 2005 Express edition there is no Copy Database Wizard, the n the above method is better for Copying database files from lower to higher editions.

If Copy Database Wizard available:

Following are the steps to copy database from one instance to another instance.

Specify Source Server

Specify Destination Server

Here you can select option if you want to keep the database ONLINE when it is being copied.

You can also select option of MOVE or COPY database as well.

Give appropriate database name.

On this screen you can select additional options to copy as well.

You create the package over here.

You can schedule the package using SQL Server Agent.

When this process is over it will show the success message and database will be copied to another server.

Moving Database from SQL Server 2005 to Higher Versions(Detach &Attach Method)

Detach and Attach method to move database from sql 2005 to 2008

Detach:

In this Topic we will see how to Detach Database from SQL Server 2005 to SQL Server  Higher Versions. You can use database Detach and Attach Method option to move database  from one location to another location on the server.

Different ways to Detach Database in SQL Server

• Detach Database in SQL Server Using SQL Server Management Studio (SSMS)
• Detach Database in SQL Server Using TSQL Script

Permissions to Detach Database in SQL Server

You need membership in the db_owner fixed database role to detach database in SQL Server.

Important Note: It is recommend to note down the location of Data and Log file before detaching the SQL Server Database.

TSQL Script to Identify location of Data and Log file of SQL Server Database

USE MyTechMantra
GO

Exec sp_helpfile
GO

How to Detach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Expand Databases node in Object Explorer

3. Right click User Database -> Tasks ->  Detach… option from the drop down list as shown in the snippet below.

4. In Detach Database dialog box, select Drop Connections and Update Statistics check boxes as shown in the snippet below and click OK to detach the user database from SQL Server. You won’t be able to detach a database until all the connection to the database is dropped. Refer the following article titled “How to Get Exclusive Access to SQL Server Database” to know how to kill all active connections from a database. It is always a best practice to Update Statistics before detaching SQL Server database.

5. Once you have clicked OK the database will be detached from SQL Server.

How to Detach Database in SQL Server Using TSQL Script

Database Administrator can detach database from SQL Server using the below mentioned TSQL script.

USE [master]
GO
ALTER DATABASE [MyTechMantra] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyTechMantra', @skipchecks = 'false'
GO

Attach:

How to Attach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Right click Databases -> Attach… option from the drop down list as shown in the snippet below.

3. In Attach Databases dialog box click on Add… button as shown in the snippet below.

4. In Locate Database Files dialog box select the .MDF file of the database and click OK to return to Attach Database dialog box.

5. In Attach Databases dialog box; under Databases to attach and Database Details you will see all the relevant information with respect to the database which you are attaching in SQL Server. Finally, click OK to attach the database to SQL Server.

How to Attach Database in SQL Server Using TSQL Script

Database Administrator can attach database in SQL Server using the below TSQL script.

USE [master]
GO
CREATE DATABASE [MyTechMantra] ON 
( FILENAME = N'D:\MSSQL\Data\MyTechMantra.mdf' ),
( FILENAME = N'D:\MSSQL\Data\MyTechMantra_log.ldf' )
 FOR ATTACH
GO