SQL – SQL Server Script to Iterate and Pull Data from All SQL Instances – Database Backup

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

About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in SQL, T-SQL and tagged , , . Bookmark the permalink.

One Response to SQL – SQL Server Script to Iterate and Pull Data from All SQL Instances – Database Backup

  1. Pingback: SQL- Setup and Configure Backup Report – Step by Step Approach – All listed SQL Instances | Prashanth Jayaram

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s