WordPress.com



/*Execute the below T-SQL on Centralized Server --*//*************************--Enable XP_CMDShell -SSMS*****************************/sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'xp_cmdShell', 1;GORECONFIGURE;GO/*************************--List all SQL Instances in c:\Server.txt*****************************/ABCDEFEFG/*************************--Table Creation --SSMS*****************************/CREATE TABLE TDATA_BackupDetails([id] int identity(1,1),[servername] [nvarchar](128) NULL,[DBName] [sysname] NOT NULL,[LastBackUpTaken] varchar(50) NULL,[BackupSizeinMB] varchar(20) NULL,[DayssinceBackup] varchar(20) NULL,[UserName] [varchar](12) NULL)--**************-- After Changing the @DatabaseName Parameter, Copy and Paste the below code- C:\BackupDetails.sql--***************SET NOCOUNT ONDECLARE@SERVERNAME VARCHAR(15),@DBNAME VARCHAR(100),@LASTBACKUPTAKEN VARCHAR(20),@BACKUPSIZEINMB VARCHAR(20),@DAYSSINCEBACKUP VARCHAR(20),@USERNAME VARCHAR(20),@LOOPSTATUS_1 INT,@ROWID_1 INT,@DML NVARCHAR(4000),@DatabaseName Varchar(50)SET @DatabaseName='DBTest'CREATE TABLE #TLOG_BACKUPDETAILS([ID] INT IDENTITY(1,1),[SERVERNAME] [NVARCHAR](128) NULL,[DBNAME][SYSNAME] NOT NULL,[LASTBACKUPTAKEN] VARCHAR(50)NULL,[BACKUPSIZEINMB] VARCHAR(20)NULL,[DAYSSINCEBACKUP] VARCHAR(20)NULL,[USERNAME] [VARCHAR](12)NULL)INSERT INTO #TLOG_BACKUPDETAILS([SERVERNAME],[DBNAME],[LASTBACKUPTAKEN],[BACKUPSIZEINMB],[DAYSSINCEBACKUP],[USERNAME])SELECT@@SERVERNAME [SERVERNAME],T.NAME AS [DBNAME],(COALESCE(CONVERT(DATETIME,MAX(U.BACKUP_FINISH_DATE),101),'NOTYETTAKEN'))AS [LASTBACKUPTAKEN],(((COALESCE(CONVERT(REAL(256),MAX(U.BACKUP_SIZE),101),'NA'))/1024)/1024)AS [BACKUPSIZEINMB],ABS((COALESCE(CONVERT(VARCHAR(10),MAX(DATEDIFF(D,GETDATE(),U.BACKUP_FINISH_DATE))),101)))AS [DAYSSINCEBACKUP],(COALESCE(CONVERT(VARCHAR(12),MAX(U.USER_NAME),101),'NA'))AS [USERNAME]FROM MASTER..SYSDATABASES TINNER JOIN MSDB.dbo.BACKUPSET UON T.NAME=U.DATABASE_NAMEGROUP BY T.NAMEORDER BY T.NAMESET @LOOPSTATUS_1=1SET @ROWID_1=1WHILE(@LOOPSTATUS_1<>0)BEGINSET @DML=''SELECT@SERVERNAME=[SERVERNAME],@DBNAME=[DBNAME],@LASTBACKUPTAKEN=[LASTBACKUPTAKEN],@BACKUPSIZEINMB=[BACKUPSIZEINMB],@DAYSSINCEBACKUP=[DAYSSINCEBACKUP],@USERNAME=[USERNAME]FROM#TLOG_BACKUPDETAILSWHERE(ID=@ROWID_1)IF(@@ROWCOUNT=0)BEGINSET @LOOPSTATUS_1=0ENDELSEBEGINSET @DML=@DML+'INSERT INTO ['+@DatabaseName+'].dbo.TDATA_BACKUPDETAILS([SERVERNAME],[DBNAME],[LASTBACKUPTAKEN],[BACKUPSIZEINMB],[DAYSSINCEBACKUP],[USERNAME])VALUES('+''''+@@SERVERNAME+''''+','+''''+@DBNAME+''''+','+''''+@LASTBACKUPTAKEN+''''+','+''''+@BACKUPSIZEINMB+''''+','+''''+@DAYSSINCEBACKUP+''''+','+''''+@USERNAME+''''+')'ENDPRINT @DMLSET @ROWID_1=@ROWID_1+1ENDPRINT @DMLDROP TABLE #TLOG_BACKUPDETAILS/**********************************-Execute it through SSMS – replace below code to valid centralized server name. Replace AQDBSQL01.**************/Master..XP_CMDSHELL 'for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\BackupDetails.sql -E >> c:\backup.sql'GOMASTER..XP_CMDSHELL 'sqlcmd -S AQDBSQL01 -i c:\backup.sql -E'/***************/--Data Validation --SSMS*************select * from dbo.TDATA_BackupDetails where [DayssinceBackup]>1/***************---Auto Email Sending script --SSMS*************/DECLARE @tableHTML NVARCHAR(MAX),@td1 char(15),@td2 char(15) ,@td3 char(20) ,@td4 char(15),@td5 char(15),@td6 char(15),@td7 char(15),@Loopstatus11 int,@dmll nvarchar(max),@dml2 nvarchar(max),@Loopstatus1 int,@RowId1 int,@ProfileName varchar(100),@receipients varchar(200)/* Change Profile and Receipients details*/SET @ProfileName ='Test'SET @receipients='pjayaram@;prashanth.jayaram@'CREATE TABLE #TLOG_BackupDetails([id] int identity(1,1),[servername] [nvarchar](128) NULL,[DB Name] [sysname] NOT NULL,[Last BackUp Taken] varchar(50) NULL,[Backup Size in MB] varchar(20) NULL,[Days since Backup] varchar(20) NULL,[User Name] [varchar](12) NULL)insert into #TLOG_BackupDetails( [servername], [DB Name], [Last BackUp Taken], [Backup Size in MB], [Days since Backup], [USER Name] )SELECT [servername], [DBName], [LastBackUpTaken],[BackupSizeinMB], [DayssinceBackup], [UserName]from dbo.TDATA_BackupDetails where [DayssinceBackup]>0SET @dmll=''SET @dml2=''SET @Loopstatus1=1SET @RowId1=1SET @tableHTML = N'<H1>Database Backup Details </H1>' + N'<table border="1" cellpadding="1"><tr> <th BGCOLOR="RED">[ServerName]</th> <th BGCOLOR="RED">[DB Name]</th> <th BGCOLOR="RED">[Last BackUp Taken]</th> <th BGCOLOR="RED">[Backup Size in MB]</th> <th BGCOLOR="RED">[Days since Backup]</th> <th BGCOLOR="RED">[User Name]</th> </tr>'While @Loopstatus1<>0beginselect @td1 =[ServerName],@td2 =[DB Name],@td3 =[Last BackUp Taken],@td4 =[Backup Size in MB],@td5 =[Days since Backup],@td6 =[User Name] from #TLOG_BackupDetails where id=@RowId1 if @@ROWCOUNT=0beginset @Loopstatus1=0endelsebeginset @dmll= @dml2+N'<tr><td>'+@td1+N'</td><td>'+@td2+'</td><td>'+@td3+'</td><td>'+@td4+'</td><td>'+@td5+'</td><td>'+@td6+'</td></tr>'set @dml2=@dmllset @dmll=''endset @RowId1=@RowId1+1endSET @tableHTML=@tableHTML+@dml2+'</table>'print @tableHTMLif (exists (select * from #TLOG_BackupDetails))beginEXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName,@recipients=@receipients, @subject = 'Backup Details Report', @body = @tableHTML, @body_format = 'HTML';end DROP TABLE #TLOG_BackupDetails/* SQL JOBS*/--STEP 1 – Replace AQDBSQL01 in the below codeTRUNCATE TABLE dbo.TDATA_BackupDetailsGOMaster..xp_cmdshell 'del c:\backup.sql' GoMaster..XP_CMDSHELL 'for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\BackupDetails.sql -E >> c:\backup.sql'GOMASTER..XP_CMDSHELL 'sqlcmd -S AQDBSQL01 -i c:\backup.sql -E'--STEP 2: Change Profile and Receipients detailsDECLARE @tableHTML NVARCHAR(MAX),@td1 char(15),@td2 char(15) ,@td3 char(20) ,@td4 char(15),@td5 char(15),@td6 char(15),@td7 char(15),@Loopstatus11 int,@dmll nvarchar(max),@dml2 nvarchar(max),@Loopstatus1 int,@RowId1 int,@ProfileName varchar(100),@receipients varchar(200)/* Change Profile and Receipients details*/SET @ProfileName ='Test'SET @receipients='pjayaram@;prashanth.jayaram@'CREATE TABLE #TLOG_BackupDetails([id] int identity(1,1),[servername] [nvarchar](128) NULL,[DB Name] [sysname] NOT NULL,[Last BackUp Taken] varchar(50) NULL,[Backup Size in MB] varchar(20) NULL,[Days since Backup] varchar(20) NULL,[User Name] [varchar](12) NULL)insert into #TLOG_BackupDetails( [servername], [DB Name], [Last BackUp Taken], [Backup Size in MB], [Days since Backup], [USER Name] )SELECT [servername], [DBName], [LastBackUpTaken],[BackupSizeinMB], [DayssinceBackup], [UserName]from dbo.TDATA_BackupDetails where [DayssinceBackup]>0SET @dmll=''SET @dml2=''SET @Loopstatus1=1SET @RowId1=1SET @tableHTML = N'<H1>Database Backup Details </H1>' + N'<table border="1" cellpadding="1"><tr> <th BGCOLOR="RED">[ServerName]</th> <th BGCOLOR="RED">[DB Name]</th> <th BGCOLOR="RED">[Last BackUp Taken]</th> <th BGCOLOR="RED">[Backup Size in MB]</th> <th BGCOLOR="RED">[Days since Backup]</th> <th BGCOLOR="RED">[User Name]</th> </tr>'While @Loopstatus1<>0beginselect @td1 =[ServerName],@td2 =[DB Name],@td3 =[Last BackUp Taken],@td4 =[Backup Size in MB],@td5 =[Days since Backup],@td6 =[User Name] from #TLOG_BackupDetails where id=@RowId1 if @@ROWCOUNT=0beginset @Loopstatus1=0endelsebeginset @dmll= @dml2+N'<tr><td>'+@td1+N'</td><td>'+@td2+'</td><td>'+@td3+'</td><td>'+@td4+'</td><td>'+@td5+'</td><td>'+@td6+'</td></tr>'set @dml2=@dmllset @dmll=''endset @RowId1=@RowId1+1endSET @tableHTML=@tableHTML+@dml2+'</table>'print @tableHTMLif (exists (select * from #TLOG_BackupDetails))beginEXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName,@recipients=@receipients, @subject = 'Backup Details Report', @body = @tableHTML, @body_format = 'HTML';end DROP TABLE #TLOG_BackupDetails ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download