--Main


--DBA Script Thumb 10th Anniversary
/*
          /(|
          (  :
         __\  \  _____
       (____)  `|
      (____)|   |
       (____).__|
        (___)__.|_____
                   
*/


/* REALLY DUMB QUERY */

--You will need the [SELECT] database for this to work.
SELECT [Where],SUM([COUNT]) AS [GROUP BY]
FROM [SELECT].[dbo].[FROM]
    INNER JOIN [JOIN] [ON]
        ON [ON].JOINID = [FROM].[Where]
WHERE [Where] > 1
GROUP BY [WHERE]

/* REALLY UGLY NASTY QUERY */

/*
           ____                      ,
          /---.'.__             ____//
               '--.\           /.---'
          _______  \\         //
        /.------.\  \|      .'/  ______
       //  ___  \ \ ||/|\  //  _/_----.\__
      |/  /.-.\  \ \:|< >|// _/.'..\   '--'
         //   \'. | \'.|.'/ /_/ /  \\
        //     \ \_\/" ' ~\-'.-'    \\
       //       '-._| :H: |'-.__     \\
      //           (/'==='\)'-._\     ||
      ||                        \\    \|
      ||                         \\    '
      |/                          \\
                                   ||
                                   ||
                                   \\
                                    '

*/
--Credit in SQL Server Tacklebox Copyright 2009 Rodney Landrum
USE DBMaint

BEGIN TRAN T_Time;

DECLARE @SQL_Alphabet VARCHAR(26);
SET @SQL_Alphabet = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @rnd_seed INT;
SET @rnd_seed = 26;
DECLARE @DT DATETIME;
SET @DT = '05/21/1969';
DECLARE @counter INT;
SET @counter = 1;
DECLARE @tempVal NCHAR(40);

WHILE @counter < 100
BEGIN
    SET @tempVal = SUBSTRING(@SQL_Alphabet, CAST(RAND() * @rnd_seed AS INT) + 1, CAST(RAND() * @rnd_seed AS INT) + 1);

    INSERT INTO Important_Data WITH (XLOCK)
    VALUES
    (@tempVal, DATEDIFF(d, CAST(RAND() * 10000 AS INT) + 1, @DT), NEWID());
    WAITFOR DELAY '00:00:01';
    SET @counter = @counter + 1;
END;
EXEC xp_cmdshell 'C:\Windows\notepad.exe';
COMMIT TRAN T_Time;

--Produce Blocking
SELECT * FROM Important_data


--BLOCKING SCRIPT
--This was pulled directly from Pinal Dave at SQlAuthority.com
--https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
 GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
 AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO


 /*_
  ___  _  _  ____  ____  _  _  ____  ____  ____     ____  _  _  ____  _  _  ____  ___ 
( ___)( \/ )(_  _)( ___)( \( )(  _ \( ___)(  _ \   ( ___)( \/ )( ___)( \( )(_  _)/ __)
 )__)  )  (   )(   )__)  )  (  )(_) ))__)  )(_) )   )__)  \  /  )__)  )  (   )(  \__ \
(____)(_/\_) (__) (____)(_)\_)(____/(____)(____/   (____)  \/  (____)(_)\_) (__) (___/
*/

SELECT 
 event_data.value(N'(event/@timestamp)[1]', N'datetime') AS ts,
           event_data.value('(event/action[@name="nt_username"]/value)[1]', N'nvarchar(max)') AS [Login],
       event_data.value(N'(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)') AS [sql],
       event_data.value(N'(event/action[@name="session_id"]/value)[1]', N'int') AS SPID,
       event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP],
       event_data.value('(event/data[@name="duration"]/value)[1]', 'int') AS duration,
       event_data.value('(event/action[@name="database_name"]/value)[1]', N'nvarchar(max)') AS database_name,
       event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'int') AS [Physical reads],
       event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'int') AS [Logical Reads],
       event_data.value('(event/data[@name="row_count"]/value)[1]', 'int') AS [Row Count],
       event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'int') AS CPU
	   -- Reference Jonathan Kehayias at SQLSkills.com for below code sample SELECCT
	   FROM
	   ( SELECT  CONVERT(XML, event_data) AS event_data FROM sys.fn_xe_file_target_read_file(N'C:\Temp\QueryCapture_0_*.xel', NULL, NULL, NULL)) entries

--ADD these to a table for persistence.

MERGE dbo.QueryAnalysis AS Target
USING  

(SELECT 
 event_data.value(N'(event/@timestamp)[1]', N'datetime') AS ts,
           event_data.value('(event/action[@name="nt_username"]/value)[1]', N'nvarchar(max)') AS [Login],
       event_data.value(N'(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)') AS [sql],
       event_data.value(N'(event/action[@name="session_id"]/value)[1]', N'int') AS SPID,
       event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP],
       event_data.value('(event/data[@name="duration"]/value)[1]', 'int') AS duration,
       event_data.value('(event/action[@name="database_name"]/value)[1]', N'nvarchar(max)') AS database_name,
       event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'int') AS [Physical reads],
       event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'int') AS [Logical Reads],
       event_data.value('(event/data[@name="row_count"]/value)[1]', 'int') AS [Row Count],
       event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'int') AS CPU
	   --INTO QueryAnalysis
	   FROM
	   ( SELECT  CONVERT(XML, event_data) AS event_data FROM sys.fn_xe_file_target_read_file(N'C:\Temp\QueryCapture_0_*.xel', NULL, NULL, NULL)) entries
	 ) 
	   
	    AS Source
ON (Source.ts = Target.ts and Source.sql = Target.sql
and Source.spid = Target.spid and Source.timestamp = Target.timestamp)

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ts,Login,sql,spid,Timestamp,Duration,Database_Name,[Physical reads],[Logical Reads], [Row Count],CPU)
    VALUES (Source.ts,Source.login ,Source.sql,Source.spid, Source.timestamp ,Source.duration,Source.database_name, Source.[Physical reads], Source.[Logical Reads], Source.[Row Count], Source.CPU)  ;

--Query Analysis
SELECT  [ts]
      ,[Login]
      ,[sql]
      ,[SPID]
      ,[TIMESTAMP]
      ,[duration]
      ,[database_name]
      ,[Physical reads]
      ,[Logical Reads]
      ,[Row Count]
      ,[CPU]
  FROM [QueryCapture].[dbo].[QueryAnalysis]
  ORDER BY CPU DESC, Duration DESC

--Query Analysis Aggregate
  SELECT       
      [sql]
     , MAX([duration]) max_duration
     , MAX([Physical reads]) max_PR
      ,MAX([Logical Reads]) AS max_LR
      ,MAX([Row Count]) AS max_RC
      ,MAX([CPU]) AS max_CPU
  FROM [QueryCapture].[dbo].[QueryAnalysis]
  GROUP BY  [sql]
 




/*

 ▄▄▄       ██▓    ▓█████  ██▀███  ▄▄▄█████▓  ██████ 
▒████▄    ▓██▒    ▓█   ▀ ▓██ ▒ ██▒▓  ██▒ ▓▒▒██    ▒ 
▒██  ▀█▄  ▒██░    ▒███   ▓██ ░▄█ ▒▒ ▓██░ ▒░░ ▓██▄   
░██▄▄▄▄██ ▒██░    ▒▓█  ▄ ▒██▀▀█▄  ░ ▓██▓ ░   ▒   ██▒
 ▓█   ▓██▒░██████▒░▒████▒░██▓ ▒██▒  ▒██▒ ░ ▒██████▒▒
 ▒▒   ▓▒█░░ ▒░▓  ░░░ ▒░ ░░ ▒▓ ░▒▓░  ▒ ░░   ▒ ▒▓▒ ▒ ░
  ▒   ▒▒ ░░ ░ ▒  ░ ░ ░  ░  ░▒ ░ ▒░    ░    ░ ░▒  ░ ░
  ░   ▒     ░ ░      ░     ░░   ░   ░      ░  ░  ░  
      ░  ░    ░  ░   ░  ░   ░                    ░  
	  
*/


   --Post Maintenance Checks
   --This is a sample only of how to use HTML in email with sp_send_dbmail
   --You will need to modify this to supply your own queries.
   

DECLARE @Attempts SMALLINT;
DECLARE @AlertHTML NVARCHAR(MAX);

 
DECLARE @Subject NVARCHAR(40) = 'Post Maintenance Errors';
DECLARE @CountAlert VARCHAR(10);

--sets a counter to inlude in the report. Supply your query here.

SELECT  @CountAlert = CAST(COUNT(Server) AS VARCHAR(10))
FROM    [DBMaint].[dbo].[SQL_ErrorLogCheck]
WHERE   DATEDIFF(dd, LogDate, GETDATE()) = 0
        AND Text LIKE '%Failure%'
        OR Text LIKE '%Error %'
        OR Text LIKE '%Fail%'
        AND Text NOT LIKE 'Login Failed%'
        --AND @Attempts >= 3;
--sets a control for flow of the code. If all checks are good (= 0) then execute one path, else (>0) execute failure path.

SELECT  @Attempts = COUNT(Server)
FROM    [DBMaint].[dbo].[SQL_ErrorLogCheck]
WHERE   DATEDIFF(dd, LogDate, GETDATE()) = 0
        AND Text LIKE '%Failure%'
        OR Text LIKE '%Error %'
        OR Text LIKE '%Fail%'
        AND Text NOT LIKE 'Login Failed%';
                    --AND @Attempts >= 3

--sets a control for flow of the code. If all checks are good (= 0) then execute one path, else (>0) execute failure path.
IF @Attempts = 0 SET @AlertHTML =  + N'<HTML><style type="text/css">h2, body {font-family: Arial, verdana;}
		table{font-size:11px; border-collapse:collapse;}
		td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
		th{background-color:#99CCFF;}
		</style>'
        + N'<h2 style="color:#7CFC00;">Error Reported in Error Logs - Count (' + @CountAlert
        + ') No Errors or Failures Reported</h2>'       


IF @Attempts > 0
    SET @AlertHTML = N'<HTML><style type="text/css">h2, body {font-family: Arial, verdana;}
		table{font-size:11px; border-collapse:collapse;}
		td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
		th{background-color:#99CCFF;}</style>'
        + N'<h2>Error Log Failure and Error Entries - Count (' + @CountAlert
        + ')</h2>' + N'<table border="1">' + N'<tr>
		<th>Server</th>
		<th>LogDate</th>
		<th>Event</th>

' 
--Add your SELECT fileds and tables and WHERE criteria here.
+ CAST(( SELECT   td = [Server] ,
                    '' ,
                    td = [LogDate] ,
                    '' ,
                    td = [Text]
           FROM     [DBMaint].[dbo].[SQL_ErrorLogCheck]
           WHERE    DATEDIFF(dd, LogDate, GETDATE()) = 0
                    AND Text LIKE '%Failure%'
                    OR Text LIKE '%Error %'
                    OR Text LIKE '%Fail%'
                    AND Text NOT LIKE 'Login Failed%'
                    AND @Attempts >= 3
           ORDER BY 1
         FOR
           XML PATH('tr') ,
               TYPE
         ) AS NVARCHAR(MAX)) + N'</table>'


    SELECT  @Attempts = COUNT(InstanceName)
    FROM    [DBMaint].[dbo].[DatabaseCheck]
    WHERE   
	--DATEDIFF(dd, PollingTimeStamp, GETDATE()) = 0
           state_desc <> 'ONLINE'

    SELECT  @CountAlert = CAST(COUNT(InstanceName) AS VARCHAR(10))
    FROM    [DBMaint].[dbo].[DatabaseCheck]
    WHERE   
	--DATEDIFF(dd, PollingTimeStamp, GETDATE()) = 0
             state_desc <> 'ONLINE'

IF @Attempts = 0 SET @AlertHTML = @AlertHTML + N'<style type="text/css">h2, body {font-family: Arial, verdana;}
		table{font-size:11px; border-collapse:collapse;}
		td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
		th{background-color:#99CCFF;}
		</style>'
        + N'<h2 style="color:#7CFC00;">Database Not Online - Count (' + @CountAlert
        + ') All Databases Reported Online</h2>'	
	
 IF @Attempts > 0
    SET @AlertHTML = @AlertHTML
        + N'<style type="text/css">h2, body {font-family: Arial, verdana;}
		table{font-size:11px; border-collapse:collapse;}
		td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
		th{background-color:#99CCFF;}</style>'
        + N'<h2>Databases Not Online - Count (' + @CountAlert + ')</h2>'
        + N'<table border="1">' + N'<tr>
		<th>InstanceName</th>
		<th>Database Name</th>
		<th>Status</th>

'
--Add your SELECT fileds and tables and WHERE criteria here.
 + CAST(( SELECT   td = [InstanceName] ,
                    '' ,
                    td = [DatabaseName] ,
                    '' ,
                    td = [state_desc]
           FROM     [DBMaint].[dbo].[DatabaseCheck]
           WHERE   
		    --DATEDIFF(dd, PollingTimeStamp, GETDATE()) = 0
                     state_desc <> 'ONLINE'
           ORDER BY 1
         FOR
           XML PATH('tr') ,
               TYPE
         ) AS NVARCHAR(MAX)) + N'</table>';


    SELECT  @Attempts = COUNT(InstanceName)
    FROM    [DBMaint].[dbo].[SQLAgentStatus]
    WHERE   SQLServerAgentStatus <> 'RUNNING';

    SELECT  @CountAlert = CAST(COUNT(InstanceName) AS VARCHAR(10))
    FROM    [DBMaint].[dbo].[SQLAgentStatus]
    WHERE   SQLServerAgentStatus <> 'RUNNING';

IF @Attempts = 0 SET @AlertHTML = @AlertHTML + N'<style type="text/css">h2, body {font-family: Arial, verdana;}
		table{font-size:11px; border-collapse:collapse;}
		td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
		th{background-color:#99CCFF;}
		</style>'
        + N'<h2 style="color:#7CFC00;">SQL Agent Status Not Running - Count (' + @CountAlert
        + ') All Agents Reported Running Status</h2>'	
	
 IF @Attempts > 0 
    SET @AlertHTML = @AlertHTML
        + N'<style type="text/css">h2, body {font-family: Arial, verdana;}
		table{font-size:11px; border-collapse:collapse;}
		td{background-color:#F1F1F1; border:1px solid black; padding:3px;}
		th{background-color:#99CCFF;}</style>'
        + N'<h2>SQL Agent Status Not Running - Count (' + @CountAlert
        + ')</h2>' + N'<table border="1">' + N'<tr>
		<th>InstanceName</th>
		<th>SQL Agent Status</th>
		<th>Polling Time Stamp</th>

' 
--Add your SELECT fileds and tables and WHERE criteria here.
+  CAST(( SELECT   td = [InstanceName] ,
                    '' ,
                    td = [SQLServerAgentStatus] ,
                    '' ,
                    td = [PollingTimeStamp]
           FROM     [DBMaint].[dbo].[SQLAgentStatus]
           WHERE    SQLServerAgentStatus <> 'RUNNING'
           ORDER BY 1
         FOR
           XML PATH('tr') ,
               TYPE
         ) AS NVARCHAR(MAX)) + N'</table>';
 
    SET @Subject = 'Post Maintenance Checks Report ';
    EXEC msdb.dbo.sp_send_dbmail @recipients = 'Youremail@emailhere.com;',
        @subject = @Subject, @body = @AlertHTML, @profile_name = 'Alerts',
        @body_format = 'HTML';		

/*
8 888888888o          .8.           ,o888888o.    8 8888     ,88' 8 8888      88 8 888888888o     d888888o.   
8 8888    `88.       .888.         8888     `88.  8 8888    ,88'  8 8888      88 8 8888    `88. .`8888:' `88. 
8 8888     `88      :88888.     ,8 8888       `8. 8 8888   ,88'   8 8888      88 8 8888     `88 8.`8888.   Y8 
8 8888     ,88     . `88888.    88 8888           8 8888  ,88'    8 8888      88 8 8888     ,88 `8.`8888.     
8 8888.   ,88'    .8. `88888.   88 8888           8 8888 ,88'     8 8888      88 8 8888.   ,88'  `8.`8888.    
8 8888888888     .8`8. `88888.  88 8888           8 8888 88'      8 8888      88 8 888888888P'    `8.`8888.   
8 8888    `88.  .8' `8. `88888. 88 8888           8 888888<       8 8888      88 8 8888            `8.`8888.  
8 8888      88 .8'   `8. `88888.`8 8888       .8' 8 8888 `Y8.     ` 8888     ,8P 8 8888        8b   `8.`8888. 
8 8888    ,88'.888888888. `88888.  8888     ,88'  8 8888   `Y8.     8888   ,d8P  8 8888        `8b.  ;8.`8888 
8 888888888P .8'       `8. `88888.  `8888888P'    8 8888     `Y8.    `Y88888P'   8 8888         `Y8888P ,88P' 

*/

--Modified from original.

Declare @dbname varchar(100)
declare @sql nvarchar(max)
declare @filepath nvarchar(200)
DECLARE @isExists INT

set nocount on

declare dblist cursor for
select name from sys.databases where name in  ('Test','Test2','Test3','Audit')

open dblist

fetch next from dblist into @dbname

while @@fetch_Status=0
begin
	set @filepath='C:\Temp\Backups\' + @dbname + '1110175.bak'
	exec master.dbo.xp_fileexist @filepath, 
	@isExists OUTPUT

	if @isExists <> 1 
	begin

		set @sql='BACKUP DATABASE [' + @dbname + '] TO  DISK = ''' + @filepath + ''' WITH NOFORMAT, INIT, COPY_ONLY, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
		print @sql
		--print @filepath
		--exec sp_Executesql @sql
	end

	fetch next from dblist into @dbname
end

close dblist
deallocate dblist


/*
8 888888888o.   8 8888888888     d888888o. 8888888 8888888888 ,o888888o.     8 888888888o.   8 8888888888     d888888o.   
8 8888    `88.  8 8888         .`8888:' `88.     8 8888    . 8888     `88.   8 8888    `88.  8 8888         .`8888:' `88. 
8 8888     `88  8 8888         8.`8888.   Y8     8 8888   ,8 8888       `8b  8 8888     `88  8 8888         8.`8888.   Y8 
8 8888     ,88  8 8888         `8.`8888.         8 8888   88 8888        `8b 8 8888     ,88  8 8888         `8.`8888.     
8 8888.   ,88'  8 888888888888  `8.`8888.        8 8888   88 8888         88 8 8888.   ,88'  8 888888888888  `8.`8888.    
8 888888888P'   8 8888           `8.`8888.       8 8888   88 8888         88 8 888888888P'   8 8888           `8.`8888.   
8 8888`8b       8 8888            `8.`8888.      8 8888   88 8888        ,8P 8 8888`8b       8 8888            `8.`8888.  
8 8888 `8b.     8 8888        8b   `8.`8888.     8 8888   `8 8888       ,8P  8 8888 `8b.     8 8888        8b   `8.`8888. 
8 8888   `8b.   8 8888        `8b.  ;8.`8888     8 8888    ` 8888     ,88'   8 8888   `8b.   8 8888        `8b.  ;8.`8888 
8 8888     `88. 8 888888888888 `Y8888P ,88P'     8 8888       `8888888P'     8 8888     `88. 8 888888888888 `Y8888P ,88P' 
*/

--CODE BEGIN

-- The original code was plucked from various sources on the Internet, mainly SQLServerCentral 
--I do not claim it as mine but I have made several modifications to the original

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      Type char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL,
	  SnapShotURL varchar(MAX)
 );

 DECLARE @Header TABLE
(
 [BackupName] [nvarchar](128) NULL,
 [BackupDescription] [nvarchar](255) NULL,
 [BackupType] [smallint] NULL,
 [ExpirationDate] [datetime] NULL,
 [Compressed] [bit] NULL,
 [Position] [smallint] NOT NULL,
 [DeviceType] [tinyint] NULL,
 [UserName] [nvarchar](128) NULL,
 [ServerName] [nvarchar](128) NULL,
 [DatabaseName] [nvarchar](128) NULL,
 [DatabaseVersion] [int] NULL,
 [DatabaseCreationDate] [datetime] NULL,
 [BackupSize] [numeric](20, 0) NULL,
 [FirstLSN] [numeric](25, 0) NULL,
 [LastLSN] [numeric](25, 0) NULL,
 [CheckpointLSN] [numeric](25, 0) NULL,
 [DatabaseBackupLSN] [numeric](25, 0) NULL,
 [BackupStartDate] [datetime] NULL,
 [BackupFinishDate] [datetime] NULL,
 [SortOrder] [smallint] NULL,
 [CodePage] [smallint] NULL,
 [UnicodeLocaleId] [int] NULL,
 [UnicodeComparisonStyle] [int] NULL,
 [CompatibilityLevel] [tinyint] NULL,
 [SoftwareVendorId] [int] NULL,
 [SoftwareVersionMajor] [int] NULL,
 [SoftwareVersionMinor] [int] NULL,
 [SoftwareVersionBuild] [int] NULL,
 [MachineName] [nvarchar](128) NULL,
 [Flags] [int] NULL,
 [BindingID] [uniqueidentifier] NULL,
 [RecoveryForkID] [uniqueidentifier] NULL,
 [Collation] [nvarchar](128) NULL,
 [FamilyGUID] [uniqueidentifier] NULL,
 [HasBulkLoggedData] [bit] NULL,
 [IsSnapshot] [bit] NULL,
 [IsReadOnly] [bit] NULL,
 [IsSingleUser] [bit] NULL,
 [HasBackupChecksums] [bit] NULL,
 [IsDamaged] [bit] NULL,
 [BeginsLogChain] [bit] NULL,
 [HasIncompleteMetaData] [bit] NULL,
 [IsForceOffline] [bit] NULL,
 [IsCopyOnly] [bit] NULL,
 [FirstRecoveryForkID] [uniqueidentifier] NULL,
 [ForkPointLSN] [numeric](25, 0) NULL,
 [RecoveryModel] [nvarchar](60) NULL,
 [DifferentialBaseLSN] [numeric](25, 0) NULL,
 [DifferentialBaseGUID] [uniqueidentifier] NULL,
 [BackupTypeDescription] [nvarchar](60) NULL,
 [BackupSetGUID] [uniqueidentifier] NULL,
 [CompressedBackupSize] [bigint] NULL
 ,[containment] [tinyint] NOT NULL 
 ,[keyAlgorithm] [VARCHAR](20)
 ,[EncryptorThmbprint] [VARCHAR](30)
 ,EncryptorType [VARCHAR](20) -- ONLY FOR SQL 2012+
) 

SET NOCOUNT ON

DECLARE @Command VARCHAR(100)
SET @Command = 'C:& cd\Temp\Backups&dir /b /s *.bak'

declare @DBName varchar(500)


DECLARE @FilesInAFolder TABLE  (FileNamesWithFolder VARCHAR(500))
DECLARE @Files TABLE  (FileNames VARCHAR(500))
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @Command

; WITH CTE AS
(
  SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder
)
insert into @Files
SELECT --FileNames = STUFF ( FileNamesWithFolder , 1 , (LEN(FileNamesWithFolder) - CHARINDEX ('\', REVERSE(FileNamesWithFolder))+1) , '')
      FileNames = REVERSE (LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)))
FROM  CTE
WHERE ReverseFileNames IS NOT NULL
Select * from @Files

declare @dbonly varchar(500), @realDBName nvarchar(500)
declare @sql nvarchar(max)
declare @FileID int
declare @fulltextlist nvarchar(max)=''
declare @fulltextfile varchar(500)
declare @datalist nvarchar(max)=''
declare @datafile varchar(500)
declare @logical_data nvarchar(max), @logical_log nvarchar(max), @logical_fulltxt nvarchar(max);
declare @physical_data nvarchar(max), @physical_log nvarchar(max);
declare DBs cursor for 
select FileNames from @Files

open DBs

fetch next from DBs into @DBName
while @@fetch_status = 0

begin
	set @dbonly=substring(@dbname,0,charindex('.bak',@dbname))

	if not exists (select * from sys.databases d where d.name=@dbonly)
	begin
		--do restore
		set @sql='restore filelistonly ' + ' From disk=''C:\Temp\Backups' + @dbname + ''''
		
		INSERT INTO @FileList
		EXEC(@sql);

		--PRINT @SQL

		select * from @filelist

		-- get DB name
		set @sql='restore headeronly ' + ' From disk=''C:\Temp\Backups' + @dbname + ''''
		INSERT INTO @Header
		EXEC (@sql)

		--PRINT @SQL 

		set @realDBName=(select DatabaseName from @header)
		delete from @header

		set @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
		set @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)

		set @physical_log = (select PhysicalName from @FileList where Type = 'L' and FileID = 2)
		set @physical_log = REVERSE ( LEFT (REVERSE(@physical_log), CHARINDEX ('\', REVERSE(@physical_log))-1))

		-- data files text
		declare files cursor for select LogicalName,PhysicalName, FileID from @FileList where Type = 'D'
		open files
		set @datalist=''
		fetch next from files into @datafile, @physical_data, @FileID
		while @@FETCH_STATUS = 0
		begin
			set @physical_data = REVERSE ( LEFT (REVERSE(@physical_data), CHARINDEX ('\', REVERSE(@physical_data))-1))

			if @datalist <>'' 
				set @datalist=@datalist + ',
				'
			set @datalist = @datalist + 'MOVE '''+ @datafile + ''' TO ''C:\SQLData\' + @physical_data + ''''
			fetch next from files into @datafile, @physical_data, @FileID
		end
		close files
		deallocate files

		-- full text
		declare files cursor for select LogicalName, PhysicalName from @FileList where Type = 'F'
		open files
		set @fulltextlist=''
		fetch next from files into @fulltextfile, @physical_data
		while @@FETCH_STATUS = 0
		begin
			set @physical_data = REVERSE ( LEFT (REVERSE(@physical_data), CHARINDEX ('\', REVERSE(@physical_data))-1))

			set @fulltextlist=@fulltextlist + ',
			MOVE '''+ @fulltextfile + ''' TO ''C:\SQLData\' + @physical_data + ''''
			fetch next from files into @fulltextfile, @physical_data
		end
		close files
		deallocate files

		set @sql='Restore database [' +  @realDBName + '_1] From disk=''C:\Temp\Backups' + @dbname + '''
		WITH ' + @datalist + ',
		MOVE '''+ @logical_log + ''' TO ''C:\SQLData\' + @physical_log + '''' + @fulltextlist
		print @sql
		--exec sp_executesql @sql


		set @Command='Move C:\Temp\Backups' + @dbname + '.bak C:\Temp\Backups\archive\'
		--print @command
		--EXEC MASTER..xp_cmdshell @Command
		
		delete from  @FileList
	end

	fetch next from DBs into @DBName
end


close DBs
deallocate DBs

--CODE END


/*
..######...#######..##.............###.....######...########.##....##.########....####.##....##.########..#######.
.##....##.##.....##.##............##.##...##....##..##.......###...##....##........##..###...##.##.......##.....##
.##.......##.....##.##...........##...##..##........##.......####..##....##........##..####..##.##.......##.....##
..######..##.....##.##..........##.....##.##...####.######...##.##.##....##........##..##.##.##.######...##.....##
.......##.##..##.##.##..........#########.##....##..##.......##..####....##........##..##..####.##.......##.....##
.##....##.##....##..##..........##.....##.##....##..##.......##...###....##........##..##...###.##.......##.....##
..######...#####.##.########....##.....##..######...########.##....##....##.......####.##....##.##........#######.
*/

-- Schedule Information
--MSDB FIRST
USE MSDB
GO
--Modified from code on mssqltips
--https://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/


SELECT 

[Job].[job_id] AS [JobID],
       [Job].[name] AS [JobName],
       [jobstep].[step_uid] AS [StepID],
       [jobstep].[step_id] AS [StepNo],
       --[jobstep].[subsystem],
       [jobstep].[command],
       [jobstep].[database_name],
       [jobstep].[step_name] AS [StepName],
      job.enabled,
--https://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/
	   dbo.udf_schedule_description(dbo.sysschedules.freq_type, 
dbo.sysschedules.freq_interval,  
dbo.sysschedules.freq_subday_type, 
dbo.sysschedules.freq_subday_interval, 
dbo.sysschedules.freq_relative_interval,  
dbo.sysschedules.freq_recurrence_factor, 
dbo.sysschedules.active_start_date, 
dbo.sysschedules.active_end_date,  
dbo.sysschedules.active_start_time, 
dbo.sysschedules.active_end_time) AS ScheduleDscr,
       CASE [jobstep].[last_run_outcome]
           WHEN 0 THEN
               'Failed'
           WHEN 1 THEN
               'Succeeded'
           WHEN 2 THEN
               'Retry'
           WHEN 3 THEN
               'Canceled'
           WHEN 5 THEN
               'Unknown'
       END AS [LastRunStatus],
       STUFF(STUFF(RIGHT('000000' + CAST([jobstep].[last_run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)],
       [jobstep].[last_run_retries] AS [LastRunRetryAttempts],
       CASE [jobstep].[last_run_date]
           WHEN 0 THEN
               NULL
           ELSE
               CAST(CAST([jobstep].[last_run_date] AS CHAR(8)) + ' '
                    + STUFF(
                               STUFF(RIGHT('000000' + CAST([jobstep].[last_run_time] AS VARCHAR(6)), 6), 3, 0, ':'),
                               6,
                               0,
                               ':'
                           ) AS DATETIME)
       END AS [LastRunDateTime]
 
FROM [dbo].[sysjobsteps] AS [jobstep]
    INNER JOIN [dbo].[sysjobs] AS [Job]
        ON [jobstep].[job_id] = [Job].[job_id]
		Left Outer  JOIN  
dbo.sysjobschedules ON Job.job_id = dbo.sysjobschedules.job_id Left Outer JOIN  
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 

ORDER BY [JobName],
         [StepNo];

/*
       __    ______   .______           _______.  ______  __    __   _______  _______   __    __   __       _______     _______.
      |  |  /  __  \  |   _  \         /       | /      ||  |  |  | |   ____||       \ |  |  |  | |  |     |   ____|   /       |
      |  | |  |  |  | |  |_)  |       |   (----`|  ,----'|  |__|  | |  |__   |  .--.  ||  |  |  | |  |     |  |__     |   (----`
.--.  |  | |  |  |  | |   _  <         \   \    |  |     |   __   | |   __|  |  |  |  ||  |  |  | |  |     |   __|     \   \    
|  `--'  | |  `--'  | |  |_)  |    .----)   |   |  `----.|  |  |  | |  |____ |  '--'  ||  `--'  | |  `----.|  |____.----)   |   
 \______/   \______/  |______/     |_______/     \______||__|  |__| |_______||_______/  \______/  |_______||_______|_______/    
                                                                                                                             
*/


SELECT * FROM (
SELECT [Job].[job_id] AS [JobID],
       [Job].[name] AS [JobName],
       Job.enabled,
       dbo.sysschedules.enabled AS Schedule_Enabled,
       --https://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/
       dbo.udf_schedule_description(
                                       dbo.sysschedules.freq_type,
                                       dbo.sysschedules.freq_interval,
                                       dbo.sysschedules.freq_subday_type,
                                       dbo.sysschedules.freq_subday_interval,
                                       dbo.sysschedules.freq_relative_interval,
                                       dbo.sysschedules.freq_recurrence_factor,
                                       dbo.sysschedules.active_start_date,
                                       dbo.sysschedules.active_end_date,
                                       dbo.sysschedules.active_start_time,
                                       dbo.sysschedules.active_end_time
                                   ) AS ScheduleDscr,
       ROW_NUMBER() OVER (PARTITION BY [Job].[job_id] ORDER BY [Job].[job_id]) AS rn
FROM [dbo].[sysjobs] AS [Job]
    LEFT OUTER JOIN dbo.sysjobschedules
        ON Job.job_id = dbo.sysjobschedules.job_id
    LEFT OUTER JOIN dbo.sysschedules
        ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id) t1
		WHERE rn = 1

--ORDER BY [JobName];


/*
       __    ______   .______       __    __   __       _______.___________.  ______   .______     ____    ____ 
      |  |  /  __  \  |   _  \     |  |  |  | |  |     /       |           | /  __  \  |   _  \    \   \  /   / 
      |  | |  |  |  | |  |_)  |    |  |__|  | |  |    |   (----`---|  |----`|  |  |  | |  |_)  |    \   \/   /  
.--.  |  | |  |  |  | |   _  <     |   __   | |  |     \   \       |  |     |  |  |  | |      /      \_    _/   
|  `--'  | |  `--'  | |  |_)  |    |  |  |  | |  | .----)   |      |  |     |  `--'  | |  |\  \----.   |  |     
 \______/   \______/  |______/     |__|  |__| |__| |_______/       |__|      \______/  | _| `._____|   |__|   
 
 */



USE msdb
GO

WITH CTE_SQLAgent_JH 
AS (SELECT TOP 100000 j.Name,
       CAST(dbo.agent_datetime(run_date, run_time) AS DATE) AS fRun_Date,
       dbo.agent_datetime(run_date, run_time) AS fRun_date_time,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
       STUFF(
                STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'),
                9,
                0,
                ':'
            ) 'run_duration',
			DATENAME(DAY, CAST(dbo.agent_datetime(run_date, run_time) AS DATE)) AS DayName_JH,
			DATENAME(Month, CAST(dbo.agent_datetime(run_date, run_time) AS DATE)) AS MonthName_JH,

       instance_id,
       jh.job_id,
       step_id,
       sql_message_id,
       sql_severity,
       message,
       run_status,
       run_date,
       run_time AS run_Time_Day,
       run_duration AS duration_minutes,
       AVG(jh.run_duration) OVER (PARTITION BY jh.Job_id) AS average_rt,
       MAX(jh.run_duration) OVER (PARTITION BY jh.Job_id) AS max_rt,
       MIN(jh.run_duration) OVER (PARTITION BY jh.Job_id) AS min_rt,
       COUNT(jh.job_id) OVER (PARTITION BY jh.Job_id, run_date) AS executions_per_day,
       DATEPART(HOUR, dbo.agent_datetime(run_date, run_time)) AS Hour_day,
       COUNT(jh.job_id) OVER (PARTITION BY jh.job_id,
                                           run_date,
                                           DATEPART(HOUR, dbo.agent_datetime(run_date, run_time))
                             ) AS execution_hour,
       server
FROM sysjobhistory jh
    INNER JOIN sysjobs j
        ON j.job_id = jh.job_id
WHERE step_id = 0
      AND CAST(dbo.agent_datetime(run_date, run_time) AS DATE) >= GETDATE() - 30
)

		 SELECT * FROM CTE_SQLAgent_JH 
		 ORDER BY execution_hour desc
		 --WHERE CTE_SQLAgent_JH.executions_per_day < 100


/*
 $$$$$$\                                          $$\   $$\               
$$  __$$\                                         \__|  $$ |              
$$ /  \__| $$$$$$\   $$$$$$$\ $$\   $$\  $$$$$$\  $$\ $$$$$$\   $$\   $$\ 
\$$$$$$\  $$  __$$\ $$  _____|$$ |  $$ |$$  __$$\ $$ |\_$$  _|  $$ |  $$ |
 \____$$\ $$$$$$$$ |$$ /      $$ |  $$ |$$ |  \__|$$ |  $$ |    $$ |  $$ |
$$\   $$ |$$   ____|$$ |      $$ |  $$ |$$ |      $$ |  $$ |$$\ $$ |  $$ |
\$$$$$$  |\$$$$$$$\ \$$$$$$$\ \$$$$$$  |$$ |      $$ |  \$$$$  |\$$$$$$$ |
 \______/  \_______| \_______| \______/ \__|      \__|   \____/  \____$$ |
                                                                $$\   $$ |
                                                                \$$$$$$  |
                                                                 \______/ 
*/


SET NOCOUNT ON;
GO
--Drop Users

DECLARE @sqlCommand VARCHAR(2000);
DECLARE @uname VARCHAR(max);
--THIS PRINTS THE DROP USER AND DOPR LOGIN COMMANDS FOR USERS STORED IN AN EXCEL FILE LIST
--ENTER USER NAME HERE / For Windows, include Domain

--SET @uname = 'Monica';


IF EXISTS (SELECT     *
                        FROM         tempdb.dbo.sysobjects
                        WHERE     id = OBJECT_ID(N'[tempdb].[dbo].[#TempUsers]'))  DROP TABLE #TempUsers;

 SELECT DISTINCT
    STUFF(
    (
        SELECT ',' + u.users
		--Need an Excel file with a list of users with a heading of Users
--like

--Users
--rlandrum
--testuser1
--testuser2
        FROM
            OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 12.0 Xml;HDR=YES;Database=C:\Temp\HoldFiles\User_List.xlsx',
                       'SELECT * FROM [Sheet1$]'
                      ) u
        WHERE u.users = users
        ORDER BY u.users
        FOR XML PATH('')
    ),
    1,
    1,
    ''
         ) AS userlist INTO #TempUsers
FROM
--Need an Excel file with a list of users with a heading of Users
--like

--Users
--rlandrum
--testuser1
--testuser2

--Be sure to point to the correct folder, make sure Excel is closed, and you may have to run SSMS in administrator mode.
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
               'Excel 12.0 Xml;HDR=YES;Database=C:\Temp\HoldFiles\User_List.xlsx',
               'SELECT * FROM [Sheet1$]'
              )
GROUP BY users;

SELECT @uname = Userlist FROM #TempUsers

--ADD LOGINS and INSERT INTO A TEMP TABLE
--PRINT @sqlCommand
DECLARE @DB_USers TABLE
(ServerName VARCHAR(2000), DatabaseName varchar(2000), UserName varchar(2000))


Declare @str varchar(2000) = @uname
Declare @single varchar(2000) = NULL


IF EXISTS (SELECT     *
                        FROM         tempdb.dbo.sysobjects
                        WHERE     id = OBJECT_ID(N'[tempdb].[dbo].[HoldParam]')) DROP TABLE [tempdb].[dbo].[HoldParam]; 
CREATE TABLE [tempdb].[dbo].[HoldParam] (Val VARCHAR(2000))

WHILE LEN(@str) > 0
BEGIN
    IF PATINDEX('%,%', @str) > 0
    BEGIN
        SET @single = SUBSTRING(@str,
                                    0,
                                    PATINDEX('%,%', @str))
		INSERT INTO [tempdb].[dbo].[HoldParam]
        SELECT  @single

        SET @str = SUBSTRING(@str,
                                  LEN(@single + ',') + 1,
                                  LEN(@str))
    END
    ELSE
    BEGIN
        SET @single = @str
        SET @str = NULL
 		INSERT INTO[tempdb].[dbo].[HoldParam]
        SELECT  @single
    END
END


INSERT @DB_USers
EXEC master..sp_MSforeachdb 'USE [?]

SELECT  @@ServerName,''?'' as DatabaseName,Name as UserName FROM sys.database_principals prin
WHERE  prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT ServerName,DatabaseName, 'Use' + '[' + Databasename + '];' + 'DROP User ' + Username AS DROP_Statement FROM @DB_USers WHERE Username IN (SELECT val FROM [tempdb].[dbo].[HoldParam])

--DECLARE @droplogin VARCHAR(MAX);

DECLARE @Logins_Server TABLE
    (
	ServerName VARCHAR(2000),
      Login_name VARCHAR(2000) 
      
    );
INSERT  INTO @Logins_Server
        SELECT  @@Servername,name AS Login_Name 
                
        FROM    sys.server_principals
        WHERE   type IN ( 'U', 'S', 'G' )
                AND name NOT LIKE '%##%'
               


IF EXISTS ( SELECT  Login_name
            FROM    @Logins_Server )
BEGIN
        SELECT  ServerName, 'DROP LOGIN ' + '''' + Login_name  + '''' AS DROP_LOGIN
        FROM    @Logins_Server Where Login_Name IN (SELECT val FROM [tempdb].[dbo].[HoldParam]);
        --PRINT @droplogin;
END