How to Change the Default Database Location

Problem :

After I Create Cluster and I make 2 Share Disk one for Data and the another for Logs I forget to Customize the location For the database What can I do to Change this Location ?

Salutation : 

You have two ways the first by T-SQL and the Second is Wizard Steps :

First Way:

A- Change the Default location for data file :

EXEC xp_instance_regwrite







B- Change the Default location for Logs file :

EXEC xp_instance_regwrite







Second Way:

Open the management Studio >>> Write Click on the Server >>> Select properties


Then Select Database Setting >>> you Will See Database Default Location


Cluster Firewall issue – An item with the same key has already been added

Problem :

When I Prepare the Test Environment to the SQL Server Clustering  Under Windows Server 2008R2 Clustering I get this issue :

Clutser validation Error

After I Create the Cluster must be make validate the Configuration when I check this Configuration I get this issue Validate Windows Firewall Configuration in the detail of the error An item with the same key has already been added

Reason of the problem :

This is typically because the NIC GUID is duplicated between cluster nodes. When I won’t to create 3 Virtual machine (Cluster-PC , Node 1 , Node 2 )

I Create the First Virtual machine and take it Twice copy this is the issue because when I make Copy the Node 1 and Node 2 Created with the same Physical Address (MAC IP) so the Conflict in this area

the first solution came to my brain it’s I will Format Node 1 and I will install it from the first (Windows) but I Check more time to another Solution and I defected good Solution by Asking my friend System Administrator Eng.Hamada Abdelkader

Solution :

1- open the Run

2- Write this Command (sysprep)

3- Enter

this Command is System preparation it will male  preparation to the network after this Command finish I Check the mac IP very good new Address and I run the Validate Configuration for the Windows Cluster again and it successful 100%

Thanks For Eng.Hamada Abdelkader


Eng.Mostafa Elmasry

Database Administrator

What is SQL Server events Captured in SQL Server transaction Logs

Q : Are all SQL Server events captured in the SQL Server Transaction Logs ?

A : The start and end of each transaction is saved in transaction logs

– Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables .

– Every extent and page allocation or deallocation .

– Creating or dropping a table or index.

– Rollback operations .

– Transaction SID .


How to install Adventure Work Database

Problem :

When I download Database Adventure Work  I see it MDF file only ok but now how can I attach this database without LDF file . SQL Server give me ERROR

AdventureWork Without LDF File





Solution : 

to fix this error and install the database on the production we will create the LDF file by this script .


Create Database AdventureWorks2008R2

ON (FILENAME=‘E:\AdventureWorks2008R2_Data.mdf’)


by this Script I Create Database AdventureWorks2008R2 and put the File name for the MDF file the ( Full path ) then I make REBUILD for the LOG by this Command  (ATTACH_REBUILD_LOG)

Now after you EXCE this Query Check the path of the MDF file you will see the new File LDF for the database adventure work also you check the Databases on SQL Server management studio you will find the Database ADVENTUREWORK2008R2

Regards ,

Eng. Mustafa Elmasry

Database Administrator



How to transfer Login and password from Server to Server in MSSQL 2008

Problem :

When you install new instance SQL Server in New Server you won’t to Copy all users from Server A to the New Server B so let’s se the solution

Solution :

Microsoft make good solution about this problem  Check this link :


Microsoft make Script to run it on old server to generate Script for your all Users and passwords then take this Script and run it on your new server