No, but that's just because I haven't had to do it. I do have some code that determines when the latest backup was and what the path+filename is (used in a documentation script I have). It wouldn't be too hard to wrap a restore command around it.

The question would be what exactly you would want to see in such a proc. It would be easy enough to write a proc that does a forced restore over the top of the existing database, but is that what you are after?

Here's the code for determining last backup date & location (note - only tested with disk backups, not tape). There's SQL 2000 & SQL 2005 code (I don't think there's a lot of difference, other than I've probably refined the 2005 version a little more). Bear in mind it was pulled out of a script that outputs HTML-compatible text, so some of the formatting will be redundant (using LEN, RTRIM et al).

2000:

[font="Courier New"]DECLARE @sql nvarchar(4000),

@maxlen1 smallint,

@maxlen2 smallint,

@maxlen3 smallint

IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmp_backups%')

DROP TABLE #tmp_backups

CREATE TABLE #tmp_backups

(

name sysname,

backupstartdate char(11),

comment varchar(50),

location nvarchar(260)

)

-- Last full backups

INSERT INTO #tmp_backups

SELECT s.name,

CAST(b.backup_finish_date AS char(11)),

CASE WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

THEN 'Last full backup was within the last 24 hours'

WHEN b.backup_finish_date > DATEADD(dd,-7,getdate())

THEN 'Last full backup was within the last week'

ELSE '***** CHECK BACKUP!!! *****'

END,

bmf.physical_device_name

FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

WHERE s.name <> 'tempdb'

AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

FROM msdb..backupset

WHERE database_name = b.database_name

AND type = 'D') -- full database backups only, not log backups

ORDER BY s.name

SELECT @maxlen1 = COALESCE((MAX(LEN(RTRIM(name))) + 2), 1)

FROM #tmp_backups

SELECT @maxlen2 = COALESCE((MAX(LEN(RTRIM(comment))) + 2), 1)

FROM #tmp_backups

SELECT @maxlen3 = COALESCE((MAX(LEN(RTRIM(location))) + 2), 1)

FROM #tmp_backups

SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

SET @sql = @sql + 'backupstartdate AS ''Backup Date'', '

SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

SET @sql = @sql + 'FROM #tmp_backups '

SET @sql = @sql + 'ORDER BY name'

EXEC (@sql)

TRUNCATE TABLE #tmp_backups

-- Last log backups

INSERT INTO #tmp_backups

SELECT s.name,

CAST(b.backup_finish_date AS char(11)),

CASE WHEN b.backup_finish_date > DATEADD(hh,-12,getdate())

THEN 'Last log backup was within the last 12 hours'

WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

THEN 'Last log backup was within the last day'

ELSE '***** CHECK BACKUP!!! *****'

END,

bmf.physical_device_name

FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

WHERE s.name <> 'tempdb'

AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

FROM msdb..backupset

WHERE database_name = b.database_name

AND type = 'L') -- log backups only, not full backups

ORDER BY s.name

SELECT @maxlen1 = COALESCE((MAX(LEN(RTRIM(name))) + 2), 1)

FROM #tmp_backups

SELECT @maxlen2 = COALESCE((MAX(LEN(RTRIM(comment))) + 2), 1)

FROM #tmp_backups

SELECT @maxlen3 = COALESCE((MAX(LEN(RTRIM(location))) + 2), 1)

FROM #tmp_backups

SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

SET @sql = @sql + 'backupstartdate AS ''Backup Date'', '

SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

SET @sql = @sql + 'FROM #tmp_backups '

SET @sql = @sql + 'ORDER BY name'

EXEC (@sql)

DROP TABLE #tmp_backups[/font]

2005:

[font="Courier New"]DECLARE @sql nvarchar(4000),

@maxlen1 smallint,

@maxlen2 smallint,

@maxlen3 smallint

IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmp_backups%')

DROP TABLE #tmp_backups

CREATE TABLE #tmp_backups

(

name sysname,

backupfinishdate datetime,

comment varchar(50),

location nvarchar(260)

)

INSERT INTO #tmp_backups

SELECT s.name,

b.backup_finish_date,

CASE WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

THEN 'Last full backup was within the last 24 hours'

WHEN b.backup_finish_date > DATEADD(dd,-7,getdate())

THEN 'Last full backup was within the last week'

ELSE '***** CHECK BACKUP!!! *****'

END,

bmf.physical_device_name

FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

WHERE s.name <> 'tempdb'

AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

FROM msdb..backupset

WHERE database_name = b.database_name

AND type = 'D') -- full database backups only, not log backups

ORDER BY s.name

SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)

FROM #tmp_backups

SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)

FROM #tmp_backups

SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)

FROM #tmp_backups

SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '

SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

SET @sql = @sql + 'FROM #tmp_backups '

SET @sql = @sql + 'ORDER BY name'

EXEC (@sql)

TRUNCATE TABLE #tmp_backups

-- Last log backups

INSERT INTO #tmp_backups

SELECT s.name,

b.backup_finish_date,

CASE WHEN b.backup_finish_date > DATEADD(hh,-12,getdate())

THEN 'Last log backup was within the last 12 hours'

WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

THEN 'Last log backup was within the last day'

ELSE '***** CHECK BACKUP!!! *****'

END,

bmf.physical_device_name

FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

WHERE s.name <> 'tempdb'

AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

FROM msdb..backupset

WHERE database_name = b.database_name

AND type = 'L') -- log database backups only, not full backups

ORDER BY s.name

SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)

FROM #tmp_backups

SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)

FROM #tmp_backups

SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)

FROM #tmp_backups

SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '

SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

SET @sql = @sql + 'FROM #tmp_backups '

SET @sql = @sql + 'ORDER BY name'

EXEC (@sql)

DROP TABLE #tmp_backups

[/font]