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.
| Author |
Topic |
|
mitin
Yak Posting Veteran
81 Posts |
 Posted - 2012-12-10 : 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 :) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-11 : 01:56:40
|
| The relevant tables are : backupset, and backupmediafamily . They join on the "media_set_id" columnJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2012-12-11 : 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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-11 : 07:11:12
|
| Yes , there would be entries. Unless someone clears down the tablesUse this query for view the backup history - [url]http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.html[/url]Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2012-12-11 : 11:04:02
|
| I usually go to the database and run thisSELECT TOP 100s.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_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_idWHERE s.database_name = DB_NAME() -- Remove this line for all the databaseORDER BY backup_start_date DESC, backup_finish_dateGO |
|
|
|
|
|
|
|
|
Ho do I know if a DB has ever been backed up?