This Post is a continuation of my previous post. Many times, there is a difficulty in pulling the data to centralized server. As I’ve already mentioned in my previous post PART1,  I’m explaining how to pull the  DATABASE BACKUP information from all listed instance

In order to execute the below one you need to do a small change (Enable XP_CMDShell only on Centralized Server or where you are intended to schedule a sql job). In this case, below defined parameters needed to be changed with reference to your environment.

ServerName = ADBSQL01(Centralized Server)

DatabaseName= DBTest

TableName= TDATA_BackupDetails

The following steps will explain how to execute the script:

1)Create table to hold backup details

Use DBTest

Go

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

)

2) C:\Server.txt of ADBSQL01 Server holds all SQL Instances

3)The BackupDetails.sql file will contain the actual code. This file should be placed on your centralized server (ADBSQL01) on C: Drive. Copy and paste the below code in c:\BackupDetails.SQL

Note: – Change the Database Name in the below code. Dynamic SQL is in place to prepare Insert sql.

**********************

SET NOCOUNT ON

DECLARE

@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 T

INNER JOIN MSDB.dbo.BACKUPSET U

ON T.NAME=U.DATABASE_NAME

GROUP BY T.NAME

ORDER BY T.NAME

SET @LOOPSTATUS_1=1

SET @ROWID_1=1

WHILE(@LOOPSTATUS_1<>0)BEGIN

SET @DML=”

SELECT

@SERVERNAME=[SERVERNAME],

@DBNAME=[DBNAME],

@LASTBACKUPTAKEN=[LASTBACKUPTAKEN],

@BACKUPSIZEINMB=[BACKUPSIZEINMB],

@DAYSSINCEBACKUP=[DAYSSINCEBACKUP],

@USERNAME=[USERNAME]

FROM

#TLOG_BACKUPDETAILS

WHERE

(ID=@ROWID_1)

IF(@@ROWCOUNT=0)

BEGIN

SET @LOOPSTATUS_1=0

END

ELSE

BEGIN

SET @DML=@DML+’INSERT INTO ['+@DatabaseName+'].dbo.TDATA_BACKUPDETAILS([SERVERNAME],[DBNAME],[LASTBACKUPTAKEN],[BACKUPSIZEINMB],[DAYSSINCEBACKUP],[USERNAME])VALUES(‘+””+@@SERVERNAME+””+’,'+””+@DBNAME+””+’,'+””+@LASTBACKUPTAKEN+””+’,'+””+@BACKUPSIZEINMB+””+’,'+””+@DAYSSINCEBACKUP+””+’,'+””+@USERNAME+””+’)’

END

PRINT @DML

SET @ROWID_1=@ROWID_1+1

END

PRINT @DML

DROP TABLE #TLOG_BACKUPDETAILS

**********************************

4)In the SQL Server Management Studio, connect to ADBSQL01 server. Under DBTest Database, Execute the below T-SQL’s.

Master..XP_CMDSHELL ‘for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\BackupDetails.sql -E >> c:\backup.sql’
GO
MASTER..XP_CMDSHELL ‘sqlcmd -S ADBSQL01 -i c:\backup.sql -E’

Note: – First part of the above T-SQL gives you a list of Insert SQL Statement(backup.sql).

insert into DBTest.dbo.TDATA_BackupDetails([servername],[DBName],[LastBackUpTaken],[BackupSizeinMB],[DayssinceBackup],[UserName])values(‘AQDBSQL07′,’ReportServer’,'May22201310:00AM’,’5.375′,’0′,’NTAUTHORITY’)

insert into DBTest.dbo.TDATA_BackupDetails([servername],[DBName],[LastBackUpTaken],[BackupSizeinMB],[DayssinceBackup],[UserName])values(‘AQDBSQL11′,’WSS_UsageApplication_a66cb18ebff042f0a09666554c5901e7′,’May22 2013 10:01AM’,’207.25′,’7′,’NT AUTHORITY’)

Second Part does insertion all those SQL’s into DBTest.dbo.TDATA_BackupDetails.

5)Use SSMS and Query the table

USE DBTest

Go

select * from DBtest.dbo.TDATA_BackupDetails where [DayssinceBackup]>1

You can also send an automated email. Refer my below post for more information

http://sqlpowershell.wordpress.com/2013/04/22/sql-formatting-and-email-sending-reports-in-html-format-using-t-sql/#comments

Code is here SQL-Iteration

Thanks for reading my space and let me know if you need a code to send automated email.

Happy Learning!!!!