SQL Server Database - Recover From Suspect mode

Sometimes you find your SQL database in suspect mode. This normally happens when your PC abnormally shutdowns or any other major hardware failure. Here i will explain how to recover your precious data from this.

In many cases there will be no change in the size of MDF and LDF files, but more likely LDF is corrupted.

So here is the solution of your problem.

• First make sure that database is in suspect mode
  • Create a new empty database at some safe location
  • Now stop the SQL Server service
  • Replace the old MDF file over newly created
  • Start SQL service and database will try to recover it. (During this database will go to suspect mode)
• Now we will put the database to emergency mode (This can be done by using ALTER DATABASE or following commands)

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = ''
GO
sp_dboption '', 'single_user', 'true'
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Try to use DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS) This will rebuild log file and run full repair of the MDF file)

DBCC CHECKDB('', REPAIR_ALLOW_DATA_LOSS)
Go

(*If it gives error use next step)

• Then use DBCC REBUILD_LOG ('','new LDF path') to rebuild the LDF file

DBCC REBUILD_LOG('', 'new LDF path')
Go

• Almost done. Now we will push database to normal mode (Again use either ALTER DATABASE or following commands)
  

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 16 where name = ''
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Restart the engine of Microsoft SQL Server (I have successfully checked on Microsoft SQL 2000)
• Verify your database by checking its tables and data.

* Don't forget to make a daily backup schedule next time :)

Microsoft SQL Server 2000 Alternative

There is a free version of Microsoft SQL Server 2000 named MSDE (Microsoft SQL Server Desktop Engine) with few limitations

Maximum 2GB database

Slow performance over 8 concurrent connections

Not supported in Windows NT 6.

No GUI management tool

http://en.wikipedia.org/wiki/MSDE

Get application name for current session in SQL server

Get application name for current session in SQL server.

SELECT APP_NAME()

Get current user name in SQL server

Get current user name in SQL server.

SELECT CURRENT_USER

Get current login name in SQL server

Get current login name in SQL server.

SELECT SYSTEM_USER

Executing dynamic queries with more than 4000 characters in SQL server

Executing dynamic queries with more than 4000 characters in SQL server.

DECLARE @sql_Body_1 VARCHAR(8000)

DECLARE @sql_Body_2 VARCHAR(8000)

DECLARE @sql_Body_3 VARCHAR(8000)

DECLARE @sql_Body_4 VARCHAR(8000)

DECLARE @sql_Body_5 VARCHAR(8000)

DECLARE @sql_Hold VARCHAR(8000)

SET @sql_Body_1=''

SET @sql_Body_2=''

SET @sql_Body_3=''

SET @sql_Body_4=''

SET @sql_Body_5=''

SET @sql_Hold=''

DECLARE @i INT

SELECT @i = 1

WHILE @i < 1000

BEGIN

SELECT @sql_Hold = 'insert into ...'

IF 3999 - LEN(@sql_Body_1) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_1 = @sql_Body_1 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_2) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_2 = @sql_Body_2 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_3) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_3 = @sql_Body_3 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_4) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_4 = @sql_Body_4 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_5) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_5 = @sql_Body_5 + @sql_Hold

END

SELECT @i= @i + 1

END

EXEC (@sql_Body_1+ ' ' + @sql_Body_2+ ' ' + @sql_Body_3+ ' ' + @sql_Body_4+ ' ' + @sql_Body_5)

Iterate on user tables in SQL server database

Iterate on user tables in SQL server database.

DECLARE @TABLE_NAME sysname

SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

WHILE @TABLE_NAME IS NOT NULL

BEGIN

PRINT @TABLE_NAME

SELECT @TABLE_NAME = MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME > @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

END

Avoid trigger error for text,ntext,image data types in SQL server

SQL server triggers do not work with text,ntext and image data types. So avoid these like this...

Select COLUMN_NAME

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME = 'tbl_Name'

AND CAST(INFORMATION_SCHEMA.columns.DATA_TYPE AS nVARCHAR(100)) not IN (CAST('text' AS nVARCHAR(100)),CAST('ntext' AS nVARCHAR(100)),CAST('image' AS nVARCHAR(100)))

AND COLUMN_NAME NOT in

(select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'tbl_Name'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

Get name of non-primarykey columns of a table in SQL server

Get all column names of a table that are not primary key.

Select COLUMN_NAME

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME = 'tbl_Name'

AND COLUMN_NAME NOT in

(select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'tbl_Name'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

Get name of primarykey columns of a table in SQL server

Get names of all primarykey columns of a table in SQL server.

select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'TBL_NAME'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

Replicate / Padding in SQL server

Right or left padding in SQL server. Use Replicate for this...

PRINT REPLICATE('-', 10 - LEN('TABLE'))+'TABLE'

PRINT 'TABLE' + REPLICATE('-', 10 - LEN('TABLE'))

Check and drop existing view in SQL database

Check if view exists in SQL server database and drop it if required.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TBL_View]') AND OBJECTPROPERTY(id, N'IsView') = 1)

BEGIN

DROP VIEW [dbo].[TBL_View]

END

Check and drop existing function in SQL database

Check if function exists in SQL server, and drop it if required.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FN_Name]') AND xtype in (N'FN', N'IF', N'TF'))

BEGIN

DROP FUNCTION [dbo].[FN_Name]

END

Check if table exists in SQL server database

Check if table exists or not in database in SQL server.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'TBL_Name')

BEGIN

PRINT 'Found'

END

Drop existing trigger in SQL server

Drop existing trigger from SQL server database.

IF OBJECT_ID ('Trigger_Name', 'TR') IS NOT NULL DROP TRIGGER Trigger_Name

List all user tables of a database in SQL server

List all user defined tables of a databse in SQL server.

SELECT *

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE = 'BASE TABLE'

AND TABLE_NAME != 'sysdiagrams'

SUBSTRING in SQL server

Get substring (piece of string) in SQL server.

DECLARE @Temp AS VARCHAR(100)

SELECT @Temp = 'I am part one.I am part two.'

SELECT SUBSTRING(@Temp,1,14)

SELECT SUBSTRING(@Temp,15,CHARINDEX('.',@Temp))

IndexOf in SQL server

Find index of a character within a string in SQL server

SELECT CHARINDEX('.','I am at here.Catch me.')

Decrypt column data in SQL server

CREATE FUNCTION [dbo].[Decrypt] ( @EncryptedString NVARCHAR(4000) )

RETURNS VARCHAR(4000) WITH ENCRYPTION AS

BEGIN

DECLARE @vClearString VARCHAR(4000)

DECLARE @vIdx INT

DECLARE @vBaseIncrement INT

SET @vIdx = 1

SET @vBaseIncrement = 128

SET @vClearString = ''''

WHILE @vIdx <= LEN(@EncryptedString)

BEGIN

SET @vClearString = @vClearString +

CHAR(UNICODE(SUBSTRING(@EncryptedString, @vIdx, 1)) -

@vBaseIncrement - @vIdx + 1)

SET @vIdx = @vIdx + 1

END

RETURN @vClearString

END

To encrypt check following...

http://binarymyth.blogspot.com/2010/06/encrypt-column-data-in-sql-server.html

Encrypt column data in SQL server

CREATE FUNCTION [dbo].[Encrypt] ( @InputString VARCHAR(4000) )

RETURNS NVARCHAR(4000) WITH ENCRYPTION AS

BEGIN

DECLARE @vEncryptedString NVARCHAR(4000)

DECLARE @vIdx INT

DECLARE @vBaseIncrement INT

SET @vIdx = 1

SET @vBaseIncrement = 128

SET @vEncryptedString = ''''

WHILE @vIdx <= LEN(@InputString)

BEGIN

SET @vEncryptedString = @vEncryptedString +

NCHAR(ASCII(SUBSTRING(@InputString, @vIdx, 1)) +

@vBaseIncrement + @vIdx - 1)

SET @vIdx = @vIdx + 1

END

RETURN @vEncryptedString

END

To decrypt check following

http://binarymyth.blogspot.com/2010/06/dencrypt-column-data-in-sql-server.html