Great Tools for Testing Your Website on Mobile Devices

Smartphones and other handheld devices contribute substantially to the overall viewership of any website. Latest estimates show that it is not long before the mobile Internet users are slated to have more stake in the website usage from their desktop and laptop counterparts.

In such a scenario it would be a cardinal sin to ignore the mobile users when it comes to development of a website. Thus one of the most important stages is making a website mobile friendly is to test it for different smartphone platforms and other handheld devices. Let us take a look the importance of mobile phone emulators when it comes to designing websites.

  1. A mobile phone emulator allows you to check the responsiveness and functionality of the mobile websites across different mobile platforms such as iPhone, Android, BlackBerry, iPad etc.
  2. Mobile emulators pinpoint the problems and issues concerning the website and allow you to take corrective measures to make the website visually appealing and functional on smartphones and other hand held devices.
  3. A mobile emulator eases the process of website testing and does away with the technique of testing the website on different devices. The developers can test the website on a virtual mobile interface with all the functionalities available on a device.
  4. Testing through mobile emulators works perfectly on touch based phones and the traditional QWERTY based smartphones.

There are a number of mobile phone emulators available in the market and here we’ll take a look at some of the most popular among them.

iPhone Tester

As the name suggests, this is one of the most popular mobile emulators which is used for checking the compatibility of a website on the iPhones. All that you need to do is enter the URL of your website into the search box and get a real-time preview of how it would appear on an iPhone.

Mobile Phone Emulator

This is surely one of the most popular mobile phone emulators that is being used by a large number of developers. The greatest advantage with this tool is that it tests the compatibility of the website across a large number of platforms including Samsung, iPhone, BlackBerry etc.

iPad Peek

iPad is surely the most acclaimed tablet available in the market and to ensure that your website is compatible with iPads you can try out iPad Peek. It will replicate the appearance and functionality of your website on an iPad.

Screenfly

This is one of the best preview tools as it allows you to see how the website would be rendered by a number of mobile devices apart from the traditional desktops and laptops. You can customize it the way you would like to test your website under various categories. This is a must have emulation tool due to its wide coverage of platforms.

MobiReady

This works similar to Gomez and allows you to enter the URL of your website and test the website along different parameters including Page Test, Markup Test and Site Test. What makes it even more powerful than Gomez is the fact that it checks many other parameters such as dotMobi compliance and W3C mobile compliance, offering detailed error reports. It also has a comprehensive code checker which comes in handy.

This article is copied from this article.

DROOLS.NET

Drools.NET is a Business Rules Engine (BRE) based on Charles Forgy’s Rete algorithm. Developers can now exploit a powerful Rule Engine through a completely managed .NET code base! Drools.NET is based on Jboss Rules, and comes with all the features of that Rules Engine.

Link: http://droolsdotnet.codehaus.org/

Drools.Net 3.0 supports the following features:

  • Full Rete Implementation with high performance indexing
  • Field Constraints
  • Literal, Bound Variable, ReturnValue and PredicateConstraints
  • Conditional Elements
  • And, Or, Not, Exists and Eval
  • Agenda Management
    • Conflict Resolution (Salience+Depth)
    • Agenda Groups
    • Activation Groups
    • Agenda Filters
    • No loop for recursion control
  • Truth Maintenance with Logical Assertions
  • Temporal Rules (allows rules to fire based on time requirements)
  • Dynamic Rules (runtime addition and removal of rules)
  • Event Model
    • Working Memory Actions
    • Agenda
  • Functions
  • Global Ddata

You can find an example of Drools implementation on Code Project: http://www.codeproject.com/Articles/29165/Getting-Started-with-Drools-NET

How to Recover Deleted Data from SQL Server?

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!

How to Configure SQL Database Mirroring?

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…

How to Find “Big” Tables in your Database?

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