Thursday, May 25, 2017

Generating Transaction Log script

Production transaction logs are backed up every 15 or 30 mins. We have a full database backup daily. When we need to restore a database point in time. We will need to restore transaction logs. It will take time to restore each transaction log if we have more than 20 transaction log a day.
I wrote the following script to generate Transaction log restore script which I can use to restore T log faster after full backup is restored. 

--***********Read this before running the script**************************************
-- The following script generates Transaction Log restore script.
-- If database crashed , we will need to restore a full database backup and Transaction logs.
-- After restoring a full database backup, we will need to restore all the good transaction logs which are backed up after the full back up is taken. 

-- This script will generates T Log restores scripts. We copy the scripts which is generated in message window.

-- Step1 . We will need to enable xp_cmdshell to 1 by running the following script one time only . After restore is done, we need to disbale xp_cmdshell

--sp_configure xp_cmdshell,1
--go

--reconfigure
--go

-- Step 2 . We need to change 3 variable values in the script, @BP,@BP2,@DatabaseName
-- @BP and @BP2 values are the Transaction Log backup location path and both values must be the same 
-- @DatabaseName is the name of the database that you are restoring


Declare @BP varchar(max)
Declare @GF varchar(max)
Declare @sql nvarchar(max)


--************************************************************
--Change the backup path of Transaction Logs here
--************************************************************
set @BP = 'C:\BackUp'

--************************************************************
--************************************************************


--construct sql for getting files
set @GF = ''' dir '+  @BP +'''' 
set @sql = 'EXEC MASTER..XP_CMDSHELL'+ ''+ @GF + ''

--Create a staging table
CREATE TABLE GetBackupFiles (Files VARCHAR(500))
INSERT INTO GetBackupFiles
Exec sp_executesql @sql

--Cleaning up the output from file list
--delete all directories
DELETE GetBackupFiles WHERE Files LIKE '%
%'

----delete all informational messages
DELETE GetBackupFiles WHERE Files LIKE ' %'

----delete the null values
DELETE GetBackupFiles WHERE Files IS NULL

--add FileDate column
ALTER TABLE GetBackupFiles add FileDate DateTime
GO

--split dateinfo
UPDATE GetBackupFiles SET FileDate =LEFT(files,20)

--get rid of dateinfo
UPDATE GetBackupFiles SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces
UPDATE GetBackupFiles SET files =LTRIM(files)


Declare @Databasename varchar(200)
Declare  @BP2 as varchar(max)

--*************************************************************
--Change the backup path here again same backup path as @BP
--Change the Databaes name here
--*************************************************************

set @BP2 = 'C:\BackUp'
Set @Databasename = 'MyDatabase'

--**************************************************************
--**************************************************************


-- Generate Restore script for Transaction Log
Declare @FN varchar(max)

Declare GetFileNames Cursor LOCAL for


--************************************************************************
--************************************************************************
-- Here we change the timestamp of the full database backup***************
--************************************************************************
--************************************************************************

select RIGHT(files,LEN(files) -PATINDEX('% %',files)) from GetBackupFiles
where FileDate >= '2017-05-24 23:45:00.000'
order by FileDate asc

--************************************************************************
--************************************************************************


Open GetFileNames;
Fetch GetFileNames into

@FN

WHILE @@FETCH_STATUS = 0
Begin

----Build Transaction Logs restore script
Print 'RESTORE DATABASE ' + @Databasename + ' FROM DISK = '''+ @BP2+@FN+' '+ ''' WITH NORECOVERY'

Fetch GetFileNames into
@FN


End

Close GetFileNames
Deallocate GetFileNames


--Drop staging table
Drop table GetBackupFiles


I gave credit to http://www.tek-tips.com/viewthread.cfm?qid=1278283

Thank you for reading.

Thursday, April 6, 2017

Fix for “The target principal name is incorrect. Cannot generate SSPI context.”

After changing a service account running SQL server, I get an error when I connect to SQL server from Management Studio from my laptop or other servers. The error is below.

“The target principal name is incorrect.  Cannot generate SSPI context.”

To Fix this,

You need to register the service account in SPN.

MSSQLSvc/ Server1.domain.us.dom Domain\ServiceAccount
MSSQLSvc/ Server1.domain.us.dom:1433 Domain\ServiceAccount


After SPN is registered successful, the error is resolved. Thank you.

Tuesday, March 28, 2017

xp_logininfo

xp_logininfo 'Domain\ADGroup',members

xp_logininfo returns members of the AD group in SQL server. I use this command to check who has access to a database server in the AD group.

Friday, March 24, 2017

Renaming NIC , NetBios Setting, Disabling Protocols

The objective of powershell script is to automate renaming NIC, changing NetBios setting in Private and Public NIC and Disabling some protocols in Private NIC.


I created the following powershell scripts to automate the changes. The changes are necessary to set up AlwaysOn availability group severs in my process. Thank you for reading.








import-module Netadapter

Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | Select InterfaceAlias, IPAddress

#Rename NIC Public 10.*
$PubNic=Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | where IPAddress -like '10.*'|Select InterfaceAlias
$PubNicName= $PubNic.InterfaceAlias
$NewPublicNicName = "Public NIC"
Rename-NetAdapter -Name $PubNicName -NewName $NewPublicNicName

#Rename NIC Private 192.*
$PriNic=Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | where IPAddress -like '192.*'|Select InterfaceAlias
$PriNicName= $PriNic.InterfaceAlias
$NewPrivateNicName ="Private NIC"
Rename-NetAdapter -Name $PriNicName -NewName $NewPrivateNicName


#In Public NIC, Enable NetBios over TCP
#0: Enable Netbios via DHCP.1: Enable Netbios on the interface.2: Disable Netbios on the interface.

$PubNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '10.*'}
$PubNicConfig.SetTcpipNetbios(1)
#Verify the changes
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '10.*'}|Select IpAddress,Description,TcpipNEtbiosOptions |format-list


#In Private NIC, Enable NetBios over TCP
#0: Enable Netbios via DHCP.1: Enable Netbios on the interface.2: Disable Netbios on the interface.

$PriNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}
$PriNicConfig.SetTcpipNetbios(2)
#Verify the changes
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}|Select IpAddress,Description,TcpipNEtbiosOptions |format-list


#Private NIC, Do Not Register this connection  address in DNS
$PriNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}
$PriNicConfig.SetDynamicDNSRegistration($false,$false)  
# Verify Wins, Netbios setting
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'} |Select * |format-list


#Disable Protocols Public and Private protocols

#Disable Client for Microsoft Network
Disable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_msclient
#Disable File and Printer Sharing for Microsoft Networks
Disable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_server
#Enable Ipv6 for both Private and Public NIC
Enable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_tcpip6
Enable-NetAdapterBinding -Name 'Public NIC' -ComponentID ms_tcpip6
#Verify the changes
Get-netadapterbinding -Name 'Private NIC'
Get-netadapterbinding -Name 'Public NIC'

Changing NIC Bindings Order using Powershell Script

NIC Bindings Order


I did a lot of research on how to change NIC Binding Order using powershell. I am changing the binding Order to meet the requirement of Always On server setup. I created the following powershell script which changes registry of NIC binding. After you run the script you will not see the binding order is changed in UI but it is actually updated when you run IP config. I believe that the setting not being updated in UI is a bug in Windows.

In Adapters and Bindings tab, I move Public NIC on top of Private NIC. The powershell script will update 3 places in - Bind , Export and Route under CurrentControl set in Registry.



#-------------------------Public Setting ID------------------------------------------------#

$PublicSettingID = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter "IPenabled = $true" | where {$_.IpAddress -like '10.*'}|select Settingid
$PubSettingID = $PublicSettingID.Settingid
Write-host $PubSettingID

#-------------------------Private Setting ID------------------------------------------------#

$PrivateSettingID = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter "IPenabled = $true" | where {$_.IpAddress -like '192.*'}|select Settingid
$PriSettingID = $PrivateSettingID.Settingid
Write-host $PriSettingID

#---------------------------------BIND------------------------------------------------------#
$BindNewOrder = @()
$writereg = $null
$Bindkey = 'HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage'
$Bindvalue = 'Bind'

#get values in registry
$BindOldOrder = (Get-ItemProperty $Bindkey $Bindvalue).$Bindvalue 
write-host $BindOldOrder

$BindPubSettingID='\Device\'+$PubSettingID
$BindPriSettingID='\Device\'+$PriSettingID

#Order
if ($BindOldOrder -contains $BindPubSettingID) {$BindNewOrder += $BindPubSettingID}
if ($BindOldOrder -contains $BindPriSettingID) {$BindNewOrder += $BindPriSettingID}
if ($BindOldOrder.count -gt $BindNewOrder.count) {$BindNewOrder += $BindOldOrder}


$BindNewOrder = $BindNewOrder | select -unique
Write-host $BindNewOrder

#Change registry valules
Set-ItemProperty -path $Bindkey -Name $Bindvalue -Value $BindNewOrder

#>

#----------------------------EXPORT----------------------------------------------------#

$ExportNewOrder = @()
$Exportkey = 'HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage'
$Exportvalue = 'Export'
$ExportPubSettingID = '\Device\Tcpip_' + $PubSettingID
$ExportPriSettingID = '\Device\Tcpip_' + $PriSettingID

Write-host $ExportPubSettingID
Write-host $ExportPriSettingID

#get values in registry
$ExportOldOrder = (Get-ItemProperty $Exportkey $Exportvalue).$Exportvalue 
write-host $ExportOldOrder

#Order
if ($ExportOldOrder -contains $ExportPubSettingID) {$ExportNewOrder += $ExportPubSettingID}
if ($ExportOldOrder -contains $ExportPriSettingID) {$ExportNewOrder += $ExportPriSettingID}
if ($ExportOldOrder.count -gt $ExportNewOrder.count) {$ExportNewOrder += $ExportOldOrder}


$ExportNewOrder = $ExportNewOrder | select -unique
Write-host $ExportNewOrder
Set-ItemProperty -path $Exportkey -Name $Exportvalue -Value $ExportNewOrder


#-----------------------------ROUTE-------------------------------------------------------#

$RouteNewOrder = @()
$Routekey = 'HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage'
$Routevalue = 'Route'
$RoutePubSettingID = '"' + $PubSettingID + '"'
$RoutePriSettingID = '"' + $PriSettingID + '"'

Write-host $RoutePubSettingID
Write-host $RoutePriSettingID

#get values in registry
$RouteOldOrder = (Get-ItemProperty $Routekey $Routevalue).$Routevalue 
write-host $RouteOldOrder

#Order
if ($RouteOldOrder -contains $RoutePubSettingID) {$RouteNewOrder += $RoutePubSettingID}
if ($RouteOldOrder -contains $RoutePriSettingID) {$RouteNewOrder += $RoutePriSettingID}
if ($RouteOldOrder.count -gt $RouteNewOrder.count) {$RouteNewOrder += $RouteOldOrder}


$RouteNewOrder = $RouteNewOrder | select -unique
Write-host $RouteNewOrder
Set-ItemProperty -path $Routekey -Name $Routevalue -Value $RouteNewOrder


After you run the script, you will see the output similar to below.



The changes takes affect on NIC Binding but UI is not updated. It think it is a bug in Microsoft windows. The UI does not change even after rebooting the server.When you run Ipconfig /all on the server, it returns public ip address then private ip address.







Changing NIC Provider Order with Powershell Script

NIC Provider Order


Hello 2017! I hope everyone had a great 2016. My son was born in December 2016 and 2016 was the best year for me.

Now, I am back to my full time job. This is my first blog of 2017.

Here we go.....

Before configuring Availability Groups, we will need to configure NIC Provider Order and Binding Order. Usually, I changed them manually. If we are changing it in multiple servers, it will take time to configure them. So, I created a powershell script to change NIC provider order.

Manual Steps - Screenshots below are the steps how to change NIC provider order manually.


In Network and Sharing,
Click on Public NIC, press Alt + N , Click on Advanced Settings




Move Microsoft Windows Network on the top



The powershell script below is how to change Provider Order. I want to move Microsoft Windows Network on top of the rest.I am planning to use it in VM template deployment. Thank you for visiting my page.

$newproder = @()
$writereg = $null
$key = 'HKLM:\SYSTEM\CurrentControlSet\Control\NetworkProvider\Order'
$value = 'ProviderOrder'

#get values in registry
$proder = (Get-ItemProperty $key $value).$value | % {$_.split(",")}

#Order
if ($proder -contains "LanmanWorkstation") {$newproder += "LanmanWorkstation"}
if ($proder -contains "RDPNP") {$newproder += "RDPNP"}
if ($proder.count -gt $newproder.count) {$newproder += $proder}

$newproder = $newproder | select -unique

#Make a string
$newproder|ForEach-Object{$writereg += $_+","}

$writereg = $writereg.substring(0,$writereg.length-1)

#Change registry valules
Set-ItemProperty -path $key -Name $value -Value $writereg

I will reboot the server to take affect the changes. 

My reference is 

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/9a91540a-8c24-4f97-a433-70e4c9c7b709/change-network-providerorder-using-powershell?forum=ITCG

Tuesday, November 15, 2016

Reading Transaction Log

Someone or a service deleted a record from a table. We will need to know when it was deleted and who deleted the record. I read every transaction log between a specific time to find out if there was a delete statement in transaction log.  Transaction logs are located on E:\

Reading Transaction Log

Option 1

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID],LEFT ([Description], 40) AS [Description]  FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE Operation = 'LOP_DELETE_ROWS'
GO

Option 2

In the script below, we passed table name and operation

WITH CTE
as
       (SELECT [Transaction ID], count(*) as DeletedRows
       FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
       WHERE Operation = ('LOP_DELETE_ROWS')
       AND [PartitionId] = (SELECT sp.partition_id
                            FROM sys.objects so
                            INNER JOIN sys.partitions sp on so.object_id = sp.object_id
                            WHERE name = 'TESTTABLE')
       GROUP BY [Transaction ID]
       )
SELECT [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as LoginName, DeletedRows
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a
INNER JOIN cte on a.[Transaction ID] = cte.[Transaction ID]
WHERE Operation = ('LOP_BEGIN_XACT')

Option 3

If we know specific transaction SID, we can find who ran the delete.

SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
[End Time],
[Transaction Name],
LEFT ([Description], 40) AS [Description],
SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?],
[PartitionID],
[Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
FROM
     fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE [Transaction ID] in('0000:0084ff66','0000:0084ff66','0000:0085002c')
GO

I used above three queries to find out who deleted a record and delete time.

Thank you for reading.


How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...