June 6, 2011 at 12:03 pm
Greetz!
Our SQL Server 2005 databases are supposed to be backupped each night by our network operations center but for the past several months their backup software has been failing periodically. So I'd like to know if there is a way I can check on SQL Server itself to see if a backup has been performed.
I've used the script below but it shows databases that aren't in use any more... at least I no longer see them in Enterprise Manager.
SELECT @@SERVERNAME AS ServerName
, database_name AS [DBName]
, MAX(backup_finish_date) AS [LastBackup]
FROM msdb.dbo.backupset
WHERE type = 'D'
GROUP BY database_name
ORDER BY database_name
Is there a way to remove the db's that are no longer in use?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 7, 2011 at 6:40 am
If databases that are no longer on the server are in the backup history, that just means the history wasn't cleared when the database was dropped. You can use sp_delete_backup_history to clean out older data.
Other than that, you're in the right spot to see what backups have been run.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 7, 2011 at 6:50 am
If by "no longer in use" you mean the databases have been dropped, you could join that query to sys.databases, and that would filter it for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 7:01 am
Gus is right.
I think sp_delete_backuphistory is date specific, not database specific, so you may end up throwing the baby out with the bathwater if you use it.
John
June 7, 2011 at 7:04 am
John Mitchell-245523 (6/7/2011)
Gus is right.I think sp_delete_backuphistory is date specific, not database specific, so you may end up throwing the baby out with the bathwater if you use it.
John
It is date based. I guess pointing that out might be in order. Don't run it further back than you want to check your backups for. Seems like common sense, but it's easy to dig holes.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply