A Script to Find Latest Database backup


USE MASTER

GO

IF OBJECT_ID(‘USP_DBA_RECENTBACKUPS’) IS NOT NULL

DROP PROC USP_DBA_RECENTBACKUPS

GO

exec USP_DBA_RECENTBACKUPS ‘NTERP’,‘R’

CREATE PROC USP_DBA_RECENTBACKUPS

@P_DBNAME VARCHAR(200)= NULL,

@PLAN CHAR(1)= NULL

AS

BEGIN

DECLARE @DB_NAME VARCHAR(200)

SET @DB_NAME=@P_DBNAME

IF @DB_NAME IS NULL

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID

ORDER BY SERVER_NAME,DATABASE_NAME,BACKUP_DATE

END

ELSE

BEGIN

IF @PLAN = ‘R’

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT * FROM(

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME AND [TYPE] IN (‘D’,‘I’)

UNION

SELECT

SERVER_NAME,DATABASE_NAME,BAKUPTYPE=‘LOG’,

BACKUP_FINISH_DATE BACKUP_DATE,PHYSICAL_DEVICE_NAME [LOCATION]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

WHERE [TYPE]=‘L’

AND BACKUP_FINISH_DATE>

(

SELECT TOP 1 BACKUP_FINISH_DATE FROM MSDB.DBO.BACKUPSET WHERE [TYPE] IN (‘D’,‘I’)

AND DATABASE_NAME= @DB_NAME ORDER BY BACKUP_FINISH_DATE DESC

)

AND DATABASE_NAME= @DB_NAME ) AS RESTORE_PLAN

ORDER BY [RECENT BACKUP]

END

ELSE

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME

ORDER BY BACKUP_DATE

END

END

END

Referecne : http://www.SQLServerCentral.com

Advertisements

One Response to A Script to Find Latest Database backup

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

%d bloggers like this: