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
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!!!!
