SQL Server 2K Backup History
-
Hello all, I have a quick question. I am currently trying to extract data from SQL Server to determine the last time a backup was run for a particular database.
I have identified the following two tables msdb.backupfile and msdb.backupset these both give me exactly what I am after, by performing a simple query I can retrieve the backupfile.backup_set_id for each backupfile and do a simple join on backupset.backup_set_id to retrieve the backupset.backup_start_date and backupset.backup_finish_date.
This is excellent, but I have been hunting for a system stored procedure or system function that will avoid me having to query the system tables directly but to no avail. Any ideas or suggestions would be greatly appreciated.?
Cheers
Lloyd
-
As I recall when I ran a profile trace on the taskpad view in Enterprise Manager it queried the system tables directly in order to get that information.
You might double-check that though. It's been a while.
All I did was open EM and set the database view to detail, switch to profiler configured to trace events with just that database and the system databases, started the trace, switched back to EM, changed the view to taskpad, waited till it was done loading and turned off the trace.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
I use the following when creating a test version of a database based on the most recent back up.
declare @physical_device_name nvarchar(128)
declare @backup_start_date datetime
declare @db varchar(128)
set @db = 'dbname' -- the name of the database
select @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
from msdb.dbo.backupset a join msdb.dbo.backupmediaset b on a.media_set_id = b.media_set_id
join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id
where type='D' and backup_start_date =
(select top 1 backup_start_date from msdb.dbo.backupset
where @db = database_name and type = 'D'
order by backup_start_date desc)
select @backup_start_date --- the time of most recent backup
select @physical_device_name -- the name of backup file
Francis
-
We use a view, similar to this one:
CREATE VIEW MostRecentBackup
AS
select a.database_name as 'DatabaseName', max(backup_finish_date) as 'BackupDate'
from msdb..backupset a
where a.type in ('I','D')
and exists
(select *
from master..sysdatabases b
where a.database_name = b.name
and isnull(databaseproperty(b.name,'isReadOnly'),0) = 0
and isnull(databaseproperty(b.name,'isOffline'),0) = 0)
group by a.database_name
The only difference is that we have another condition in the where clause to exclude certain databases based on their existence in an exclusion table.
Regards,
SteveMeddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.
-
As you can see by the replies, there is no system functionality to get the backup history (at least none that I know of), so I guess, as you already have found out, you will need to query the system tables.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply