Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


last backup date query


last backup date query

Author
Message
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7381 Visits: 15095
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
extremenovice
extremenovice
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 1140
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. :-)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36026 Visits: 18732
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
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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, MVP, M.Sc (Comp Sci)
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


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7381 Visits: 15095
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
extremenovice
extremenovice
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 1140
Great, thanks for all your advice on my queries John, Steve and Gilamonster wish me luck! :-)
JohnBevan
JohnBevan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 58
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
   Wink 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


poldi.rijke
poldi.rijke
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 475
You can run John Mitchell's query on a Server Group (right click on folder icon > New Query) within your Registered Servers.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7381 Visits: 15095
Yes, that'll work if you want to run it interactively. If you want to automate it, you'll need to centralise using SSIS or similar.

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search