| Author |
 Topic  |
|
|
mitin
Yak Posting Veteran
54 Posts |
 Posted - 12/10/2012 : 15:18:40
|
Hi, I have a SQL server 2000 DB on my network and I need to know if the database has ever been backed up, and/or if these backups occur on a regular basis.
The DB is using the full recovery model, so I guess if there are backups being performed these would be full, differential and transaction log (although I suppose you can still choose which of these you want to occur with the full recovery model, maybe?).
Anyway, I need to know, I'd like to know how to do this through both the GUI and SQL queries, but either way will do to get the job done, from googling, it seems pretty hard to find a concrete answer.
Hope you don't mind this question, thanks for any help :) |
Edited by - mitin on 12/10/2012 15:19:52
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
Posted - 12/11/2012 : 01:56:40
|
The relevant tables are : backupset, and backupmediafamily . They join on the "media_set_id" column
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
 |
|
|
mitin
Yak Posting Veteran
54 Posts |
Posted - 12/11/2012 : 05:16:36
|
Thanks guys, I can see in the backupset family that one backup has been taken before, that is all, looks like this wasn't scheduled and just a manual backup when required that one time.
If scheduled backups occurred, say every week for example, then there would be an entry for each of these in this table right?
thanks for the info! |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
|
|
shiyam198
Yak Posting Veteran
82 Posts |
Posted - 12/11/2012 : 11:04:02
|
I usually go to the database and run this
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
|
|
|
| |
Topic |
|
Ho do I know if a DB has ever been backed up?
