Scripting all Agent Jobs By Using SQL Server Management Studio

I recently came across a way to script out all agent jobs in one go within SQL Server Management Studio (SSMS). Previously I used to right click on each job and select ‘Script Job as’ to script up each one individually, but this can be very laborious
if there are a lot of jobs to do.

To script all jobs, just open the ‘Object Explorer Details’ from the View menu in SSMS, or press the F7 key. Click on any job in the Object Explorer window and a list of all the agent jobs appears in the ‘Object Explorer Details’ window.

Select all the jobs you want to script (press the Ctrl button while clicking to select individual jobs) and then right click and select the scripting option you want. This will then create all the selected jobs as a single query. You can script to a query window, file etc.

Find out the backup locations/paths for multiple SQL Instances

If you are trying to consolidate backup locations, we first need to analyse where the databases are being backed up to. Below query will give you the location of where the databases were backed up to. Run this in Central Management server and you can get a report of all the SQL Instances in one shot

SET NOCOUNT ON
SELECT CONVERT(CHAR(30), SERVERPROPERTY('Servername')) AS instance_name,
CONVERT(CHAR(30),bs.database_name) AS database_name,
CONVERT(CHAR(125),bf.physical_device_name) backup_path
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bf
ON bs.media_set_id = bf.media_set_id
JOIN master.dbo.sysdatabases db
ON bs.database_name = db.name
WHERE bs.backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
            AND type = 'D'
      )
      AND (DATABASEPROPERTYEX(bs.database_name,'Status') = 'ONLINE'
      AND DATABASEPROPERTYEX(bs.database_name,'IsInStandby') = 0)

Script to generate database Role membership and also to generate commands to add the users to database roles

During the migration of a database from one SQL Instance to another, we might have to re-create the permissions on the target SQL Instance. This is not required in all the cases, but only in some cases where the user permissions are not synced for some reason. So an easy way to script them out before performing the database refresh is to use the below script to capture the existing setup of database role membership:

-- List Database Roles and Members with Server Login
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      ,MEM.type_desc AS MemberType
      ,MEM.default_schema_name AS DefaultSchema
      ,SP.name AS ServerLogin
FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY RoleName
        ,MemberName;

Now after this is captured, you still have to manually run the below commands for each of the users captured:

sp_addrolemember 'Rolename','UserName'

It takes a lot of time to generate a script manually if there are more than 5 or 10 users. Below is a script to generate those commands as well by leveraging on the above script:

SET QUOTED_IDENTIFIER OFF
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME="##USER_PERMISSIONS")
DROP TABLE ##USER_PERMISSIONS
CREATE TABLE ##USER_PERMISSIONS
(ROLENAME VARCHAR (100),
MEMBERNAME VARCHAR(100))
INSERT INTO ##USER_PERMISSIONS
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY MemberName, Rolename;
SELECT ("sp_addrolemember '"+ ROLENAME +"','"+MEMBERNAME+"'") AS CMD FROM ##user_permissions
go

How To Find How Many Instances Installed In The System By Using T-SQL

Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

Insert into #SQLInstances
EXECUTE xp_regread
@rootkey ='HKEY_LOCAL_MACHINE',
@key ='SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name ='InstalledInstances'


select instancename,Data from #SQLInstances