How to determine when the last successful full backup occurred?

Does anyone know how one might, from within a SQL query, determine when the
last successful full backup occurred. I can see this information within
Enterprise Manager (or Management Studio in this case) but I'd like to take
that information and use it to execute a cleanup task to delete all
transaction log backups prior to that last full backup.Look in the backupfile table in msdb database to find the backup
information.
If it is a scheduled job, look in the sysjobhistory table in msdb database.
The run_status value of 1 denotes a successful job completion.
Anith|||here is some code that i use for determining the recent full and differentia
l
backups from the msdb. all you have to do is supply the name of the database
,
and you can use that to get back the pertinent information
select @.backup_set_full = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'D' --for full Database backup
and bs.server_name = @.@.servername
select @.backup_set_diff = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'I' --for latest differential ('I'ncremental)
and bs.backup_set_id > @.backup_set_full
and bs.server_name = @.@.servername
using the backup_set_id, you can then run the following to determine the
physical file name:
select @.physical_device_name_bak=
'"'+convert(varchar(200),bmf.physical_device_name)+'"'
from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
where bs.media_set_id = bmf.media_set_id
and ((bs.backup_set_id = @.backup_set_full)) -- need full
and bs.server_name = @.@.servername
HTH
Thomas LaRock
Database Administrator
ING Investment Management
"Michael D'Angelo" wrote:

> Does anyone know how one might, from within a SQL query, determine when th
e
> last successful full backup occurred. I can see this information within
> Enterprise Manager (or Management Studio in this case) but I'd like to tak
e
> that information and use it to execute a cleanup task to delete all
> transaction log backups prior to that last full backup.
>
>|||Thanks for the help, I was able to come up with the following query to do
it...
DECLARE @.last datetime
DECLARE @.dbid int
DECLARE @.name nvarchar(255)
DECLARE @.path nvarchar(1024)
CREATE TABLE #temp_db
(name nvarchar(255),
dbid int)
INSERT INTO #temp_db
SELECT name,dbid FROM master..sysdatabases
WHILE ((SELECT COUNT(*) FROM #temp_db) > 0)
BEGIN
SET @.dbid = (SELECT MIN(dbid) FROM #temp_db)
SET @.name = (SELECT name FROM #temp_db WHERE dbid = @.dbid)
SET @.last = (
SELECT CAST(MAX(backup_start_date) as datetime)
FROM backupset
WHERE type = 'D'
AND database_name = @.name)
SET @.path = N'X:\pathtobackupdir' + @.name
PRINT 'Removing data from ' + @.path + ' before ' + CAST(@.last as
nvarchar(1024))
EXECUTE master.dbo.xp_delete_file 0,@.path,N'trn',@.last
DELETE FROM #temp_db WHERE dbid = @.dbid
END
DROP TABLE #temp_db
"Thomas LaRock" <thomas.larock@.discussions.microsoft.com> wrote in message
news:54E3BE74-D1FF-4101-823B-FD75FDB70F8F@.microsoft.com...
> here is some code that i use for determining the recent full and
> differential
> backups from the msdb. all you have to do is supply the name of the
> database,
> and you can use that to get back the pertinent information
> select @.backup_set_full = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'D' --for full Database backup
> and bs.server_name = @.@.servername
> select @.backup_set_diff = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'I' --for latest differential ('I'ncremental)
> and bs.backup_set_id > @.backup_set_full
> and bs.server_name = @.@.servername
>
> using the backup_set_id, you can then run the following to determine the
> physical file name:
> select @.physical_device_name_bak=
> '"'+convert(varchar(200),bmf.physical_device_name)+'"'
> from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
> where bs.media_set_id = bmf.media_set_id
> and ((bs.backup_set_id = @.backup_set_full)) -- need full
> and bs.server_name = @.@.servername
> HTH
>
> --
> Thomas LaRock
> Database Administrator
> ING Investment Management
>
> "Michael D'Angelo" wrote:
>