SQL Delete duplicatie records in a table which as a primary key

delete e1.* FROM TempTable e1 INNER JOIN TempTable e2 ON e1.col1=e2.col1 AND e1.col2=e2.col2 AND e1.ID>e2.ID

DB Backup and Restore with Differential backups

--Taking Backup

--Step1:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

GO

--Step2:

--Create some test table and insert some Rows

--Step3:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH DIFFERENTIAL

GO

--Step4:

--Insert Some more rows

--Step5:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_2.bak' WITH DIFFERENTIAL

GO

--Step4 and Step5 Can be repeated as many time

-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB First Time

--Step1:

--Create TestDB1 using UI . Pointing MDF and LDF as shown below, And without 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB1

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB1\TestDB1.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB1\TestDB1.ldf';

GO

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB Second Time

--Step1:

--Create TestDB2 using UI . Pointing MDF and LDF as shown below, And with 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB2

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH NORECOVERY,REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB2\TestDB2.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB2\TestDB2.ldf';

GO

--Step3:

RESTORE DATABASE TestDB2 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH RECOVERY

GO

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB Third Time

--Step1:

--Create TestDB3 using UI . Pointing MDF and LDF as shown below, And with 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB3

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH NORECOVERY,REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB3\TestDB3.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB3\TestDB3.ldf';

GO

--Step3:

RESTORE DATABASE TestDB3 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH NORECOVERY

GO

--Step4:

RESTORE DATABASE TestDB3 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_2.bak' WITH RECOVERY

GO

free hit counter

Execute .NET Code under SQL Server

----------Create one database anmed TestingCLR-----------------------

--ALTER DATABASE TestingCLR SET TRUSTWORTHY ON

--GO

-----Start---Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

------using System;

------using System.Collections.Generic;

------using System.Text;

------using System.IO;

------using System.Data.SqlTypes;

------using System.Data.SqlClient;

------using Microsoft.SqlServer.Server;

------namespace ManagedCodeAndSQLServer

------{

------    public class BaseFunctionClass

------    {

------        #region "Default Constructor"

------        public BaseFunctionClass()

------        {

------        }

------        #endregion

------        #region "Welcome Function"

------        ///

------        /// This function will be called from the SQL Stored Procedure.

------        ///

------        /// Name

------        /// Welcome Message

------        [SqlProcedure]

------        public static void GetMessage(SqlString strName, out SqlString

------        strMessge)

------        {

------            strMessge = "Welcome," + strName + ", " + "your code is executed under CLR !";

------        }

------        #endregion

------    }

------}

-----End------Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

--sp_configure 'clr enable', 1

--GO

--RECONFIGURE

--GO

--CREATE ASSEMBLY ManagedCodeAndSQLServer

--AUTHORIZATION dbo

--FROM 'C:\Dev\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll'

--WITH PERMISSION_SET = UNSAFE

--GO

--CREATE ASSEMBLY Messaging

--AUTHORIZATION dbo

--FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

--WITH PERMISSION_SET = UNSAFE

--GO

--CREATE PROCEDURE usp_UseHelloDotNetAssembly

--@name nvarchar(200),

--@msg nvarchar(MAX)OUTPUT

--AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage

--GO

--DECLARE @msg varchar(MAX)

--EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output

--PRINT @msg

-----Ref : http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server

SQL Column level Encryption / Decryption

----------- Prerequisites to be done --------------------Start----------

--SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';

--CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';

--CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data';

--CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1;

----------- Prerequisites to be done --------------------End----------

OPEN SYMMETRIC KEY SymmetricKey1

DECRYPTION BY CERTIFICATE Certificate1;

Declare @Card_number as varchar(25)

set @Card_number = '1234'

select @Card_number OrginalVale, EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number) Encryptedvalue, CONVERT(varchar, DecryptByKey(EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number))) Decryptedvalue

CLOSE SYMMETRIC KEY SymmetricKey1;

Make Chart (Spatial results) from SQL Query

--To Draw a line string

----------------------------

SELECT geometry::STGeomFromText(

'LINESTRING(0 0,1 1)', 0 );

--To Draw Polygons

-----------------------------

SELECT geometry::STGeomFromText(

'POLYGON((0 0,0 1,1 1,1 0,0 0))',

0 );

SELECT geometry::STGeomFromText(

'MULTIPOLYGON(

((0 0,0 1,1 1,1 0,0 0)),

((2 0,2 1,3 1,3 0,2 0)))', 0 );

--Fetch Data from Table and Form chart

--------------------------------------------------

--Create Table

CREATE TABLE [dbo].[Sales](

[FY] [int] NOT NULL,

[Sales] [int] NOT NULL

) ON [PRIMARY]

--Form chart from Results of table

DECLARE @WKT AS VARCHAR(8000);

SET @WKT =

  STUFF(

    (SELECT ',' + CAST( FY AS CHAR(4) ) + ' ' + CAST( Sales AS VARCHAR(30) )

    FROM Sales

    ORDER BY FY

    FOR XML PATH('')), 1, 1, '');

Select @WKT

SELECT geometry::STGeomFromText( 'LINESTRING(' + @WKT + ')', 0 );

--Form Chart with some manipulations in table Result

DECLARE @WKT AS VARCHAR(8000);

SET @WKT =

  STUFF(

    (SELECT ',((' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0,' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' ' +

      CAST( Sales AS VARCHAR(30) ) + ',' +

      CAST( FY + 0.3 AS VARCHAR(30) ) + ' ' +

      CAST( Sales AS VARCHAR(30) ) + ',' +

      CAST( FY + 0.3 AS VARCHAR(30) ) + ' 0,' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0))'

    FROM Sales

    ORDER BY FY

    FOR XML PATH('')), 1, 1, '');

SELECT geometry::STGeomFromText( 'MULTIPOLYGON(' + @WKT + ')', 0 );

Ref : http://sqlmag.com/t-sql/generating-charts-and-drawings-sql-server-management-studio

TFS Add Work Item Column

1) Get List of WorkItem:

     witadmin listwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName

2) Export Specific WorkItem Type from the List:

     witadmin exportwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName/f:C:\TFS\WorkItem\Product_Backlog_Item.xml /n:"Product Backlog Item"

3) Edit the xml in VS 2015 it will open Eork Item Editor

4) Add Custom Columns ( StartDate -- Custom.StartDate, End Date -- Custom.EndDate)

5) Set the Layout where the colum should display

6) Save the WorkItem Template

7) Validate the WorkItem template

     witadmin importwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName/f:C:\TFS\WorkItem\Product_Backlog_Item.xml /v

8) Import the WorkItem template (by removing /v in previous command)

    witadmin importwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName /f:C:\TFS\WorkItem\Product_Backlog_Item.xml

What to DB if DB Goes Suspect Mode ?

--First Check the auto recovery status may be the DB will be in recovery mode 

--xp_readerrorlog

--IF Db not in recovery mode and it is Suspect mode

-- Execute the below query line by line after removing comments
-------------------------------------------------------------------------------------------------------------------

--EXEC sp_resetstatus 'dbname';

--ALTER DATABASE [dbname] SET EMERGENCY

--DBCC checkdb('dbname')

--ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--DBCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS)

--ALTER DATABASE [dbname] SET MULTI_USER

--DBCC CheckDB ('dbname')

-------------------------------------------------------------------------------------------------------------------

--Ref1 : http://www.sql-server-performance.com/2015/recovery-sql-server-suspect-mode/2/

--Ref2: http://www.stellarinfo.com/support/kb/index.php/article/procedure-to-recover-sql-database-from-suspect-mode

free hit counter