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 |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2012-05-09 : 13:56:44
|
| Hello,I'm trying to retrieve the date each db was last backed-up. And I'm using a Central Management Server (CMS)The following stmt returns records for every time the database was backed-up, all the history, of course.select server_name as 'ServerName', database_name as 'DatabaseName', max(backup_start_date) as 'LastBackup', recovery_model as 'RecoveryModel' from msdb.dbo.backupsetgroup by server_name, database_name, backup_start_date, recovery_model;And this returns only 1 record:select TOP 1 server_name as 'ServerName', database_name as 'DatabaseName', max(backup_start_date) as 'LastBackup', recovery_model as 'RecoveryModel' from msdb.dbo.backupsetgroup by server_name, database_name, backup_start_date, recovery_model;How can I get the max/latest backup date along with the other columns I need?Thanks, John |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-09 : 14:21:36
|
Remove the backup_start_date from the group by clauseselect server_name as 'ServerName', database_name as 'DatabaseName', max(backup_start_date) as 'LastBackup', recovery_model as 'RecoveryModel' from msdb.dbo.backupsetgroup by server_name, database_name, backup_start_date, recovery_model; |
 |
|
|
|
|
|
|
|
TSQL Question