/* DATE AUTHOR REMARKS
11/1/18 PPaiva Initial creation.
DESCRIPTION
Use this code to get a historical list of backups on
this instance.
First query result is Last backup of each database.
USAGE
-- Last backup of each database
SELECT *
FROM vwBackupHistory
WHERE Num = 1
ORDER BY DateStarted desc
-- Databases NEVER backed up
SELECT *
FROM vwBackupNever
-- Last backups, all databases
SELECT *
FROM vwBackupHistory
ORDER BY DateStarted desc
-- Last 5 backups per DB per Type per Path
SELECT *
FROM vwBackupHistory
WHERE Num <= 5
AND IsSysDB = 0
ORDER BY Instance, DB, DateStarted desc
*/
Use tempdb
-- Use Admin -- put this code in your DBA database if you have one
go
IF object_id('dbo.udfGetFilenameOnly') Is Not Null
DROP FUNCTION dbo.udfGetFilenameOnly
go
CREATE FUNCTION dbo.udfGetFilenameOnly(
@In varchar(500)
)
RETURNS varchar(300)
AS
/* DATE AUTHOR REMARKS
2/2/16 PPaiva Initial creation.
USAGE
SELECT dbo.udfGetFilenameOnly('D:\Program Files\Microsoft SQL Server\Logs\Admin_Log.LDF')
SELECT dbo.udfGetFilenameOnly('Admin_Log.ldf')
*/
BEGIN
DECLARE @Out varchar(300),
@Pos smallint
-- Get position of last backslash
SET @Pos = CharIndex('\', Reverse(@In))
IF @Pos > 1
BEGIN
SET @Pos = Len(@In) - @Pos
SET @Out = Substring(@In, @Pos + 2, 500)
END
ELSE
SET @Out = @In
RETURN @Out
END
go
IF object_id('dbo.udfFormatInteger') Is Not Null
DROP FUNCTION dbo.udfFormatInteger
go
CREATE FUNCTION dbo.udfFormatInteger(
@In bigint
)
RETURNS varchar(20)
AS
/* DATE AUTHOR REMARKS
2/22/16 PPaiva Initial creation.
DESCRIPTION
Formats a given integer with commas.
Examples:
IN OUT
123 123
12345 12,345
-54321 -54,321
USAGE
SELECT dbo.udfFormatInteger(1234567890)
SELECT dbo.udfFormatInteger(12345)
SELECT dbo.udfFormatInteger(-54321)
SELECT dbo.udfFormatInteger(0)
*/
BEGIN
DECLARE @Out varchar(20),
@sIn varchar(20),
@Balance varchar(20),
@CurrTextLen smallint,
@IsNegative bit
IF @In < 0
BEGIN
SET @IsNegative = 1
SET @In = Abs(@In)
END
SET @sIn = Convert(varchar, @In)
SET @CurrTextLen = Len(@sIn)
SET @Out = ''
SET @Balance = @sIn
IF @CurrTextLen > 3
BEGIN
WHILE 1 = 1
BEGIN
SET @Out = ',' + Right(@Balance, 3) + @Out
SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)
SET @CurrTextLen = Len(@Balance)
IF @CurrTextLen > 3
CONTINUE
ELSE
BEGIN
SET @Out = @Balance + @Out
BREAK
END
END
END
ELSE
SET @Out = @sIn
IF @IsNegative = 1
SET @Out = '-' + @Out
RETURN @Out
END
go
IF object_id('dbo.udfGetPathOnly') Is Not Null
DROP FUNCTION dbo.udfGetPathOnly
go
CREATE FUNCTION dbo.udfGetPathOnly(
@In varchar(1000)
)
RETURNS varchar(1000)
AS
/* DATE AUTHOR REMARKS
2/22/16 PPaiva Initial creation.
DESCRIPTION
Returns the path only, with a trailing backslash.
USAGE
SELECT dbo.udfGetPathOnly('C:\Windows\Notepad.exe')
SELECT dbo.udfGetPathOnly('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
SELECT dbo.udfGetPathOnly('tempdb.ldf')
SELECT dbo.udfGetPathOnly('C:\asdf')
SELECT dbo.udfGetPathOnly('C:')
SELECT dbo.udfGetPathOnly('Z:')
*/
BEGIN
DECLARE @Out varchar(300),
@PosSlash smallint,
@PosDot smallint
-- Get position of last backslash
SET @PosSlash = CharIndex('\', Reverse(@In))
-- Get position of last dot
SET @PosDot = CharIndex('.', Reverse(@In))
IF @PosDot > 0
SET @PosDot = Len(@In) - @PosDot + 1
IF @PosSlash > 0
BEGIN
SET @PosSlash = Len(@In) - @PosSlash + 1
IF @PosDot = 0
SET @Out = @In
ELSE
SET @Out = Left(@In, @PosSlash)
END
ELSE
IF Len(@In) = 2 AND Right(@In, 1) = ':'
SET @Out = @In + '\'
RETURN @Out
END
go
IF Object_ID('dbo.vwBackupHistory') Is Not Null
DROP VIEW dbo.vwBackupHistory
GO
CREATE VIEW dbo.vwBackupHistory
AS
/* DATE AUTHOR REMARKS
11/29/16 PPaiva Initial creation.
11/1/18 PPaiva Added column IsOnline.
-- Last 10 backups per
DB per Type per Path
SELECT *
FROM vwBackupHistory
WHERE Num <= 10
AND DB Not In
('master', 'model', 'msdb')
ORDER BY 1, 2, 3, 4,
5, 6
SELECT *
FROM vwBackupHistory
ORDER BY DateStarted
desc
-- DBs backed up
SELECT Machine,
SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty
FROM vwBackupHistory
GROUP BY Machine,
SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
ORDER BY Machine,
SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
SELECT *
FROM vwBackupHistory
WHERE BackupType
<> 'Log'
ORDER BY DateFinished
desc, DB
SELECT Machine,
SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty
FROM vwBackupHistory
GROUP BY Machine,
SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
ORDER BY Machine,
SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
*/
SELECT ROW_NUMBER() OVER (PARTITION BY b.database_name, b.Type, b.Recovery_Model ORDER
BY backup_start_date desc) Num,
ServerProperty('ServerName') Instance,
b.database_name
DB,
CASE
WHEN d.name Is Not Null THEN 1
ELSE 0 END IsOnline,
CASE b.type
WHEN
'D' THEN 'Database'
WHEN
'L' THEN 'Log'
WHEN
'I' THEN 'Differential Database'
WHEN
'F' THEN 'File or FileGroup'
WHEN
'G' THEN 'Differential File'
WHEN
'P' THEN 'Partial'
WHEN
'Q' THEN 'Differential Partial'
WHEN
Null THEN 'Null'
ELSE
'Need to define in view'
END
AS BackupType,
b.recovery_model
RecoveryModel,
b.backup_start_date
DateStarted,
DATEDIFF(second, b.backup_start_date, b.backup_finish_date) BackupSecs,
DATEDIFF(minute, b.backup_start_date, b.backup_finish_date) BackupMins,
dbo.udfFormatInteger((b.backup_size /10000.)) SizeMBFmt,
CASE
WHEN b.database_name
In ('master', 'model', 'msdb', 'tempdb', 'distribution', 'Admin')
THEN 1 ELSE 0
END IsSysDB,
dbo.udfGetFilenameOnly(f.physical_device_name) Filename,
dbo.udfGetPathOnly(f.physical_device_name) Path,
bms.is_compressed
IsCompressed, -- remove column for 2005 and lower
b.compressed_backup_size
CompressedSizeBytes, -- remove column
for 2005 and lower
(Convert(decimal(4,1), (100 * (b.compressed_backup_size
/ b.backup_size))) ) PercComp, -- remove column for 2005 and lower
b.backup_size
SizeBytes,
b.is_copy_only
IsCopy,
b.is_damaged
IsDamaged,
b.description,
b.expiration_date
ExpDate,
f.physical_device_name
PathFilename,
b.name AS BackupSetName,
f.logical_device_name
LogicalDeviceName,
fg.name
FileGroup,
bms.software_name
SoftwareName,
b.backup_finish_date
DateFinished
FROM msdb.dbo.backupmediafamily
f
JOIN msdb.dbo.backupset b
ON f.media_set_id = b.media_set_id
JOIN msdb.dbo.backupmediaset bms
ON bms.media_set_id = b.media_set_id
JOIN msdb.dbo.backupfilegroup fg
ON fg.backup_set_id = b.backup_set_id
LEFT JOIN sys.databases d
ON d.name = b.database_name
go
IF Object_ID('dbo.vwBackupNever') Is Not Null
DROP VIEW dbo.vwBackupNever
GO
CREATE VIEW dbo.vwBackupNever
AS
/* DATE AUTHOR REMARKS
11/1/18 PPaiva Initial creation.
SELECT *
FROM vwBackupNever
ORDER BY Name
*/
-- Databases NEVER backed up
SELECT s.Name,
s.state_desc
State,
S.recovery_model_desc
RecoveryModel
FROM sys.databases s
LEFT JOIN (SELECT DB FROM
vwBackupHistory WHERE Num = 1) as v
ON v.DB = s.name
WHERE v.DB Is null
go
