Powershell and SQL Server – 3- Access SQL Server Instance – List Databases and Properties with Invoke-Sqlcmd

List out database name from a SQL Instance if (-not (Get-Module SQLPS)) { Import-Module SQLPS } $SqlServerName=”LENOVO-PC\SQL2016″ $result=Invoke-Sqlcmd -ServerInstance $SqlServerName -Database master -Query “SELECT * FROM sys.sysdatabases” -QueryTimeout 0 foreach ($dbname in $result.name) { $dbname } or foreach ($dbname in $result) { $dbname.name } List Database properties for each database if (-not (Get-Module SQLPS)) {Import-Module […]

Powershell and SQL Server – 2- Connect to SQL Server Instance

There are different connection options for powershell scripts to connect to SQL Server Invoke-sqlcmd Windows Authentication Invoke-Sqlcmd SQL Authentication Invoke-Sqlcmd with User Login Authentication Prompt SQLConnection.connectionstring property SMO Connection Invoke-sqlcmd Windows Authentication if (-not (Get-Module SQLPS)) { Import-Module SQLPS } $result=Invoke-Sqlcmd -ServerInstance LENOVO-PC\SQL2016 -Database master -Query “select @@servername as ServerName” -QueryTimeout 0 $result Invoke-Sqlcmd SQL […]

The server network address “TCP://SQL Server:5022” cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Error : The server network address “TCP://SQL Server:5022” cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418) To over come the issue follow the below steps. Drop the Mirroring Endpoints from Principal and Secondary. select […]

Restore Database Backup from Azure Storage URL

In this topic we will restore a database from an on-premises backup apply to restoring a database from a cloud backup file. Before you start the Restore Database Process from Azure Cloud Storage,You need Azure Storage Account, Account Key,and Credential Create Credential CREATE CREDENTIAL AzureStorageCred WITH IDENTITY = ‘dbproxazurestorage’ ,SECRET = ‘HJjm/dfds+MFFmpBLQwiWR+fdsafadfsd+XDAz8uLX/asdasssdfgdsfdsfas==’; Right Click the […]

Powershell and SQL Server – 1- Import SQLPS Module

Ways to connect to SQL Server using Powershell Microsoft recommend to use SQLPS Module to manage SQL Server.Import the SQLPS Module into Windows Powershell environment Check if SQLPS module is registered and available Get-Module Import SQLPS Module to Powershell console Import-Module SQLPS Ignore Warnings Ignore the Module Load if already loaded if (-not (Get-Module SQLPS)) […]

Backup On-Premises Database To Azure Cloud Storage

In this article we will see how to do SQL Database backup directly to Azure Cloud Storage. Microsoft Introduced URL  Backup from on-premises to Azure Cloud Storage with SQL Server 2012 . I assume that you have a container set up in Azure Blob Storage Service. Configuration: Create SQL Server credentials for authentication to the Microsoft Azure Blob […]

Enable / Disable Users In Database

I normally see this issue when connect permission is removed/revoked from the database USE DatabaseName GO GRANT CONNECT TO LoginName You can check which users in a database have the CONNECT permission by executing this T-SQL: USE DatabaseName GO SELECT name, hasdbaccess FROM sys.sysusers WHERE name = ‘LoginName’

Restart an Interrupted Restore Operation

What exactly we normally do when a restore is interrupted, we restore the database again. What if you can restart the restore where it was interrupted? Quite Good !Right Microsoft SQL Server Added the option Restart in the Restore command from SQL Server 2012.The  WITH RESTART option checks the checkpoint of the interrupted backup and […]

Dynamic Data Masking

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a security feature that hides the sensitive data in the result […]

Get List of ASSEMBLY and PATH

Get List of assembly and there paths SELECT assembly = saa.name, path = af.name FROM sys.assemblies AS sa INNER JOIN sys.assembly_files AS af ON sa.assembly_id = af.assembly_id WHERE sa.is_user_defined = 1;

Script – Server Level – Database Level Permissions

The Script scripts all the permission granted to each login on Server and Database Level. DECLARE @Start int=1 DECLARE @End int DECLARE @DatabaseName varchar(100) DECLARE @cmd nvarchar(4000) DECLARE @Permission Table (ID int Identity,Script varchar(max)) DECLARE @DbList Table (id int identity,DBName varchar(100)) INSERT INTO @DbList(DBName) SELECT name FROM sys.databases where name not in (‘master’,’model’,’msdb’,’tempdb’) and state=0 […]

External Tables : Incorrect syntax near ‘CREDENTIAL’

I was trying to work through External Table in SQL Server 2016 and was configuring the prerequisites , this is when i got stuck with error while creating EXTERNAL DATA SOURCE CREATE EXTERNAL DATA SOURCE sql2016ds WITH ( TYPE = HADOOP, LOCATION = ‘wasbs://containername@account.blob.core.windows.net/’, CREDENTIAL = cred_azstorage); GO Error Msg 102, Level 15, State 1, […]