SELECT*

Click on the below link( dbatools ) and found "DownloadZip" under CODE. Once the zip folder is downloaded it will download as  "dbatools-development". and extract it. After that change the folder name from "dbatools-development" to "dbatools". And sometimes there is a possibility that dbatools folder contains another dbatools-development folder with in the folder,if that is the case please make there is only one "dbatools" folder not any duplicate folders with in it. dbatools In the search space search with "ISE" and choose PowerShell and run as administrator and run the below command. And it will give the paths of modules like below. You can find these many paths for modules and copy the dbatools folder from source to " C:\Program Files\WindowsPowerShell\Modules " folder  and run the below command in PowerShell and you can see the progress of installation and will successfully install it.

 The below command will allow fire wall rule to get results through ping command and to access the server remotely.

 I  go the below error in one our environments. Below are the troubleshooting steps. Run the below command find the session id that are using by that particular session KILL all the session KILL SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'LoginName' And then run the below command USE [master] GO DROP LOGIN [LoginName]

The below query will provide us the amount of free space that exist at database data file level. CREATE TABLE # FileSize (dbName NVARCHAR( 128 ), [FileName] NVARCHAR( 128 ), physical_name varchar ( 150 ), type_desc NVARCHAR( 128 ), CurrentSizeMB DECIMAL ( 10 , 2 ), FreeSpaceMB DECIMAL ( 10 , 2 ) ); INSERT INTO # FileSize (dbName,[FileName],physical_name,type_desc, CurrentSizeMB, FreeSpaceMB) exec sp_msforeachdb 'use [?]; SELECT DB_NAME() AS DbName, name AS FileName, physical_name, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1) and type_desc=''ROWS'';' ; SELECT * FROM # FileSize WHERE dbName NOT IN ( 'distribution' , 'master' , 'model' , 'msdb' ) and type_desc = 'ROWS' and physical_name LIKE 'E:\%' --A...

Distribution Agent job failed with below erro message and the below workaround fixed the issue in my environment Error messages: The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074) USE Distribution GO SELECT STATUS,* FROM [dbo].[MSsubscriptions] WHERE PUBLISHER_DB= 'PublisherDBNameHere' use distribution go update MSSubscriptions set [status] = 2 WHERE PUBLISHER_DB= 'PublisherDBNameHere' And to check thestatus after above command use distribution go exec sp_browsereplcmds

The below powershell query will create a login it could be a windows login or SQL Server Authentication login we can create those login by using below query and add that particular login to particular role and can extract the info. If that particular login alredy exists in the environment the query will ignore or if you mention -FORCE parameter it will overwrite. cls $ServerList = @ ( "Instance1" , "Instance2" , "Instance3" ) $ServerList | ForEach -Object{ $indserver = $_ New-DbaLogin -SqlInstance $indserver -Login "DomainName\WindowsLoginGroup" | Select-Object ComputerName,InstanceName,SqlInstance,Name,LoginType,CreateDate,LastLogin,HasAccess,IsLocked,IsDisabled Set-DbaLogin -SqlInstance $indserver -Login "DomainName\WindowsLoginGroup" -AddRole sysadmin| Select-Object ComputerName,InstanceName,SqlInstance,LoginName,ServerRole Get-DbaLogin -SqlInstance $indserver -Login "DomainName\WindowsLoginGroup" | Select-Objec...

 The below database role will create a new role called db_executor, a role which gives permissions to all users to execute all the stored procedures of that particular database. Whoever associated to this role. -- Create a db_executor role CREATE ROLE db_executor -- Grant execute rights to the new role GRANT EXECUTE TO db_executor

Feature differences between Azure SQL databases and Azure SQL Managed instance . I have not mentioned here about the SQL Server in Azure Virtual machine as it is equal to on premises SQL Server and there would not be any much differences in the environment.

We can use below dbatools to restore database. Export-DbaUser will export all the respective database users to a spcific file. If you want all the user script need to add single file then user -FilePath parameter.If you want indivual files need to created user -Path parameter. cls Export - DbaUser ` - SqlInstance InstanceNameHere ` - Database DatabaseNameHere ` - FilePath C : \ Temp \ Users_DatbaseNameHere_InstanceNameHere. sql ` - Verbose In the below command -Path parameter you no need pass .BAK file it will automatically read file. -WithReplace option will overwrite the existing database. Restore - DbaDatabase ` - SqlInstance InstanceNameHere ` - DatabaseName DatabaseNameHere ` - Path H: \ TodayRestore ` - DestinationDataDirectory "F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatabaseName_Data.mdf" ` - DestinationLogDirectory "G:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatbaseName_Log.ldf" ` - Wit...

The below query will tell us the history of Transaction Log Backups. SELECT DISTINCT s.database_name, s.first_lsn, s.last_lsn,                         s.database_backup_lsn, s.backup_start_date, s.backup_finish_date,                         s. type , s.is_snapshot,y.device_type, y.logical_device_name, y.physical_device_name FROM msdb..backupset AS s INNER JOIN msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id where database_name = 'database_name'

The below query will tells us the which user has SHOWPLAN permission on the database level. DECLARE @ Table TABLE (ServerName varchar ( 50 ),DatabaseName varchar ( 50 ), [Name] varchar ( 50 ),PermissionName varchar ( 50 ),StateDesc varchar ( 50 )) INSERT INTO @ Table (ServerName,DatabaseName,[Name],PermissionName,StateDesc) EXEC sp_MSforeachdb 'USE ? SELECT ServerName=@@ServerName, DatabaseName=db_name(), p.name, perm.permission_name,perm.STATE_DESC FROM sys.database_permissions perm INNER JOIN sys.database_principals p ON perm.grantee_principal_id = p.principal_id WHERE perm.permission_name = ''SHOWPLAN'' AND [Name] in(''User1'',''User2'', ''User3'',''User4j'');' SELECT * FROM @ Table order by [Name] DESC

This query will tell you how to handle READONLY ROUTING in AlwaysOn. ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N 'TCP://NODE1.HADRDOMAIN.COM:1433' )); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N 'TCP://NODE2.HADRDOMAIN.COM:1433' )); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ( 'NODE2' , 'NODE1' ))); ALTER AVAILABILITY GROUP AvailabilityGroupName MODIFY REPLICA ON N 'NODE2' WITH...

  The below query will extract Editions,Version and Service Pack information of all the SQL Server Instances. In the server list array provide SQL Server instance information, if you provide windows server name it might throw error or will skip that paritcular server information. cls Import-Module sqlps -DisableNameChecking #[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Smo')|Out-Null $Counter =0 $serverlist = @ ( 'SQLServerInstance1' , 'SQLServerInstance2' , 'SQLServerInstance3' ) #ForEach-Loop start $serverlist | ForEach -Object{ #$x = 1 #$perc=[math]::round($x/$serverlist.count) $indsserver = $_ $Server = New-Object Microsoft.SQLServer.Management.Smo.Server $indsserver #$Server.Information.Properties|Select-Object Name, Value $netName = ( $Server .Information.Properties| ?{ $_ .Name -like 'Netname' }).VAlue $product = ( $server .Information.Properties | ?{ $_ .Name -like 'Product' }).VAlue...

1) First we need to create a storage account ,container to place the .BACPAC file in Azure. 2)  In this container we will place the .BACPAC file of Azure SQL database. 3) Create the backup file of Azure SQL Database, below image will tell you. *for bigger images click on the images below, it will pop in new window 4) Sometimes we can also use some other Third party tool   with which we can     take the Azure SQL Database backup and can save it to your local drive. However I have explained here, how to take the Azure SQL Database backup in Azure Portal. 4) Move back to SQL Server from Azure SQL Database and see the progress as mentioned below. 5) After downloading the .BACPAC file from your azure storage to your local drive. You can restore this .BACPAC file into your local SQL Server. Below images are self explanatory.

 To access Azure SQL Database from SSMS through "Active Directory-Universal with MFA Support" authentication please follow the below steps. 1) Create an user Azure Active Directory level with MFA authentication. a) Search Azure Active Directory in market place b) Click add user so you can see the below image. c) Provide initial password and create the user. d) Now in the below image test user has been created 2) Add this test user to Directory Readers and Directory Writers role and also Global Administraor though the below image does not show it you can add it without missing. Please see the image. Chose those two roles and click on Add button and refresh. And choose authentication method too . b) Now you could see test user allocated those two roles. and very important to access Azure Active directory users. c) Now go to authentication provide users or your phone number(if you working at home for practice) after providing the phone number with +91 XXXXXXXXXX click on save b...

The below script will generate backups for all the databases in a single instance. SET NOCOUNT ON DECLARE @ servername varchar ( 50 ) DECLARE @ databasename varchar ( 50 ) DECLARE @ path VARCHAR ( 50 ) DECLARE @ stmt varchar ( 4000 ) DECLARE @ I INT DECLARE @ Count INT DECLARE @ Databases table (Sno INT IDENTITY ( 1 , 1 ),[Name] varchar ( 50 )) INSERT INTO @ Databases ([Name]) SELECT Name FROM sys.databases WHERE State = 0 and database_id not in ( 2 ) SET @ I = 0 SELECT @ Count = COUNT ( * ) FROM @ Databases SET @ servername = HOST_NAME() --Provide path name here dont give \ in the end. SET @ path = '\\ramesh\LS' WHILE( @ I <@ Count ) BEGIN SET @ I =@ I + 1 SELECT @ databasename = [Name] FROM @ Databases WHERE Sno =@ I SET @ stmt = 'BACKUP DATABASE ' + '[' +@ databasename + ']' + ' TO DISK= ' + '''' +@ path + '\' +@ servername + '_' +@ databasename + '_' + REPLACE (...

 The below query will gives you the SSRS subscription scheduled information from SQL Server end. DECLARE @ jobName VARCHAR ( 100 ) SET @ jobName = '52B9C43E-748E-40B9-8FC9-A95369E2ED73' SELECT jobs.date_created,JOBS.[NAME],jobschedule.next_run_date, NextRunTime = stuff(stuff( right ( '00000' + cast (jobschedule.next_run_time as varchar ), 6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) ,schedules.[enabled] AS ScheduleEnable from msdb.dbo.sysjobs jobs INNER JOIN msdb.dbo.sysjobschedules jobschedule ON jobs.JOB_ID = jobschedule.JOB_ID inner join msdb.dbo.sysschedules schedules ON schedules.schedule_id = jobschedule.schedule_id WHERE jobs.[name] =@ jobName The below query will tell you about which sql server agent job is running which report and its schedules SELECT distinct sj.[name] AS [Job Name], rs.SubscriptionID, c .[Name] AS [Report Name], c .[Path] FROM msdb..sysjobs AS sj INNER JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CA...

 The scenario is describing about how to take the backup from the SQL Server in Azure Virtual machine and store the backup file of particular database in Azure Container.  SELECT * FROM sys.credentials DROP CREDENTIAL [https: // 123 storages. blob .core.windows.net / 123 container] GO USE master GO CREATE CREDENTIAL [https: // 123 storages. blob .core.windows.net / 123 container] --this link you find in the continaer properties WITH IDENTITY = 'Shared Access Signature' --This shouble alwas shared access signature.Dont change the value /*the below value came from Shared access signature of continer.*/ /*Use Container Shared access signature instead of Storage access signature.*/ ,SECRET = 'sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacuptfx&se=2021-06-19T18:52:52Z&st=2021-06-19T10:52:52Z&spr=https&sig=Tgt6H8qXTKVroVkEqmHUk2ruP8HFnk%2FsN%2BeHMP18tVQ%3D' ; -- Access key GO BACKUP DATABASE TEST2 TO URL = N 'https://123storages....

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.

The mentioned command will replicate the SID's of SQL Server login accounts in Always On Availability replicas and automate the process. We have no need to update the logins when the failover occurs. The below command will drop and recreate a the login in secondary replica and the process would be very fast. And particularly it resolve SID mismatch issues between replicas. cls Copy-DbaLogin -Source SourceServerHere -Destination DestinationServerHere -Login Login1,Login2 -force -Verbose The below SQL query will works in normal environment where we are moving logins from one server to another server or source to destination. Need to run this query in master database of source server. And It creates stored procedure called sp_help_revlogin. After creating this procedure in Source server run this and you can see the results. Copy the same results and paste in Destination server and all the logins will get create. https://learn.microsoft.com/en-us/troubleshoot/sql/security/transfer...

I got the below error when we trying to take the transaction log backup of model databases. So as a part of solution, we change the recovery model of the model database from FULL to SIMPLE and then immediately changed it from SIMPLE to FULL. This resolved the issue. There is also a possibility that the below commands might not work in all cases. In that situation RESTART SQL server instance. This worked in my environments. USE [master] GO ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT GO USE [master] GO ALTER DATABASE [model] SET RECOVERY FULL WITH NO_WAIT GO

The below command will create a new user at database level and add that user to the required roles. cls $UserName = 'UserNameHere' $InstanceName = 'InstanceNameHere' New-DbaDbUser -SqlInstance $InstanceName -Database DatbaseNameHere -Username $UserName Add-DbaDbRoleMember -SqlInstance $InstanceName ` -Database DatabaseNameHere ` -Role "db_ddladmin" , "db_executor" , "db_datawriter" , "db_datareader" , "db_spexec" ` -User $UserName -Verbose

The below powershell command will give the information about SQL Server agent job and its status. cls Get-DbaAgentJobHistory ` -SqlInstance ServerNameHere ` -Job 'Job Name Here' ` -WithOutputFile| Where-Object {( $_ .Rundate -like "*05/06/2021*" )} -Verbose| Sort-Object Rundate -Descending| Format-Table ComputerName,SQLInstance,Job,RunDate,Status,Stepid,Message -Wrap

The below command will create an SQL Server authentication login on both the servers at a time. Once you run the command a pop window ask for password, then provide the password.  cls New-DbaLogin ` -SqlInstance SERVER1,SERVER2 ` -Login LoginNameHere -Verbose #Handling Windows login. The main thing we #need to remember here is DOMAIN name. cls New-DbaLogin ` -SqlInstance Server1 ` -Login DomainName\Login1,DomainName\Login2 -Verbose

Here the STATE 4 means "RUNNING" 1 Stopped. And the server names should be Windows Server names in which SQL Server instances are installed. cls #SQL Server 2014 $cmt1 = "ComputerManagement12" #sqlserver2016 $cmt2 = "ComputerManagement13" #sqlserver2017 $cmt3 = "ComputerManagement14" #SQL Server 2012 $cmt4 = "ComputerManagement11" #SQL Server 2008/2008 R2 $cmt5 = "ComputerManagement10" #provide only windows server names here $server = @ ( 'SERVER1' , 'SERVER2' , 'SERVER3' ) $server | ForEach -Object{ $singcmpt = $_ cd C : \windows\System32 $CmptMgmt =gwmi -ns 'root\Microsoft\SqlServer' __NAMESPACE -ComputerName $singcmpt | ? { $_ .name -match $cmt1 } |Select Name $CmptMgmt | ForEach -Object{ $singCmptMgmt = $_ if ( $singCmptMgmt = $cmt1 ) { Write-Host "$singcmpt is SQL Server 2014" -Verbose Get-CimInstance -ComputerName $singcmpt -Namespace "root/Microsoft/SqlS...