RSS

Monthly Archives: October 2012

Create comma-separated list

1- Create Table Emp

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATE TABLE [dbo].[Emp](

[Emp_Code] [int] NULL,

[Emp_Name] [nvarchar] (50)NULL)

ON [PRIMARY]

GO

2-  Insert Data

Insert into Emp values (1,‘Mostafa’),(1,‘Moahmed’),(2,‘Elsayed’),(2,‘Elmasry’)

GO

3- Create comma-separated list

WITH CTE AS (

SELECT DISTINCTEmp_Code FROM Emp )

SELECT Emp_Code, CommaList =STUFF((SELECT‘,’+ Emp_name FROM Emp

WHERE Emp_Code = CTE.Emp_Code ORDERBY Emp_name

FORXMLPATH(),TYPE).value(‘.’,‘varchar(max)’),1,1,)FROM CTE

ORDERBY Emp_Code;

 

 

 

 
Leave a comment

Posted by on October 31, 2012 in Script

 

Tags:

Transparent data encryption (TDE)2 Created by Eng.Ahmed Abdel Moteleb

Special thanks to the engineer Ahmed Abdel Moteleb

Introduction

Database Encryption this very Important Feature in SQL Server 2008 becouse in 2005 you can make Encryption but not in Whole Database you can make it on Tables but when Comming SQL Server 2008 Come With him this New Feature Database Encryption nooooooooooooooooooooooow You Can Encrypt The Whole Database i write Article in this Subject Since one year (02/11/2011)Check it :

https://mostafaelmasry.wordpress.com/2011/11/02/transparent-data-encryption-tde-2/

Before 2 days my Manager Aske me to make Encryption to Whole Database on one from My Client but the Problem this Client use SQL Server 2005 and i forget it’s Impossible to make Encryption on Whole Database in SQL Server 2005 So i asked my Friend

Eng.Ahmed Abdel Moteleb Database Administrstrator and Developer Team Leader

He told me you can make it on SQL Server 2008 and i try it more than once so try this Code and he Send to me this Script : Lets go to See the Script

1-CREATE MASTER KEY

USE MASTER;

GO

IF EXISTS(SELECT*FROMsys.symmetric_keysWHEREname=N’##MS_DatabaseMasterKey##’)

Begin

DROPMASTERKEY;

CREATEMASTERKEYENCRYPTIONBYPASSWORD=‘@dmin$afeP@$$w0rd’;

Print‘Master Key Drop + Created’

END

Else

begin

CREATEMASTERKEYENCRYPTIONBYPASSWORD=‘@dmin$afeP@$$w0rd’;

print‘masteer key Created’

end

GO

2-CREATE CERTIFICATE

IF EXISTS(SELECT*FROMsys.certificatesWHEREname=N’MYDATABASECERT’)

Begin

DropCertificateMYDATABASECERT

CREATECERTIFICATEMYDATABASE_CERTWITHSUBJECT=‘MYDATABASECERT’

End

Else

begin

Create CERTIFICATEMYDATABASE_CERTWITHSUBJECT=‘MYDATABASECERT’

End

Go

3-Backup certificate

Backup certificateMYDATABASE_CERTtofile=‘G:\T\MYDATABASECERT_CERT’with private key(file=‘G:\T\MYDATABASECERT_KEY’, encryption bypassword=‘@dmin$afeP@$$w0rd’);

4- Encrypt database with my created Certificate

create databaseencryptionkeywithalgorithm=aes_128

encryption byservercertificateMYDATABASECERT;

alter database{Database Name}setencryptionon;

5- take full backup 4 ur database with over write option

6- Take log backup 4 ur database with appen option

7- delete ur database orders_book and delete ur certificate on root master–>security–>certificate

8- try to restore ur backup normally — u will get error no certificate

Fix the Error

 

go

create certificateMYDATABASE_CERTfromfile=‘G:\T\MYDATABASECERT_CERT’with private key(file=‘G:\T\MYDATABASECERT_KEY’,

decryption bypassword=‘@dmin$afeP@$$w0rd’);

Now try to restore ur backup again u will successfuly restore

Special thanks to the engineer Ahmed Abdel Moteleb

 

 

 

 

 

 

 

 
Leave a comment

Posted by on October 20, 2012 in Encryption Tips

 

Drop All Databases in SQL Server

EXEC sp_MSforeachdb ‘
IF DB_ID(”?”) > 4
BEGIN
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ?
END’

 
Leave a comment

Posted by on October 12, 2012 in Script