SELECT*

If you are  using domain account first image is the use ful image. Sometimes this REBUILD database will not bring back existing databases back which are there in previously, so we need to address this in a different way. So go through the below links for reference. moving system databases MSSQLSERVER_17204

The below powershell script brings information about SQL Server Agent jobs and its status from more than one SQL Server instance. CLEAR $HOSTNAME = 'COMPUTERNAMEHERE' Import-Module sqlps -DisableNameChecking $servernames = @( "computername\namedinstancename1" , "computername\namedinstancename2" , "computername\namedinstancename3" , "computername\namedinstancename4" , "computername\namedinstancename5" ) $servernames | ForEach-Object {     $computername = $_     #Get a server object which corresponds to the default instance     $srv = New-Object `     -TypeName 'Microsoft.SQLServer.Management.Smo.Server' `     -ArgumentList $computername     $srv . JobServer . Jobs |     Select-Object OriginatingServer , Name , LastRundate , LastRunOutcome , NextRunDate , CurrentRunstatus |     Where-Object LastRunOutcome -EQ "Failed" |     Where-Object { $_ . Name -like &

Import-Module SQLPS -DisableNameChecking $MyComputer = $env:COMPUTERNAME #Provide sql server instance name here $instanceName = $MyComputer + '\TEST' $instanceName #This information will give you all SMO'S of SQL Server $server = New-Object ` -TypeName Microsoft.SQLServer.Management.Smo.Server ` -ArgumentList $instanceName $server | Get-Member -MemberType "Property" | Where-Object Definition -like "*SMO*"  The below Powershell query will provide us the information about SQL Server Assemblies  clear [ appdomain ]:: CurrentDomain . GetAssemblies() | Where { $_ . FullName -match "SQLServer" } | Select FullName 

The below query will tell you which SQL Server Agent Operator is internally calling which mail id's. These operators we can assign to every sql server agent job. So whenever these jobs gets failed it will route to those mail id's USE msdb  GO   EXEC dbo.sp_help_operator       @operator_name = N'Provide Operator name here'; 

SELECT  j . [name]  AS   'JobName' , Enabled   =   CASE   WHEN  j . Enabled   =  0  THEN   'No' ELSE   'Yes' END , l . [name]  AS   'OwnerName' FROM  MSDB . dbo . sysjobs j INNER   JOIN   Master . dbo . syslogins  l ON  j . owner_sid  =  l . sid ORDER   BY  j . [name]

USE msdb; GO -- Disable Multiple SQL Jobs at once: DECLARE @dynSql NVARCHAR(MAX) = '' SELECT @dynSql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' + CHAR(10) + CHAR(13) FROM msdb.dbo.sysjobs WHERE enabled = 1 ORDER BY name; PRINT @dynSql;