March 11, 2011 at 8:55 pm
Here is the details what i am looking for.
1) That all databases that are in full recovery model are having the logs backed up as well.
2) Also need max(backup_start_date) for the log backup for each database and where there is none it would be null. So,it show, Server, database_name, recovery_model, max_backup_start_date (or complete date), backup_type?
3) If there is no Lob backup in current date it should be display No Log Backup with other columns.
Thanks
March 12, 2011 at 4:54 am
I'm misunderstanding what you're asking for here. Do you want a script that performs backups or do you want a script that reports on what backups have been completed?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2011 at 11:23 pm
Thanks for your reply.
I need script for report that shows backup completed.
If there is no backup (i.e Log, Differential). It suppose to display Servername, Database name, Max(backup_finish_date), Max(backup_start_date) .
But in backup column if there is no backup it suppose to be null.
Once again thanks for your help
March 14, 2011 at 1:22 am
EasyBoy (3/13/2011)
Thanks for your reply.I need script for report that shows backup completed.
If there is no backup (i.e Log, Differential). It suppose to display Servername, Database name, Max(backup_finish_date), Max(backup_start_date) .
But in backup column if there is no backup it suppose to be null.
Once again thanks for your help
With what parts of the scripts are you struggling with?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2011 at 5:35 am
If the goal is to identify which servers or databases have a failed backup, instead of running reports, I'd suggest taking a look at setting up Policy Based Management. You can have it check on the backups, backup age, SQL Agent, and all sorts of other things, quickly and easily. Check it out here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2011 at 10:58 pm
Grant Fritchey (3/14/2011)
If the goal is to identify which servers or databases have a failed backup, instead of running reports, I'd suggest taking a look at setting up Policy Based Management. You can have it check on the backups, backup age, SQL Agent, and all sorts of other things, quickly and easily. Check it out here.
I am struggling with one problem.
Where there is no log backup it suppose to display the information as i listed ablove. But i am not able to get that information.
If there is no backup script should display example listed below
Server Name DB Name Backup_Finish_Date Backup_st_date Backup_Tyype
SQLDEV Harry N/A N/A No Backup
March 15, 2011 at 5:38 am
EasyBoy (3/14/2011)
Grant Fritchey (3/14/2011)
If the goal is to identify which servers or databases have a failed backup, instead of running reports, I'd suggest taking a look at setting up Policy Based Management. You can have it check on the backups, backup age, SQL Agent, and all sorts of other things, quickly and easily. Check it out here.I am struggling with one problem.
Where there is no log backup it suppose to display the information as i listed ablove. But i am not able to get that information.
If there is no backup script should display example listed below
Server Name DB Name Backup_Finish_Date Backup_st_date Backup_Tyype
SQLDEV Harry N/A N/A No Backup
Since you can't pull out of the database what isn't there, you'd need some mechanism to perform a left join against so that the missing data is displayed as NULL values. Maybe a table of dates or something like that, but it would have to be able to be joined against in a meaningful way for the data that exists.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 15, 2011 at 6:21 am
Brent Ozar's Blitz script has a snippet that does what you are looking for: finds any database that is in FULL recovery mode, but no transaction Log backup exists.
As Grant identified, you have to joib the list of databases to teh backups with a left join to find it.
/*
Transaction log backups - do we have any databases in full recovery mode
that haven't had t-log backups? If so, we should think about putting it in
simple recovery mode or doing t-log backups.
*/
SELECT d.name, d.recovery_model, d.recovery_model_desc
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'L'
WHERE d.recovery_model IN (1, 2) AND b.type IS NULL AND d.database_id NOT IN (2, 3)
Lowell
March 15, 2011 at 8:30 am
once you find out what is NOT being done, go to ola.hallengren.com and start to use the awesome FREE database maintenance scripts there to handle all your MX needs, including backups.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply