Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

last backup date query Expand / Collapse
Author
Message
Posted Wednesday, March 23, 2011 5:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 AM
Points: 5,077, Visits: 8,918
What do you mean by a more printable format?

To run on all your servers at once, I think you can set up a job and target it at more than one server. I've never actually done this myself, so I would advise you to go and read about it and start a new thread if you find yourself still needing help in setting it up.

John
Post #1082617
Posted Wednesday, March 23, 2011 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:26 AM
Points: 98, Visits: 1,057
Thanks for that John.
I was thinking of something that i could automate and have it output to a report as apposed to running the query each and every time i needed that information. At the moment I've not found anything like that within sql apart from this tip (http://www.mssqltips.com/tip.asp?tip=2012&home) it is different to each version of sql, but the two reports i have used are ok. I will use your script though that's for sure and will research further for running the job across many servers.
Post #1082776
Posted Wednesday, March 23, 2011 9:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:11 PM
Points: 32,810, Visits: 14,959
Typically you want to be aware of issues, not making checks to see what has run. That's a bit of a waste of your time. Assume the backups are working correctly and set up monitoring to let you know if they aren't working.

I would suggest that you setup a small database on each instance and then have a job that runs this query and stores the results in that database. You can setup a query in a job to alert you if something in the result is amiss and needs to be looked at.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1082780
Posted Wednesday, March 23, 2011 9:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:39 AM
Points: 41,547, Visits: 34,468
Steve Jones - SSC Editor (3/23/2011)
Typically you want to be aware of issues, not making checks to see what has run. That's a bit of a waste of your time. Assume the backups are working correctly and set up monitoring to let you know if they aren't working.


And make sure that the monitoring will tell you if the backup hasn't run, not just if it ran and failed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1082786
Posted Wednesday, March 23, 2011 9:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 AM
Points: 5,077, Visits: 8,918
Yes. We use the query to report from all our servers to a central server. That is then compared against a list of databases that should have been backed up. Finally, we check that the backup files exist in the specified locations.

John
Post #1082792
Posted Thursday, March 24, 2011 6:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:26 AM
Points: 98, Visits: 1,057
Great, thanks for all your advice on my queries John, Steve and Gilamonster wish me luck!
Post #1083213
Posted Tuesday, June 12, 2012 6:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 05, 2013 5:04 AM
Points: 2, Visits: 43
Here's a version that'll run on one database and produce results for all databases (assuming they're setup as linked servers on the main db).

Could be further improved by putting the type lookup into a permanent table and outputting results of dynamic sql to a temp table from where you could feed it into whatever additional queries / functionality you wanted.

declare @server_id int 
declare @sql nvarchar(max)

set @server_id = -1
while exists (select top 1 1 from sys.servers where server_id > @server_id)
begin

select top 1 @server_id = server_id, @sql = QUOTENAME(name)
from sys.servers
where server_id > @server_id
order by server_id

set @sql = 'select *
from
(
select QUOTENAME(bs.server_name) + ''.'' + QUOTENAME(bs.database_name) DBName
, bs.type BackupTypeCode --included since it looks like this field could be null, so not restricted to the lookup values
, t.descrptn BackupType
, COALESCE(Convert(nvarchar(20), backup_finish_date, 113),''Backup Not Taken'') FinishDate
, COALESCE(Convert(nvarchar(20), backup_size, 101),''NA'') Size
, COALESCE(Convert(nvarchar(20), compressed_backup_size, 101),''NA'') CompressedSize
, COALESCE(Convert(nvarchar(20), backup_set_id, 101),''NA'') SetID
, name BackupName
, ROW_NUMBER() over (partition by bs.database_name, bs.type order by bs.backup_start_date desc) r
from ' + @sql + '.msdb.dbo.backupset bs
left outer join
(
select ''D'' id, ''Database'' descrptn
union select ''I'' ,''Differential database''
union select ''L'' ,''Log''
union select ''F'' ,''File or filegroup''
union select ''G'' ,''Differential file''
union select ''P'' ,''Partial''
union select ''Q'' ,''Differential partial''
) t on bs.type = t.id
) x where r = 1
order by DBName, BackupTypeCode
'

exec (@sql) --you could insert to a temp table / something like that to make use of the results later

end

Post #1314372
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse