Last Database Backup Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Last Database Backup Date

Author

Topic

ramaiah
Starting Member

27 Posts

Posted - 2010-02-17 : 08:28:20

hi,

how i can get the SQLServer instance name, version of the sqlserver, database name, recovery model,backup type, last backup date & is there any backup scheduled on this database using t-sql.

thanks in advance

regards
ram.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-02-17 : 08:50:32

Dear Ramaiah,
if you want to know the instance name and other basic information, please go through the link below
http://msdn.microsoft.com/en-us/library/aa259183(SQL.80).aspx

regarding getting the backup information, use the below procedure

CREATE PROC dbo.uspGetDBBackupInfo
@startDate DATETIME,
@endDate DATETIME,
@database SYSNAME = NULL
AS
SELECT b.database_name,
b.backup_start_date,
b.backup_finish_date,
b.user_name,
f.logical_name,
f.physical_name,
mf.physical_device_name,
f.file_type,
f.file_size,
b.backup_size
FROM msdb.dbo.backupfile f,
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE f.backup_set_id = b.backup_set_id
AND b.media_set_id = mf.media_set_id
AND b.backup_start_date BETWEEN @startDate
AND @endDate
AND b.database_name = COALESCE
(@database,database_name)
ORDER BY b.database_name,
b.backup_start_date

Ex : exec uspGetDBBackupInfo @startDate='2008-02-17 07:42:51.563',@endDate='2010-02-17 07:42:51.563',@database='Database_Name'

Arnav
Even you learn 1%, Learn it with 100% confidence.

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 08:59:58

Probably does the same thing but there is a script here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources