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
— for single db back up history i use
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
— Get Backup History for required database
SELECT TOP ( 30 )
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ‘ ‘ + ‘MB’ as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
+ ‘Seconds’ TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
CASE s.[type]
WHEN ‘D’ THEN ‘Full’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Transaction Log’
END as BackupType,
s.server_name,
s.recovery_model
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
Hi Asim Bhai,
Is there any to take backup of database for certain date and time.
for example i want to get database backup for 12-12-2012 12:15:00.
if you could suggest some way to do it,
Thanks
i have a question here.
how to write select statement to get what backup is taken to which file on which database?
please answer to this as soon as possible!
I use the following query:-
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), ‘NEVER’) as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), ‘NEVER’) as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = ‘D’ GROUP BY B.Name ORDER BY B.name
— Complete info about all databases
SELECT
database_id,
convert(varchar(25), DB.name) as dbName,
convert(varchar(10), Databasepropertyex(name, ‘status’)) as [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc as [Recovery model],
CASE compatibility_level
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) as [Creation date],
— last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ +
ltrim(ISNULL(STR(ABS(DATEDIFF(day, GetDate(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +
‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
+ ‘seconds)’
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
Cheers!
Good script.
Could you please add the below columns to the script and re-send.
========
backupsize(MB),
Physical DeviceName,
Backedup By Username
Hi pinal,
The script is much more useful to me also.
I have a question regarding this.
How can we get the failed backup operation list.
In our office we use 5 databases. All the databases are sceduled for backup on every day night, this is scheduled via the job.
if any one database get failed while executing job, the job shows in error. How can i know which data base backup get failed.
Is there any history like this. ?
Hi Rajesh,
Did you get that scrip.
If you have please share to me.
Hi Pinal,
I really need a big favor from you. I need to set up a process
which will take a backup everyday and delete the previous
days backup. Can you please help me with it? I would really
appreciate your help.
Thanks,
Joben
Schedule through Maintenance–>management–>new management plan in sql server create one schedules
1) for creating a backup of the day
2) for deleting the previous days back up
I have a backup “full” running each day to disk but the database last backup up always remains at none.
Does this mean the instance is not able to carry out hot backups?
Hi Pinal,
Finally your script is working fine 2005 onwards
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
SQL Server 2000 and 7.0
use master
go
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),’-‘) AS LastBackUpTime
FROM sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
Thanks & Regards
Jayant Dass
it’s for sql server 6.5
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish), 101),’-‘) AS LastBackUpTime
FROM sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.sysbackuphistory bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
Thanks & Regards
Jayant Dass
Hi pinal,
As your all posts are usefull. I need sql code to find out database name with all their previous backup datetime
I am waiting for your reply.
Thanks and regards,
suhas
The above scripts are really very useful.
Thank You for your help.
Nilesh
Hi Pinnal,
Please do the need full this is P1.
I need a single script for below output for all databases.
Database name,
Database Size,
Occurrence of the log backups (i.e. every 15, 30, 60 etc).
Regards.
prashanth Kumar
Can some one help me, how to get the db backup info for all instance in a single domain.
no it gives one date only to all columns as below
for script as :
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
master 10/22/2012
tempdb –
model 10/22/2012
msdb 10/22/2012
Actional 10/22/2012
ScConfiguration 10/22/2012
ScReporting 10/22/2012
ScTrans 10/22/2012
Vault 10/22/2012
AuditTrail 10/22/2012
BitKooDirectory 10/22/2012
Keystone2 10/22/2012
keystone2_audit 10/22/2012
Keystone2Log 10/22/2012
BitkooDeployer 10/22/2012
TraceLogs 10/22/2012
SmcWmArchive 10/22/2012
SmcWmIs 10/22/2012
SmcWmMws 10/22/2012
SmcWmOptimize 10/22/2012
SmcWmProcess 10/22/2012
BIxPress 10/22/2012
ProblemResolution 10/22/2012
Hi Pinal,
Can you please explain about system databases?
Thank you All. Following SQL start,end and duration for all databases on your server
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(20), MAX(bus.backup_start_date), 100),’-‘) AS StartTime,
COALESCE(CONVERT(VARCHAR(20), MAX(bus.backup_finish_date), 100),’-‘) AS EndTime,
isnull(datediff(mi,max(bus.backup_start_date),max(bus.backup_finish_date)),0) ‘BackupDuration’
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
Here is my code to check for the last database backup. It gives the database recovery model and the last time of a full and log backup.
I hope it help someone
SELECT a.name AS [Database Name],a.recovery_model_Desc AS [Recovery Model],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘D’ and a.name=b.database_name) AS [Full Backup],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘L’ and a.name=b.database_name) AS [Log Backup]
FROM master.sys.databases a
Thx. It helped. :)
you can use this for SQL 2000
SELECT a.name AS [Database Name],databasepropertyex(a.name, ‘Recovery’) as RecoveryModel,
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘D’ and a.name=b.database_name) AS [Full Backup],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = ‘L’ and a.name=b.database_name) AS [Log Backup]
FROM master.dbo.sysdatabases a
Error received on running the topmost(first) script in SQL Server 2005 as :
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysdatabases’.
Please help me on this….
Thank you.
Hello,
I need to create a Failed backups report . We have multiple servers and we need to have a report which helps us identify most recent failed backups on all the servers and the resective database names. I have created linked servers for all these multiple servers on single SQL server system. But I need a script which helps me get all tha failed backup jobs on all these servers. Can you please provide me with a script for this.
hi pinal,
how to query for list of databases which are never backed up, and save that result into new table..?
please help
please check this Dharurkar
select sdb.name as DateBaseName , COALESCE(convert(varchar(10), bus.backup_finish_date,101),’Till now this is datebases or not bacup’ ) as LastBacupdate into #DatabasesnotBackup from sys.sysdatabases sdb
left join msdb.dbo.backupset bus
on sdb.name=bus.database_name
where bus.backup_finish_date is null
select * from #DatabasesnotBackup
drop table #DatabasesnotBackup