Here is the quick script I use find last backup time for all the databases in my server instance.
SELECT sdb.Name AS DatabaseName, COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime FROM sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name GROUP BY sdb.Name
The query above will return following result where we can see the database last database backup date and time.
Do you use any other script for the same purpose, please share here, it will be interesting for all of us know.
Here are few other blog posts which are related to this subject.
- What is a Backup Timeline for Restore Process? – Interview Question of the Week #092
- What is Copy Only Backup in SQL Server? – Interview Question of the Week #128
- SQL SERVER – 5 Don’ts When Database Corruption is Detected
- How to Get Status of Running Backup and Restore in SQL Server? – Interview Question of the Week #113
- SQL SERVER – Finding Last Backup Time for All Databases – Last Full, Differential and Log Backup – Optimized
- SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.
If you ever find your TEmpDB to be full and if you want to move TempDB, you will find this blog post very helpful. Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB.
Reference: Pinal Dave (https://blog.sqlauthority.com)
49 Comments. Leave new
Looking for a way to identify databases that are using differential backups – any way to identify them and not full or log backups?
Hi Pinal,
In my application the database is created by the User, so I am executing the backup query (listing all the databses from sys.databases) using T-SQL Statement Task. Currently i have 392 databases in our azure virtual machine and I have scheduled a backup to azure blob storage. I will explain my problem now. Backup schedule is not running for all the databases. I have checked the history. But it is showing the schedule is executed successfully. Is there is any way to solve or track this. Expecting your reply
Thanks and Regards
Sreejith
Hi,
I am using the below script for checking the status of latest full, diff and tlog backups for all the databases.
set nocount on
go
set quoted_identifier off
declare @dbname as varchar(80)
declare @msgdb as varchar(100)
declare @dbbkpname as varchar(80)
declare @dypart1 as varchar(2)
declare @dypart2 as varchar(3)
declare @dypart3 as varchar(4)
declare @currentdate as varchar(10)
declare @server_name as varchar(30)
select @server_name = @@servername
select @dypart1 = datepart(dd,getdate())
select @dypart2 = datename(mm,getdate())
select @dypart3 = datepart(yy,getdate())
select @currentdate= @dypart1 + @dypart2 + @dypart3
print “#####################################################################”
print “# SERVERNAME : ” + @server_name + ” DATE : ” + @currentdate +”#”
print “#####################################################################”
Print “DatabaseName Full Diff TranLog”
Print “##########################################################################################################################################”
SELECT SUBSTRING(s.name,1,50) AS ‘DATABASE Name’,
b.backup_start_date AS ‘Full DB Backup Status’,
c.backup_start_date AS ‘Differential DB Backup Status’,
d.backup_start_date AS ‘Transaction Log Backup Status’
FROM master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)as ‘Full DB Backup Status’
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = ‘D’) — full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)’Differential DB Backup Status’
FROM msdb..backupset
WHERE database_name = c.database_name
AND type = ‘I’)
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)’Transaction Log Backup Status’
FROM msdb..backupset
WHERE database_name = d.database_name
AND type = ‘L’)
WHERE s.name ‘tempdb’
ORDER BY s.name
Hi all…
when I execute (A) on the server, I got more detailed result of back-up for field “Last_BKP”.
If I execute from a different server using a “Linked Server” (B) a got less row…field “Last_BKP
” is NULL for 4 rows.
I did not understand why…
ALEN
———————————– (A) ———————————-
SELECT
@@SERVERNAME [Istanza],
sdb.Name AS DatabaseName,
CONVERT(VARCHAR(13), MAX(bus.backup_finish_date), 103) AS [Last_BKP],
DATEDIFF(day, MAX(bus.backup_finish_date), getdate()) DD_Late,
MAX(bus.backup_finish_date) backup_finish_date
FROM master.dbo.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
WHERE sdb.Name ‘tempdb’
GROUP BY sdb.Name
———————————– (B) ———————————-
SELECT
‘MyDB server name’ [Istanza],
sdb.Name AS DatabaseName,
CONVERT(VARCHAR(13), MAX(bus.backup_finish_date), 103) AS [Last_BKP],
DATEDIFF(day, MAX(bus.backup_finish_date), getdate()) DD_Late,
MAX(bus.backup_finish_date) backup_finish_date
FROM [].master.dbo.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
WHERE sdb.Name ‘tempdb’
GROUP BY sdb.Name
give query very full to every one…
WITH sqltmp (DatabaseName, LastBackupId) AS
(
SELECT sdb.Name ,MAX(bus.backup_set_id)
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON (bus.database_name = sdb.name AND bus.type = ‘D’ )
GROUP BY sdb.name
)
SELECT @@SERVERNAME ‘Server’, a.DatabaseName, b.backup_start_date , b.backup_finish_date,m.physical_device_name,
Convert( numeric (8,2),Round(b.backup_size/ (1024*1024),2) ) [Size_MB]
FROM sqltmp a INNER JOIN msdb.dbo.backupset b ON
( a.LastBackupId = b.backup_set_id)
LEFT OUTER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE a.DatabaseName NOT IN (‘tempdb’,’model’,’msdb’,’master’)
ORDER BY DatabaseName
can any one please tell me the script for ” how to find yesterday’s backups only “
select sdb.name as DateBaseName ,convert(varchar(10), bus.backup_finish_date,101) as LastBacupdate,
bus.machine_name as MeachineName,bus.server_name as ServerName,bus.user_name from sys.sysdatabases sdb
join msdb.dbo.backupset bus
on sdb.name=bus.database_name
where DATEDIFF(day,bus.backup_finish_date,getdate())=1
Hi, In your scripts you need to define explcitly which for the full,diff or log, because in your scripts it takes the logbackup date/time stamp, so that confuses for other guys.
I have make changes in the query and get the required results
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
SELECT TOP ( 30 )
s.server_name,
s.database_name,
CASE s.[type]
WHEN ‘D’ THEN ‘Full’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Transaction Log’
END as BackupType,
s.recovery_model,
s.backup_finish_date,
s.backup_start_date,
DATEDIFF(mi,s.backup_start_date , s.backup_finish_date)/60 as Total_Time_in_Hours,
cast(CAST(s.backup_size / 1024 / 1024 / 1024 AS INT) as varchar(14))
+ ‘ ‘ + ‘GB’ as bkSize,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
m.physical_device_name
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date
I put together a stored proc to capture this information so I could put in a SQL Agent job and have it email when there aren’t any recent backups. … I know, I know. The backup job should alert you when it fails, but I have had to go oncall after a person who ignored those alerts so this is nice safety check.
https://gallery.technet.microsoft.com:443/scriptcenter/Procedure-reports-8994befe
i want to report
databases and recovery model and last backup date
please send script
here’s a script that works better, IMO.
it makes use of with() and rank() over() to avoid supplemental joins or subqueries.
this script doesnt work in sql server 2000 ..error getting invalid object
how to check size of db since last backup??? Or how to check size for differntial backup??
SP_HELPDB for size of the database and query MSDB.DBO.BACKUPSET table to get the size of differential backup. May be this will help
select
database_name
,CAST(ROUND(((backup_size/1024)/1024),2) AS DECIMAL (10,2)) AS [Backup size in MB]
FROM msdb.dbo.backupset
where type = ‘I’
If SQL is on VM and sysadmin is taking snapshot every night then you might need to add is_snapshot = 0
How to write a script to take full backup once in a week and differential backup all days.. ?
How to script to take full backup stroage the database ?
Hi All,
could you please explain the steps to
Perform failover testing database clusters
Regards
Shiva
I use the following query:
with backups as (select database_name,type,case type when ‘D’ then ‘DATABASE’ when ‘I’ then ‘DIFFERENTIAL’ when ‘L’ then ‘LOG’ else ‘OTHER’ end type_desc,recovery_model
,(select backupmediafamily.physical_device_name from msdb.dbo.backupmediafamily where backupmediafamily.media_set_id = backupset.media_set_id) physical_device_name
,backup_start_date,datediff(ms,backup_start_date,backup_finish_date) backup_duration
,backup_size/1024 backup_size,compressed_backup_size/1024 compressed_backup_size
,(select count(1) from msdb.dbo.restorehistory where restorehistory.backup_set_id = backupset.backup_set_id) restore_count
from msdb.dbo.backupset)
,latest_backups as (select database_name,type,max(backup_start_date) backup_start_date
from backups
group by database_name,type)
select backups.*
from latest_backups
join backups on latest_backups.database_name = backups.database_name and latest_backups.type = backups.type and latest_backups.backup_start_date = backups.backup_start_date
order by backups.backup_start_date desc,case backups.type when ‘D’ then 1 when ‘I’ then 2 when ‘L’ then 3 else 4 end desc
Thanks for sharing Jona.
That’s my statement:
SELECT DB.[name], BS.[type], MAX(BS.backup_finish_date) AS LastBackup
FROM sys.databases AS DB
LEFT JOIN msdb.dbo.backupset AS BS ON DB.[name] = BS.database_name
GROUP BY DB.[name], BS.[type];