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:
>

How to design link tables?

I have been having a similar discussion on the MS Access newsgroup for the
last w and I wanted to discuss the issues in the context of MS SQL in
addition to MS Access. I hope this form of cross posting is not offensive to
anyone.
Lets assume I have to relations: student and course and I want to create a
junction table to record which students are taking which courses.
(1) If I create a two column table containing fkStudent and fkCourse where
the primary consists of these two columns. Now I want to perform a join to
find out all the courses a particular student is taking. Since the primary
key index structure requires both foreign keys and I'm only providing the
value for fkStudent and not fkCourse, will I be preforming a linear search
on the junction table when I preform a join to determine what courses I am
taking?
My guess is yes. If your answer is yes, would you anticipate a linear search
to be a problem? I would. If you agree that we should avoid linear searches,
then what would you recommend for a primary key on this link table? Do we
need a pimary key at all? Most folks say yes -- but I'm not clear on why.
(2) Lets say I have many thousands of job titles -- too many for a combo
box. I want to have a M:M relationship between Job Titles and Job Postings.
Let's say I find a job posting on the web and I need to find the foriegn key
for a job title (if it exists) or create a job title (if it does not exist).
I can assume it does not exist and try an SQL INSERT and, if that fails, use
a SELECT. Or, I can assume it does exist and use SELECT and if I don't find
any, use INSERT. Either way, the worst case scenerio requires two redundant
lookups. Is there a better approach?
Thanks,
SiegfriedHi
1) Look at [Order Details] table in Northwind database.
It is a "junction table" between Orders and Product tables.
2)
IF NOT EXISTS (SELECT * FROM Table WHERE....)
INSERT INTO ......
ELSE
SELECT <columns> FROM ......
"Siegfried Heintze" <siegfried@.heintze.com> wrote in message
news:%23ofWEqlsFHA.460@.TK2MSFTNGP15.phx.gbl...
>I have been having a similar discussion on the MS Access newsgroup for the
> last w and I wanted to discuss the issues in the context of MS SQL in
> addition to MS Access. I hope this form of cross posting is not offensive
> to
> anyone.
> Lets assume I have to relations: student and course and I want to create a
> junction table to record which students are taking which courses.
> (1) If I create a two column table containing fkStudent and fkCourse where
> the primary consists of these two columns. Now I want to perform a join to
> find out all the courses a particular student is taking. Since the primary
> key index structure requires both foreign keys and I'm only providing the
> value for fkStudent and not fkCourse, will I be preforming a linear search
> on the junction table when I preform a join to determine what courses I am
> taking?
> My guess is yes. If your answer is yes, would you anticipate a linear
> search
> to be a problem? I would. If you agree that we should avoid linear
> searches,
> then what would you recommend for a primary key on this link table? Do we
> need a pimary key at all? Most folks say yes -- but I'm not clear on why.
> (2) Lets say I have many thousands of job titles -- too many for a combo
> box. I want to have a M:M relationship between Job Titles and Job
> Postings.
> Let's say I find a job posting on the web and I need to find the foriegn
> key
> for a job title (if it exists) or create a job title (if it does not
> exist).
> I can assume it does not exist and try an SQL INSERT and, if that fails,
> use
> a SELECT. Or, I can assume it does exist and use SELECT and if I don't
> find
> any, use INSERT. Either way, the worst case scenerio requires two
> redundant
> lookups. Is there a better approach?
> Thanks,
> Siegfried
>