Jagadeeps’s Blog

I was struck with a SQL Server database which is on suspect mode and need to recover it without having any recent backups in hand. I Googled for quite a long time and found these steps to bring back the database to online state without losing any data. I found these steps very useful and I hope they will be useful to others who face similar problem

Step 1:
Open SQL Server Management Studio and connect to the SQL Server. Make sure that your current database is set to master

Step 2:
Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 3:
Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.

ALTER DATABASE DatabaseName SET EMERGENCY;

Step 4:
Restrict database to single user by changing the access mode as mentioned below
ALTER DATABASE DatabaseName SET SINGLE_USER;

Step 5:
Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.

DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Even though the step 4 is expected to result in data loss while repairing the database, I was lucky to restore the database without any loss. My job would have been much easier if the admin of the database server followed few general guidelines that allow recovery of database in case of any unexpected failures. Here are the few of them

* Backup your data frequently. (once in two days or daily or even more frequent depending on your needs)
* Have multiple backups. Move the backups to external drives or tapes frequently
* Validate that your backups are good by performing trial restores to alternate server
* Run CHECKDB regularly if possibly in your case, to know how long it regularly takes

Read more: http://www.techdreams.org/microsoft/how-to-recover-a-sql-server-database-from-in-suspect-mode/3696-20100224#ixzz0jY7Mjvg6

In SQL Server 2000, to run SQL Profiler the sysadmin needs to grant permissions to the user running the trace. In SQL Server 2005 and 2008, the option exists of granting permissions to Profiler so that users do not need to be a member of the sysadmin group by using the code below:

USE master
GO
GRANT ALTER TRACE TO username;
GO

If the permission needs to be revoked, use the code below:

USE master
GO
DENY ALTER TRACE TO username;
GO

UPDATE Table
SET Gender =CASE WHEN Gender =’M’ THEN ‘Male’
WHEN Gender =’F’ THEN ‘Female’
END

With the above query we can update all the columns at a time.

A table can have 249 Non-Clustered indexes, and only one clustered index.

The for having only one clustered index for a table is, the clustered index physically sorts the column values, and the data columns can be sorted in only one order Hence we can say that a table cannot have more than one clusterd index.