How to Recover Deleted Data from SQL Server?

April 29, 2012 Leave a comment

This is the most asked question in SQL Forums. How to recover deleted data by mistake from SQL Server? Most DB Admins will answer: “No, there is no way. If you do not have a backup of the data you cannot retrieve the deleted records.”

In this article, we say “Yes, there is a way”. So let us start by creating this procedure:

CREATE PROCEDURE Recover_Deleted_Data_Proc
@SchemaName_n_TableName NVARCHAR(Max),
@Date_From datetime=’1900/01/01′,
@Date_To datetime =’9999/12/31′
as

DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(Max)
DECLARE @AllocUnitID BIGINT
Declare @AllocUnitName NVARCHAR(Max)
Declare @SQL NVARCHAR(Max)
DECLARE @bitTable TABLE
(
[ID] INT,
[Bitvalue] INT
)

INSERT INTO @bitTable
SELECT 0,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,8 UNION ALL
SELECT 4,16 UNION ALL
SELECT 5,32 UNION ALL
SELECT 6,64 UNION ALL
SELECT 7,128

–Create table to collect the row data.

DECLARE @DeletedRecords TABLE
(
[RowLogContents] VARBINARY(8000),
[AllocUnitID] BIGINT,
[Transaction ID] NVARCHAR(Max),
[FixedLengthData] SMALLINT,
[TotalNoOfCols] SMALLINT,
[NullBitMapLength] SMALLINT,
[NullBytes] VARBINARY(8000),
[TotalNoofVarCols] SMALLINT,
[ColumnOffsetArray] VARBINARY(8000),
[VarColumnStart] SMALLINT,
[NullBitMap] VARCHAR(MAX)
)

–Create a common table expression to get all the row data plus how many bytes we have for each row.

;WITH RowData AS (
SELECT
[RowLog Contents 0] AS [RowLogContents]
,[AllocUnitID] AS [AllocUnitID]
,[Transaction ID] AS [Transaction ID]
,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] –@FixedLengthData
,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as [TotalNoOfCols]
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0×30,0×70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) AS [TotalNoofVarCols]
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0×30,0×70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0×30,0×70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END)
* 2) ELSE null END) AS [ColumnOffsetArray]
,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0×30,0×70)
THEN (
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))
+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0×30,0×70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) * 2))
ELSE null End AS [VarColumnStart]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
Where object_id=object_ID(” + @SchemaName_n_TableName + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)
And SUBSTRING([RowLog Contents 0], 1, 1)In (0×30,0×70)
AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
Where Context IN (‘LCX_NULL’) AND Operation in (‘LOP_BEGIN_XACT’)
And [Transaction Name]=’DELETE’
And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
insert into @DeletedRecords
Select RowLogContents
,[AllocUnitID]
,[Transaction ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
,[NullBitMap]=(REPLACE(STUFF((SELECT ‘,’ +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) –as [nullBitMap]
FROM
N4 AS Nums
Join RowData AS C ON n<=NullBitMapLength
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH(”)),1,1,”),’,',”))
FROM RowData D
CREATE TABLE [#temp_Data]
(
[FieldName] VARCHAR(MAX),
[FieldValue] VARCHAR(MAX),
[Rowlogcontents] VARBINARY(8000)
)
–Create common table expression and join it with the rowdata table
– to get each column details
;With CTE AS (

/*This part is for variable data columns*/

SELECT Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
–Calculate the variable column size from the variable column offset array
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) ELSE 0 END) AS [Column value Size],
—Calculate the column length
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) – 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
ELSE 0 END) AS [Column Length]

–Get the Hexa decimal value from the RowlogContent
–HexValue of the variable column=Substring([Column value Size] – [Column Length] + 1,[Column Length])
–This is the data of your column but in the Hexvalue
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING(Rowlogcontents,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) – 1, 2)))) ELSE 0 END)
- ((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) – 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) + 1,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) – 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) – 1)) – 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) END AS hex_Value

FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset<0

UNION

/*This part is for fixed data columns*/
SELECT Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],
syscolumns.length AS [Column Length]
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
,syscolumns.length) END AS hex_Value

FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset>0 )

–Converting data from Hexvalue to its orgional datatype.
–Implemented datatype conversion mechanism for each datatype

INSERT INTO #temp_Data
SELECT NAME,
CASE
WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) –NVARCHAR ,NCHAR
WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0×00, 0×20)))) –VARCHAR,CHAR
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) –TINY INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) –SMALL INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) — INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))– BIG INTEGER
WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) –DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) –SMALL DATETIME
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) — NUMERIC
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) –MONEY,SMALLMONEY
WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) — DECIMAL
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) — BIT
WHEN system_type_id =62 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) — FLOAT
When system_type_id =59 THEN Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) –Real
WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘VARBINARY(8000)’)) = 0 THEN ’0x’ ELSE ” END) +cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘varchar(max)’) — BINARY,VARBINARY
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) –UNIQUEIDENTIFIER
END AS FieldValue
,[Rowlogcontents]
FROM CTE ORDER BY nullbit

–Create the column name in the same order to do pivot table.

DECLARE @FieldName VARCHAR(max)
SET @FieldName = STUFF(
(
SELECT ‘,’ + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id(” + @SchemaName_n_TableName + ”)
FOR XML PATH(”)
), 1, 1, ”)

–Finally did pivot table and get the data back in the same format.
SET @sql = ‘SELECT ‘ + @FieldName + ‘ FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (‘ + @FieldName + ‘)) AS pvt’

EXEC sp_executesql @sql

GO

 

After creating this procedure, you can just call it with the table name as parameters and specify a date range to get the deleted records in a period.

EXEC Recover_Deleted_Data_Proc 'dbo.Employees'

It works like magic!

Filed under SQL Scripts Tagged with , , , ,

How to Configure SQL Database Mirroring?

April 27, 2012 Leave a comment

To enable mirroring between the two servers, we need to create an endpoint and the needed certificates for correct authenticate and authorization. The following script handles the creation of the endpoints and certificates.

On Principal Database Server, the following script should run:

use master

go

–create master key encryption used to create the certificate, choose any password you want

create master key encryption by password = ‘abc123!!’;

go

–create the certificate with the above created key

create certificate Principal_cert with subject = ‘Principal_cert’, start_date = ’2007/11/01′, expiry_date = ’2020/11/01′

go

 –create the mirror endpoint needed for communication on port 5022

create endpoint endpoint_mirroring state = started

as tcp(listener_port = 5022, listener_ip = all)

for database_mirroring (authentication = certificate Principal_cert,

encryption = disabled, role = all);

go

 –backup the certificate created on a file and copy it to Mirror SQL Server

backup certificate Principal_cert to file = ‘c:\Principal_cert.cer’;

—————————————————————————————

Now, on the Mirror Database Server, the following script should run:

use master

 go

 –create master key encryption used to create the certificate

create master key encryption by password = ‘abc123!!’;

go

–create the certificate with the above created key

create certificate Mirror_cert with subject = ‘Mirror_cert’, start_date = ’2007/11/01′, expiry_date = ’2020/11/01′

go

 –create the mirror endpoint needed for communication on port 5022

create endpoint endpoint_mirroring state = started

as tcp(listener_port = 5022, listener_ip = all)

for database_mirroring (authentication = certificate Mirror_cert,

encryption = disabled, role = all);

go

 –backup the certificate created on a file and copy it to Principal SQL Servers

Backup certificate Mirror_cert to file = ‘c:\Mirror_cert.cer’;

—————————————————————————————

Now, go back to the Principal Database Server, the following script should run:

–create mirror login that will allow the Mirror SQL Server to connect and read from the Principal SQL Server

create login Mirror_login with PASSWORD = ‘abc123!!’;

go

 –create the needed user according to the above created login

create user Mirror_user from login Mirror_login;

go

 –create the certificate regarding Mirror server

Create certificate Mirror_cert

Authorization Mirror_user

From file = ‘c:\Mirror_cert.cer’;

Go

 –Grand Mirror_Login grant permission on the mirroring endpoint

Grant CONNECT ON Endpoint::endpoint_mirroring to [Mirror_login];

go

—————————————————————————————

Now, go back to the Mirror Database Server, the following script should run:

–create Principal login that will allow the Principal SQL Server to connect and write to the Mirror SQL Server

create login Principal_login with PASSWORD = ‘abc123!!’;

go

 –create the needed user according to the above created login

create user Principal_user from login Principal_login;

go

–create the certificate regarding Mirror server

Create certificate Principal_cert

Authorization Principal_user

From file = ‘c:\Principal_cert.cer’;

Go

–Grand Principal_Login grant permission on the mirroring endpoint

Grant CONNECT ON Endpoint::endpoint_mirroring to [Principal_Login];

go

The above script set up the communication media between the two databases. After running the above scripts on the respective servers, you need to do the following:

  1. Verify that the principal database uses the full recovery model.
  2. Take a full backup of the principal database
  3. Take a log backup of the principal database
  4. Restore the full backup of the principal database on the mirror server using the No Recovery option
  5. Restore the log backup of the principal database on the mirror server using the No Recovery option

After finishing the above, it is time to enable mirroring:

–On Mirror SQL Server (192.168.0.100 is the IP Address of the Principal SQL Server)

alter database dbname set partner = ‘TCP://192.168.0.100:5022′;

–On Principal SQL Server (192.168.0.200 is the IP Address of the Mirror SQL Server)

alter database dbname set partner = ‘TCP://192.168.0.200:5022′;

And we’re done…

Filed under SQL Scripts Tagged with , ,

How to Find “Big” Tables in your Database?

April 27, 2012 Leave a comment

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

– Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = ‘U’

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) – data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) – sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’)

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = ‘E’
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

Filed under SQL Scripts Tagged with ,

How to Truncate Database Log File?

April 27, 2012 Leave a comment

ALTER DATABASE yourdatabasename
SET RECOVERY SIMPLE;
GO

– Shrink the truncated log file to 1 MB.

– here 2 is the file ID for transaction log file,you can also mention the log file name (dbname_log)
DBCC SHRINKFILE (2, 1);
GO

– Reset the database recovery model.
ALTER DATABASE yourdatabasename
SET RECOVERY FULL;

Filed under SQL Scripts Tagged with

How to get Database and Log Backup History in SQL Server?

April 27, 2012 Leave a comment

SELECT database_name,
backup_start_date,
type
FROM msdb.dbo.backupset
ORDER BY backup_start_date DESC

Filed under SQL Scripts Tagged with , ,

Biometric Passports Make it Harder for Undercover CIA Officers

April 26, 2012 Leave a comment

Last year, I wrote about how social media sites are making it harder than ever for undercover police officers. This story talks about how biometric passports are making it harder than ever for undercover CIA agents.

Busy spy crossroads such as Dubai, Jordan, India and many E.U. points of entry are employing iris scanners to link eyeballs irrevocably to a particular name. Likewise, the increasing use of biometric passports, which are embedded with microchips containing a person’s face, sex, fingerprints, date and place of birth, and other personal data, are increasingly replacing the old paper ones. For a clandestine field operative, flying under a false name could be a one-way ticket to a headquarters desk, since they’re irrevocably chained to whatever name and passport they used.

“If you go to one of those countries under an alias, you can’t go again under another name,” explains a career spook, who spoke on condition of anonymity because he remains an agency consultant. “So it’s a one-time thing — one and done. The biometric data on your passport, and maybe your iris, too, has been linked forever to whatever name was on your passport the first time. You can’t show up again under a different name with the same data.”

(From Bruce Schneiner’s Blog)

Now, I know why I should wait for an hour in a line in Dubai Airport waiting my turn for the eye scan…

Filed under Security Tagged with , , , ,

What Tablet PC Should I Buy?

March 7, 2012 Leave a comment

I am going to Dubai next week and I have this habit to buy a new gadget with a very visit to Dubai. I decided to buy a tablet on this visit. First, why I should get a tablet. Here are my reasons:

  1. Tease my friends who have not yet got one
  2. Browse some pages while in bed before sleeping
  3. Used in meetings to take notes and show some files (business and family)
  4. Watch some series that I missed on YouTube (also in bed before sleeping)
  5. If there is an electricity shut off, it can run much longer than my laptop on its batteries

Now what tablet should I buy. Here is a comparison table that I compiled researching this subject:

Name OS Price (USD) Display Battery (h) Weight (kg) My Grade My Notes
Asus Transformer Prime Android 3.2 ~ 600 10.1 10 0.6 8 It has a docking station. So that you can use it as an actual PC.CPU performance is said to be like IPad. The lightest and thinnest in the market now.
Samsung Galaxy Tab 10.1 Android 3.1 ~ 400 10.1 10 0.5 4 No USB port. No SD card slot. I can’t copy my files/photos easily. So it is not good for me.
Acer Iconia Tab A100 Android 3.2 ~ 400  7.0 5 0.42 5 Low Battery Life. I don’t know if the 7.0 screen should be better than the 10.1.Connectivity is said to be more than excellent.
Sony Tablet S Android 3.1 ~ 400 9.4 8 0.60 8 You can replace your DVD/TV/Stereo remote with it.Good ergonomics in terms of holding it for hours.
Lenovo Ideapad K1 Android 3.1 ~ 400 10.1 8 0.75 3 My laptop is already Lenovo. It is good to change. No USB.
HP TouchPad HP webOS ~ 300 9.7 8 0.73 2  It is not Android nor iOS. So no apps for you.
BlackBerry PlayBook BlackBerry OS ~ 300 7.0 7 0.4 8 Interesting BlackBerry Bridge feature where you can browse the web by connecting to your BB phone. Amazing since I have a BB phone. But I already have a BB!
Motorola XOOM Android 3.0 ~ 400 10.1 8 0.7 6 No SD connectivity. Most reviews favored IPad over XOOM.
Apple iPad 2 iOS 4 ~ 500 9.7 10 0.66 6  No connectivity ports.

I think I have to choose between:

  • Motorola XOOM
  • Sony Tablet S
  • Asus Transformer Prime

I will tell you which one I got after I return. And I will have a full review of the one I chose by then.

Filed under Social

Very Funny… CIA and Facebook

January 19, 2012 1 Comment

Filed under Funny Tagged with , , ,

What is Hadoop?

December 24, 2011 Leave a comment

Hadoop is getting a lot of attention now in  the database and content management circles, but many people in the industry still don’t really know what it is and or how it can be best applied.

The underlying technology was invented by Google back in their earlier days so they could usefully index all the rich textural and structural information they were collecting, and then present meaningful and actionable results to users. There was nothing on the market that would let them do that, so they built their own platform. Google’s innovations were incorporated intoNutch, an open source project, and Hadoop was later spun-off from that. Yahoo has played a key role developing Hadoop for enterprise applications.

The Hadoop platform was designed to solve problems where you have a lot of data — perhaps a mixture of complex and structured data — and it doesn’t fit nicely into tables. It’s for situations where you want to run analytics that are deep and computationally extensive, like clustering and targeting. That’s exactly what Google was doing when it was indexing the web and examining user behavior to improve performance algorithms.

Hadoop applies to a bunch of markets. In finance, if you want to do accurate portfolio evaluation and risk analysis, you can build sophisticated models that are hard to jam into a database engine. But Hadoop can handle it. In online retail, if you want to deliver better search answers to your customers so they’re more likely to buy the thing you show them, that sort of problem is well addressed by the platform Google built. Those are just a few examples.

Hadoop is designed to run on a large number of machines that don’t share any memory or disks. That means you can buy a whole bunch of commodity servers, slap them in a rack, and run the Hadoop software on each one. When you want to load all of your organization’s data into Hadoop, what the software does is bust that data into pieces that it then spreads across your different servers. There’s no one place where you go to talk to all of your data; Hadoop keeps track of where the data resides. And because there are multiple copy stores, data stored on a server that goes offline or dies can be automatically replicated from a known good copy.

In a centralized database system, you’ve got one big disk connected to four or eight or 16 big processors. But that is as much horsepower as you can bring to bear. In a Hadoop cluster, every one of those servers has two or four or eight CPUs. You can run your indexing job by sending your code to each of the dozens of servers in your cluster, and each server operates on its own little piece of the data. Results are then delivered back to you in a unified whole. That’s MapReduce: you map the operation out to all of those servers and then you reduce the results back into a single result set.

Architecturally, the reason you’re able to deal with lots of data is because Hadoop spreads it out. And the reason you’re able to ask complicated computational questions is because you’ve got all of these processors, working in parallel, harnessed together.

It’s fair to say that a current Hadoop adopter must be more sophisticated than a relational database adopter. There are not that many “shrink wrapped” applications today that you can get right out of the box and run on your Hadoop processor. It’s similar to the early ’80s when Ingres and IBM were selling their database engines and people often had to write applications locally to operate on the data.

That said, you can develop applications in a lot of different languages that run on the Hadoop framework. The developer tools and interfaces are pretty simple. Some of our partners — Informatica is a good example — have ported their tools so that they’re able to talk to data stored in a Hadoop cluster using Hadoop APIs. There are specialist vendors that are up and coming, and there are also a couple of general process query tools: a version of SQL that lets you interact with data stored on a Hadoop cluster, and Pig, a language developed by Yahoo that allows for data flow and data transformation operations on a Hadoop cluster.

Hadoop’s deployment is a bit tricky at this stage, but the vendors are moving quickly to create applications that solve these problems. I expect to see more of the shrink-wrapped apps appearing over the next couple of years.

Filed under Database Programming, Programming Tagged with , , ,

Still using Stored Procedures…

November 25, 2011 2 Comments

I’m sure we’ve all heard, over and over, that inline SQL is generally a bad practice, and that we should use Stored Procedures when possible. But let’s be realistic for a minute. Who wants to write a stupid stored procedure for every stupid little simple query needed.

Have you ever worked on a system where someone decreed* that all database calls must be Stored Procedures, and SQL is strictly verboten? I have, and this decision leads to incredible development pain:

  1. Stored Procedures are written in big iron database “languages” T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don’t want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from T-SQL.
  2. Stored Procedures typically cannot be debugged in the same IDE you write your UI. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.
  3. Stored Procedures don’t provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL exception handling, we get cryptic ‘errors’ returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?
  4. Stored Procedures can’t pass objects. So, if you’re not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter– either too many, not enough, or bad datatypes– I get a generic “bad call” error. SQL can’t tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.
  5. Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can’t view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is– or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

So why use Stored Procedures at all? Conventional wisdom says we do it because:

  • Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
  • Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
  • Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
  • Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
  • Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

However, there’s one small problem: none of these things are true in practice. The benefits are marginal, but the pain is substantial. And I’m not the only person that feels this way.

The new (as of SQL Server 7.0) cached execution plan optimization in SQL Server looks to me a lot like JIT compilation. If this is, in fact, the case it seems to me that the only overhead that would be associated with dynamic SQL would be:

  1. The amount of bandwidth + time it takes to transmit the dynamic SQL text to the database.
  2. The amount of time it takes to calculate the hash of the dynamic SQL text to look up the cached execution plan.

I can imagine quite a few scenarios where the above overhead would disappear into the noise of the network roundtrip. What upsets me are the folks who spout forth anecdotal arguments that claim stored procedures have “much better” performance than dynamic SQL.

For modern databases and real world usage scenarios, I believe a Stored Procedure architecture has serious downsides and little practical benefit. Stored Procedures should be considered database assembly language: for use in only the most performance critical situations. There are plenty of ways to design a solid, high performing data access layer without resorting to Stored Procedures; you’ll realize a lot of benefits if you stick with parameterized SQL and a single coherent development environment.

Filed under Database Programming Tagged with , , ,

← Older posts

Follow

Get every new post delivered to your Inbox.

Join 125 other followers