Category Archives: Backups

All about Backups, running backups, querying backups, reporting against backups

Location of very last Backups — All Databases

One script with some CTE, but two ways to show the info.

A narrow view:

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
--ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
--ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
--top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
--ORDER BY  s.backup_finish_date desc
)
, final as
(
select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff' 'bk_type', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1

UNION ALL

select c.database_name, c.recovery_model, 'tlog' 'bk_type', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1
)
select * from final
order by 1,3

 

 

A wide view


 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
)
, Diffbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
)
, TlogBK as (
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
)

select a.database_name, a.recovery_model
	, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
	, 'DIFF' 'DIFF_BK', b.physical_device_name,b.Seconds_TimeTaken,b.age_in_days, b.backup_start_date
	,  'TLOG' 'TLOG_BK', c.physical_device_name,c.Seconds_TimeTaken,c.age_in_days, c.backup_start_date
from fullbk a
left join Diffbk b on a.dbid = b.dbid
left join TlogBK c on a.dbid = c.dbid

where 1=1
and a.number  = 1 
and coalesce(b.number,1)=1
and coalesce(c.number,1)=1

union all
select Distinct
    convert(varchar(30),name) as DBname
    ,DATABASEPROPERTYEX(name,'recovery') 'RecoveryModel'
    ,'NO BACKUPS RECORDED ON MSDB',NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2


Location of very last Backups

This script will list the location of the last Full, Differential and Tlog (if exists)

I use three CTE and at the end I use a UNION all

the last two select statements are joins so it displays NULL if either Diff or Tlog do not exist

 

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012
MSSQL2014

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = db_name()
--SET @dbname = 'ELMO'
set @LastXdays = '30'
 
;with fullbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
ORDER BY  s.backup_finish_date desc
)

select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.bk_type,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1		 

UNION ALL
select c.database_name, c.recovery_model, 'tlog', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1		 






 

 

 

Querying CommandLog

If you have implemented Ola Hallenger scripts, http://ola.hallengren.com/, you will have a CommandLog table where you keep track of execution times.

The following query will help you get information from it, very useful for tracking backup failures:

USE DBA
SELECT
DatabaseName,
StartTime,
DATEDIFF(MINUTE,StartTime,EndTime) 'Duration in Min',
EndTime,
ErrorNumber,
ErrorMessage,
Command,
replace(
cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(1000))
,'errorlog','')
FROM DBO.COMMANDLOG
WHERE 1=1
--and StartTime > dateadd(dd,-1,getdate())
and CommandType in ('BACKUP_DATABASE','BACKUP_LOG','RESTORE_VERIFYONLY')
--and CommandType in ('xp_create_subdir','xp_delete_file')
--and CommandType in ('DBCC_CHECKDB')

--and ErrorNumber <> 0

order by ID desc

Below, we take advantage of window functions and we can see the last time a database had a DBCC checkdb done, along with its duration

 

    USE DBA
    
	;with Last_DBBC as
	(
	SELECT
    DatabaseName,
    StartTime,
    EndTime,
	ROW_NUMBER() over (PARTITION by databasename order by StartTime desc) 'rownumber'
    FROM DBO.COMMANDLOG
    WHERE 1=1
	and CommandType in ('DBCC_CHECKDB')
   	)
	select
	DatabaseName,
	StartTime, EndTime,
	DATEDIFF(MI,StartTime,EndTime) 'Duration in Min',
	DATEDIFF(DD,StartTime,GETDATE()) 'Age of DBCC in days'
	from Last_DBBC
	where 1=1
	and rownumber = 1

Historical Backup Report

The Greatest Backup Report

In some organization an important Key Point Indicator (KPI) has to do with backup success rates. This KPI attempts to measure what percentage of backups have taken place. However, many times this KPI has not been clearly defined.

I always ask "what constitute a backup failure"?  If a backup fails to take place but I fix it the next morning, is that still a fail backup?  or if a Full Backup fails but the Tlogs backups still take place, so if a failure occurs I'm still able to recover with no data loss, is that still a failed backup? This is the equivalent of if a tree fails in the forest, but I repair it without anyone knowing, did it still fall?

If we are reporting against SQL Jobs success rates, then we can run into a situation where if the job is misconfigured and it fails to account for newly created databases, we could run into a situation where we have 100% success rate but have databases with no backups.

Some might argue that, using the true definition, "a successful backup is one that you could restore".  This is fine and dandy, but how often are we able to restore all our database backups in a daily manner?

As you can see, there areextremes, but we still need to provide an honest measure for the KPI.

A good compromise would be to report against the actual backup as recorded in MSDB versus the expetected schedule of said backup

In this sense, we are not interested just on the last backup, but we are interested to know if all backups are running at regular intervals, and what that interval is.

This is a first iteration of the script where for a single DB might look like this:

1-Bk_Report_history

Based on the results, we can assume that the Backup Schedule probably is as follow:
Full 7 days.
Diff 1 day.
Tlog: Hourly.

We can also assume that the first value for Full is 5.  Since 5 is less than 7, not an issue,  still within schedule.
The last  value for Diff is 2.  Since 2 is more than 1, possible and issue. However, it probably means that a full backup was taken instead.
There is a value Tlog that is 2. Since 2 more than 1, there was a missed backup in that time period.

 

Another example:

2-Bk_Report_history

We can tell that FULL failed, since the first value is 12, and 12 is more than 7, therefore a Full Backup is missing.
We can also tell that DIFF failed, since the first value is   2, and   2 is more than 1, therefore a Diff Backup is missing.

Last Example:

3-Bk_Report_history

In here, we can see that someone was taking backups outside our normal backup schedule, sine most backups are going to TDP and we have a few backups going to local disk.

 

Finally, the script itself:

The Script is fully customizable to report for a certain number of days and either all databases or just one:

 

/*
DESCRIPTION	
List Database Backup Age, per database, and the intervale between backups

CONFIGURATION	top
SET @LastXDays for how far back you query MSDB
Set @DiffDays if you want to limit Diff.
Set @TlogDays if you want to limit the Tlog 

Set @dbname to NULL for all DBs, or to a single DB

Author		 mquinta@us.ibm.com

Compatibility list: 
MSSQL2005
MSSQL2008
MSSQL2012


*/

DECLARE @dbname varchar(500)
DECLARE @LastXdays nvarchar(2)
DECLARE @TlogDays int
DECLARE @DiffDays int

-- Configuration
--  how much history we query
SET @LastXdays = 28

-- limt the results for Differential backups
SET @DiffDays = 7
-- Limit results for Tlogs. Recommended 1 or 2 days
SET @TlogDays = 1  

-- SET database. If Null, it'll do all databases
SET @dbname = null  
-- SET @dbname = 'adventureworks'
----  end configuration


;WITH BackupAgeTable AS
(  
-- query info for backups, and place it in a 
-- Common Table Expression, adding a row_number row.
SELECT
	 s.database_name
	,s.backup_size
	,s.backup_start_date
	,s.type
	,m.physical_device_name
	,m.device_type
-- Ordering will group by datbase, then type of BK and then sort by date
	,row_number() OVER (ORDER BY s.database_name,
								 s.[type],
								 s.backup_start_date DESC)
						AS rownumber
FROM SYS.DATABASES d   
INNER JOIN msdb.dbo.backupSET s ON d.database_id = db_id(s.database_name)
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
WHERE
	1=1
	and DATEDIFF(dd,(s.backup_start_date),GETDATE()) < = @LastXdays
	and s.database_name like coalesce (@dbname,'%')
		
)

SELECT 
	cur.database_name
	, CASE cur.type
		WHEN 'D' THEN 'Full'
		WHEN 'I' THEN 'Diff'
		WHEN 'L' THEN 'TLog'
		else 'other'
	END AS backup_type
	, cast(cur.backup_size/1024/1024 as numeric (10,1)) 'SizeMB'
	, CASE cur.device_type
		when 2 then 'Disk'
		when 7 then 'Virtual' -- Like TDP
	end as 'DeviceType'
	, cur.physical_device_name
	, cur.backup_start_date
-- Using COALESCE because first row will be NULL and we need backup age instead
	, case cur.type
		when 'D' then 
		COALESCE (DATEDIFF(dd,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(dd,CUR.backup_start_date,GETDATE()))
		when 'I' then
		COALESCE (DATEDIFF(dd,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(dd,CUR.backup_start_date,GETDATE()))
		when 'L' then
		COALESCE (DATEDIFF(hh,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(hh,CUR.backup_start_date,GETDATE()))
		else 'other'			
	END as 'interval of Backups'
-- Using CASE statement because we want HOURs for Tlogs Backups and DAYS for the rest	
	, case cur.type
		when 'D' then 'days'
		when 'I' then 'days'
		when 'L' then 'hours'
		else 'other'
	END as 'units (days/hours)'

FROM BackupAgeTable CUR
-- Left join over itsef but with a offSET of one row
-- to be able to make a DIFF between dates of two consecutive rows
LEFT join BackupAgeTable PREV 
	on CUR.rownumber = (PREV.rownumber+1)
	   and cur.database_name=prev.database_name
	   and cur.type = prev.type 
where 1=1
-- Clever filtering to limit resutls
      and(   (cur.type = 'D')-- and datediff(dd,cur.backup_start_date,getdate())< @LastXdays)
	  or (cur.type = 'I' and datediff(dd,cur.backup_start_date,getdate())< @DiffDays )
	  or (cur.type = 'L' and datediff(dd,cur.backup_start_date,getdate())< @TlogDays )
	 )

-- The UNION ALL checks for DBs with no backups
UNION ALL
SELECT DISTINCT
	  SD.name 'database'
	, 'NO BACKUPS' 'TYPE'
	, NULL 'SizeMB'
	, NULL 'DeviceType'
	, NULL 'PhysicalDevice'
	, NULL 'BackupStartDate'
	, NULL 'Interval'
	, 'NO BACKUPS'	'Units'
FROM 
	 master..sysdatabases as SD

LEFT JOIN msdb..backupSET bp on SD.name = bp.database_name 
		 AND DATEDIFF(dd,(bp.backup_start_date),GETDATE()) <= @LastXdays
WHERE 1=1
and bp.database_name is NULL
and sd.name not in ('tempDB','model')


ORDER BY 8 DESC ,1,2, 6
-- First order by column 8 so databases with no backups
-- are listed on top

 

Find If Mix Backup Types are Present

There are mainly two types of backups:

  • Native SQL backups (either stored to local disk or a network location)
  • API backups, where software such as TSM/TDP or NetBackup, or CommVault, SQLLite, etc

There could be problems recovering a database if a server has different types of backups configured, unbeknown to the DBA. Per example, if TDP is used, and another DBA takes a local disk backup (out of band backup), it could pose a problem when we are restoring Tlog backups. This script identifies this situation, and lists those databases where different types of backups have been performed. If none are found, the result is blank

 


/*
DESCRIPTION
Queries the location of the last X days of databases backups
and reviews its location: API, DiskBackup or NetworkLocation

If backup location has changed in the last X days,
it returns those databases
If no change in location n the last X days,
it returns empty.

CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/

 
DECLARE @days INT
DECLARE @dbname sysname
 
SET @days = 7			-- Number of days. Limited by the history stored on MSDB
SET @dbname = NULL		-- Specify database like 'database1' . If Null it will do all
 
 
 
 ---- End of configuration
 
declare @BackupLocation table
(i int identity(1,1) ,db varchar(1000) , bk_type varchar(10), bk_Loc varchar(3))

insert into @BackupLocation 

SELECT
 bs.database_name
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, left(bmf.physical_device_name,3)
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
--and bs.type = 'D'
and bs.database_name like coalesce(@dbname,'%')


ORDER BY
bs.database_name

--------------------------
declare @summary table

(i int identity(1,1) ,db varchar(1000), db2 varchar(1000) )

insert into @summary

select db, db+'_'+bk_type
 from @BackupLocation 

group by bk_loc, db, bk_type
order by db



 select 
 case
 when count(db2) > 1 then 'Mix Backups Found'
 else 'Only one Type of Backups'
 end as 'backups'
 , db2 from @Summary 
 
 group by db2
 having count(db2)>1 


select db,  bk_type,
case substring(bk_loc,2,1)
WHEN ':' THEN 'LOCAL DISK'
WHEN '\' THEN 'NETWORK LOCATION'  --'
ELSE 'API BACKUPS'
end as 'Type of Backups'

 from @BackupLocation 

where db in
(select db from @Summary 
 group by db2, db
 having count(db2)>1 
 )
 
group by bk_loc, db, bk_type
order by db


Thank you Andrey for the idea to create this script

Backup Report – All Servers

This article describes how to run a single TSQL script against a list of SQL Servers.

It can be re purpose to run any number of script against a set of SQL servers.

 

  1.  Create a list of servers, place them on a file, and name it server_list.txt

The servers should be listed one per line.  You can use any string used to connect to SQL server, and enter port number if needed.

The only prerequisite is that the account running SQL Agent must have sysadmin access to all servers on the list.

Backup_solution

 

  1. Create a Batch file, name it run_sql.cmd 

The content of the Batch file should look like this:

Batch File:

REM: This batch file runs a SQL scripts against the SQL server 
setlocal 
C:
cd "C:\Program Files\Microsoft SQL Server\bk_report"
REM The echo is to set the headers of the resulting CSV file
echo server_name,SQLVersion,ServicePack,Database,RecoveryMode,FullBackupAge,LogBackupAge,DiffBackupAge, > "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"

for /F %%a in (server_list.txt) do (
sqlcmd -S%%a -dmaster -E -m 1 -h-1 -idetail_report.sql -s"," -W -w700 >> "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"
)
 
end

 

 

  1. Place a script with your backup report, name it detail_report.sql.

  You can use this script:  http://www.cookingsql.com/2014/10/backup-report/

HINT HINT This is the place where you can put pretty much any script and it will run against all your servers. Not only backup scripts, but also anything that you want to collect information from. Think about it like a poor's man Central Management Server.

  1. Configure a SQL Job to run the CMD file.

job

 

  1. Add a second step to email the report.

email

 

The code for the second step is:

     
    USE msdb
    EXEC sp_send_dbmail
    @profile_name='DBA', -- Please note, this Profile Must exist
    @recipients='DBA_GROUP_Email@us.company.com',
    @subject='Backup Report',
    @body='Please review the attachments. This is an automatic email generated from {server_name}',
    @file_attachments='C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv'
  1. Schedule the job to run daily.

 

Backup – Total Size Full Backups

This script is useful to find the size all last backups and an estimate on how long they took the last time they run.

It displays two queries. The first one gets the start time of the first database and end time of the last one. Naturally, if all databases were not backup at the same time this value will be off.

The second query is more reliable as it presents information for each database

It queries the MSDB for last backup on each database.

 


/*
DESCRIPTION: Queries the MSDB database for the size of the last full backup of each database
Configuration: No Configuration needed
 
Two queires: individual sizes, and Total sizes PLUS DURATION
 
Compatibility list:
MSSQL2005 - MSSQL2008 - MSSQL2008R2 - MSSQL2012
 
Does not work: MSSQL2000
 Update Log:
*/
 
 DECLARE @t TABLE
(dbname VARCHAR(500), physical_device_name VARCHAR(2000)
, sizeMB NUMERIC(20,0), StartDate DATETIME
, FinishDate DATETIME,age_in_Days INT)
 
INSERT INTO @t
EXEC sp_MSforEachDb '
SELECT
TOP 1 (s.database_name)
,m.physical_device_name
,s.backup_size/1000000
,s.backup_start_date
,s.backup_finish_date
,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days]
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
s.database_name = ''?''
and s.type =''D''
ORDER BY s.backup_finish_date desc
'
SELECT
SERVERPROPERTY('servername') 'ServerName',
SUM(sizeMB) 'TotalBackupMB',
MIN(StartDate),
sum(datediff(mi,(StartDate),(FinishDate))) 'DurationMinutes' -- This adds individaul backup times
FROM @t
 
SELECT dbname, physical_device_name, sizeMB,
Age_in_Days, StartDate, FinishDate,datediff(mi,(StartDate),(FinishDate)) 'DurationMinutes'
 
FROM @t

Backup Report

The most useful script.

There is a newer version below, that displays results in a better way

Plug this in a SQL job and send the results via email and you have a flexible reporting tool: click here for instructions

 

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog

Plus databases with no backups ever


CONFIGURATION   none

 
Compatibility list:
MSSQL2005
MSSQL2008

 */



set nocount on
use msdb
 
Select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),e.database_name) as DBname
    ,convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery')))
    ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset a Where a.database_name=e.database_name --and a.server_name  = @@servername
        and type='D'  Group by a.database_name) Full_Bk
        ,(Select datediff(dd,Max(backup_finish_date), getdate()) From backupset b  Where b.database_name=e.database_name
              --and b.server_name  = @@servername
              and type='L' Group by b.database_name) Log_Bk
       ,(Select datediff(dd,Max(backup_finish_date), getdate())  From backupset c Where c.database_name=e.database_name --and c.server_name  = @@servername
              and type='I' Group by c.database_name) Diff_Bk
From backupset e

Where
    e.database_name Not in ('tempdb','pubs','northwind','model')
    and databasepropertyex(database_name,'Status') = 'online'
    and e.database_name in (Select Distinct name from master..sysdatabases)
    --and e.server_name = select @@Servername
 
-- never backed up
Union all
select Distinct
    serverproperty('servername')
    , serverproperty('productversion')
    , serverproperty('productlevel')
    ,convert(varchar(30),name) as DBname
    , 'Never Backup, EVER'
    ,NULL
    ,NULL
    ,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2


This is another script, that better displays the missing backups and TLOG intervale of backups. ( source: LINK )

 

/*
DESCRIPTION    List the age of the last backup for all databases  for FULL, DIFF and Tlog
Plus databases with no backups ever
CONFIGURATION   none
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
 */
SELECT
  DISTINCT
        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = 'master'
                            AND type = 'D'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full (days ago)' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), datediff(dd,MAX(backup_finish_date),getdate()), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'I'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff (days ago)' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,MAX(backup_finish_date),getdate()), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'L'
                 ), 'No Log') 
        END AS 'LastLog Hours Ago' ,
        CASE DATABASEPROPERTYEX(a.name, 'Recovery')
        WHEN 'SIMPLE' THEN '--'
        ELSE
        COALESCE(( SELECT   CONVERT(VARCHAR(20), datediff(hh,backup_finish_date,getdate()), 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                                        backup_finish_date
                              FROM      msdb.dbo.backupset
                              WHERE     database_name = a.name
                                        AND type = 'L'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log')
        END AS 'LastLog2 hours ago'
                 
                 
                 
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE   a.name <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,
        a.compatibility_level
ORDER BY a.name

How long backups will take

Very simple script that queries a DMV to get an estimate of backup duration

/*
DESCRIPTION:    Queries the dm_exec requests and gives you an estimate of how long a backup/restore request is going to take
Configuration:    No Configuration needed
 
Compatibility list:
MSSQL2005 MSSQL2008  MSSQL2008R2 MSSQL2012

DOES NOT WORK MSSQL2000
Update Log:
*/
USE MASTER
SELECT
session_id as SPID
, r.command
, a.text AS Query
, start_time
, percent_complete
, dateadd(second,estimated_completion_time/1000, getdate())
                       as estimated_completion_time
, estimated_completion_time/1000/60 as time_left_mins
, r.blocking_session_id
, r.wait_type
FROM SYS.DM_EXEC_REQUESTS r
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(r.sql_handle) a
WHERE 1=1
and r.command in
('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE LOG')

Location of last X backups – Single DB

Check the location of backups for the last 7 days for a single database. Useful to see where the backups are going, and if there is any fail backup. Very similar to
http://www.cookingsql.com/2014/10/backups-location-of-all-last-x-backups-all-dbs/

 /*
DESCRIPTION    List location of  all backup for a single database.
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = 'master'
set @LastXdays = '30'
  
SELECT
    serverproperty('servername')
    ,s.database_name
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
 
--  ,cast(s.compressed_backup_size/1000000 as varchar(24))+' '+'MB' as bkSize  -- only on SQL2008
    ,CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(6))+' '+'Seconds' TimeTaken
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,CASE s.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        END as BackupType
    -- ,s.recovery_model    --- does not work 2000
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
ORDER BY  s.backup_finish_date desc