Category: SQL Server

  • T-SQL Script to check Powershell Version

    You can write $host command on powershell prompt to check powershell version or you can execute the below T-SQL script to check the powershell version.

    declare @regkeyval varchar(20), @value varchar(255), @rc int
exec @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\PowerShell\1\PowerShellEngine',
  @value_name='PowerShellVersion',
  @regkeyval=@value output


set @value =isnull(@value,'-') 

if @value <> '-'
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,@value as PSClientVersion 
,'PS is installed' as PSStatusCheck 
else
insert into #OUTPUT(SQLInstanceName,WindowsServerName,PSStatusCheck)
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,'PS nt installed' as PSStatusCheck

  • MongoDB Installation on Windows Server

    You can download the MongoDB https://www.mongodb.org/downloads. Once you download the MongoDB (Version 2.6.7), follow below step by step to process to install MongoDB. Double click on mongodb-win32-x86_64-2008plus-2.6.7-signed.msi and follow below screen.

     

     

    You will see the below files into MongoDB folder.

  • Script to list out SQL Logins and Database User Mappings

    You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings.

    Syntax
    sp_msloginmappings @Loginname , @Flags

    @Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
    @Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0

    use master
go
exec sp_msloginmappings 'sa', 0

    

    Image2
    

    use master
go
exec sp_msloginmappings 'sa', 1

    

    Image1
    

    In case you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or powershell. You can use the below script.
    

     create table #loginmappings(
  LoginName  nvarchar(128) NULL,
  DBName     nvarchar(128) NULL,
  UserName   nvarchar(128) NULL,
  AliasName  nvarchar(128) NULL
 )  

 insert into #loginmappings
 EXEC master..sp_msloginmappings

 select * from #loginmappings

 drop table #loginmappings

    

    

    Before you hand an AI agent a login, run this
    

    This script answers the first question every AI deployment has to answer: what can this identity actually touch? Before an agent, a service account, or a RAG pipeline gets a login, map its database user mappings with sp_msloginmappings and confirm least privilege — no mapping, no access. Identity is the “I” in my AI risk framework: trusted data access, least privilege, auditability. The framework: R-I-S-K-S: The 5-Dimension Framework for AI Risk Management

  • How to restart an Interrupted Database Restore in SQL Server?

    Steps to restart an Interrupted Database Restore in SQL Server

    There may scenario when you are restoring VLDB database backup and the restore database command interrupted due network failure, power failure, SQL Server Service restart or cluster failover. In this kind of scenario you can easily start the interrupted restore from the point of failure using WITH RESTART command.

    Before you start restoring the database using WITH RESTART option check database is in Restoring State not in any other state.

    You can check the database state either from SSMS object explorer or by executing below T-SQL command

    select name,state_desc from sys.databases

    Once confirmed that database is in restoring state execute the backup command WITH RESTART clause

    RESTORE DATABASE [AdventureWorks]
FROM DISK ='D:\Backup\AdventureWorks.bak'
WITH RESTART, STATS = 20
GO

  • T-SQL Script to check SQL Server Instance TCP/IP Port Number

    SQL Server Error Log

    -- Check the error log for port number
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO

    Executing DMV Script

    -- you must connect remotely using TCP/IP
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

    For Dynamic Port Number
    — Read the registry for port number

    -- Execute below script if SQL Server is configured with dynamic port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

    For Static Port Number

    -- Execute below script if SQL Server is configured with static port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO