catalog

This Catalog is the index for different blog posts in structured order

Azure
. Azure.Pricing: https://vijredblog.wordpress.com/2016/09/15/azure-database-pricing-tier-selection/

. Azure.Powershell :
Frequently used Azure SQL Powershell Commands: https://vijredblog.wordpress.com/2017/07/27/frequently-used-azure-powershell-commands/
Point in Time Restore on Deleted DB: https://vijredblog.wordpress.com/2018/05/23/how-to-point-in-time-restore-azure-sql-database-after-deleting-the-database/

. Azure.SQL
. AZURE.SQL.Frequently User SQL Commands : https://vijredblog.wordpress.com/2016/10/27/sql-commands-in-azure/

 

Best Practices: https://vijredblog.wordpress.com/2018/06/22/best-practices-being-devops-in-it/

 

Commandline
Commandline.Groupmembers:  https://vijredblog.wordpress.com/2018/07/10/how-to-operate-multiple-objects-in-ssms-f7/

 

SQLServer

. SQLServer.FrequentlyUsedCommands: https://vijredblog.wordpress.com/2018/06/22/frequently-used-mssql-commands/

. SQLServer.Patch
SQL Server Patches: https://vijredblog.wordpress.com/2018/06/18/sql-latest-available-patch-reference/

. SQLServer.SSMS 
Object Explorer Details: https://vijredblog.wordpress.com/2018/07/10/how-to-operate-multiple-objects-in-ssms-f7/

 

Advertisements
Posted in Azure, Powershell, SQL, Uncategorized | Tagged , | Leave a comment

How to operate multiple objects in SSMS (F7)

While working on a service restoration, we had to resume data movement on 100+ random databases. Generating TSQL is the best option but UI will be very quick and reliable.

Biggest challenge with SSMS UI will be to perform the action for each DB one at a time. I learnt about Object Explorer Details (F7) from my college (Thanks John)!

Simple Demo to Script 4 different tables at the same time!

1. Connect to the Server in SQL Server Management Studio (SSMS)
2. Click F7 (You can also click on View tab + Object Explorer Details)
ObjectExplorerDetails1

3. Select Tables from Object Explorer tab. Hold Control and select required table.
4. Right click -> Script Table As -> Create To -> New Query Editor Window
ObjectExplorerDetails2.jpg

Create Table script is generated for all selected tables!

Posted in SQL, SQL Server, SSMS, Uncategorized | Tagged , , | 1 Comment

Frequently used MSSQL Commands

 


-- MSSQL sleep command 
-- wait for a minute
WAITFOR DELAY '00:01'
-- wait for a second
WAITFOR DELAY '00:00:01'
-- wait for 200 ms
WAITFOR DELAY '00:00:00:200'



-- Transaction during DML 
BEGIN TRANSACTION

UPDATE Table SET Col2 = 'newValiue'
WHERE Col1 like 'mySearchCriteria'

IF (@@ROWCOUNT < 600 and @@ERROR = 0 )
BEGIN
	COMMIT TRANSACTION
	PRINT 'COMMIT'
END 
ELSE
BEGIN
	PRINT 'ROLLBACK'
	ROLLBACK TRANSACTION
END 



-- Server Name
SELECT @@SERVERNAME


-- Port- Find SQL Port Number 
SELECT local_tcp_port FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID




--


Posted in commands, SQL Server, Uncategorized | Tagged , , , , , | 1 Comment

Best practices being DEVOPS in IT

Index: MSSQL Index naming recommendations:

 

PK_ for primary keys
UK_ for unique keys
IX_ for non clustered non unique indexes
UX_ for unique indexes
<index or key type>_<table name>_<column 1>_<column 2>_<column n>

 

Posted in SQL Server, Uncategorized | Tagged , , | 1 Comment

Command to find domain group members


# list of users in a given group 
net group   /domain


# List of local group members 
net localgroup 

# PowerShell way to find group members 
dsquery group -name  | dsget group -members -expand


Related: https://vijredblog.wordpress.com/2016/04/21/activedirectory-group-user-membership-validation-tool/ (Active Directory Helper)

Posted in commands, Productivity, Uncategorized | Tagged , , , , , | Leave a comment

SQL latest available patch reference

Best source for latest SQL Server patches: https://sqlserverbuilds.blogspot.com/

Ref 2: https://technet.microsoft.com/en-us/library/ff803383.aspx (Hotfix information is not updated)

Option 3: https://sqlserverupdates.com/

Posted in SQL Server, Uncategorized | Tagged , , | 1 Comment

How to Point in time Restore Azure SQL Database after deleting the database

Azure SQL Database has feature to restore the database to point in time when the database is live. When we have to move the database to different Server we end up deleting the database from old server, we can not perform point in time restore!

About an year back when we had this situation we end up working with Microsoft to perform point in time restore on a deleted DB but this is available through Powershell command now.

SampleCode for reference;


#
$Resourcegroup="ResourceGroupName"                                                                          
$RestoredDBName = "DBName_05172018_0100UTC" # New Name                                                                     
$DBEdition = "Standard"                                                                                              
$DBTier = "S3"                                                                                                      
$RestoreTime = "2018-05-17 01:00"  # Time in UTC                                                                                  
$Servername = "AzureSQLDBServerName"                                                                                         
$DBName = "DeletedDBBame"

Login-AzureRmAccount                                                                                                
Select-AzureRmSubscription -SubscriptionId $SubscriptionId 

$DeletedDatabase = Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $ResourceGroup -ServerName $ServerName | Where-Object {$_.DatabaseN
ame -eq $DBName}

Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup -DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $DeletedDatabase.ResourceGroupName -ServerName $DeletedDatabase.ServerName -TargetDatabaseName $RestoredDBName -ResourceId $DeletedDatabase.ResourceID -Edition $DBEdition -ServiceObjectiveName $DBTier -PointInTime $RestoreTime
#

Ref: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-recovery-using-backups#point-in-time-restore

Posted in Azure, Powershell, SQL, SQL Server, Uncategorized | Tagged , , , , | 2 Comments