<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
SQL Server Agent
»
last backup date query
17 posts, Page 1 of 2
1
2
»»
last backup date query
Rate Topic
Display Mode
Topic Options
Author
Message
harriet.louis
harriet.louis
Posted Tuesday, June 09, 2009 6:31 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, July 12, 2010 8:36 AM
Points: 26,
Visits: 98
Does anyone know how SQL Server Management Studio knows when the last backup took place?
The specific query I am looking for is the the one that would populate the properties page if you right click on a database and select properties.
preferably this query will have nothing to do with sysjob tables, as I am trying to avoid working with these...
Post #731376
harriet.louis
harriet.louis
Posted Tuesday, June 09, 2009 6:36 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, July 12, 2010 8:36 AM
Points: 26,
Visits: 98
i found the answer...
and thought i'd share it for those of you who r interested:
select database_name,max(backup_finish_date) as backup_finish_date from msdb..backupset
where database_name='Libra'
group by database_name
There is also a useful article at:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LastBackUpDate
Post #731384
Lynn Pettis
Lynn Pettis
Posted Tuesday, June 09, 2009 8:14 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 21,589,
Visits: 27,388
Just a little thing, but may I suggest you write the query more like this:
select
database_name,
max(backup_finish_date) as backup_finish_date
from
msdb.dbo.backupset -- really should specify owner (SQL 2000)/schema (SQL 2005/2008)
where
database_name = 'Libra'
group by
database_name
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #731472
Roland [GISS]
Roland [GISS]
Posted Wednesday, October 06, 2010 7:42 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 17, 2013 6:42 AM
Points: 3,
Visits: 27
Hi! The query is great, but I miss something that I want to use in my environment. I would also like to see the size and the set_id of that last (log) backup.
Tried this one:
select Database_name,
COALESCE(Convert(varchar(20), MAX(backup_finish_date), 113),'Backup Not Taken') as
LastBackUpTakenDate,
COALESCE(Convert(float, MAX(backup_size), 101),'NA') as BUsize,
COALESCE(Convert(float, MAX(backup_set_id), 101),'NA') as BUid
from msdb.dbo.backupset where type='L'
GROUP BY Database_name
But this gives me the last backup date, the maximum backup size, and the maximum backup_set_id. The first and the last are the right values, but I want to see the real size of that last backup. Anyone can help please?
Thanks, Roland.
If you pay peanuts you'll get monkey's...
Post #999298
John Mitchell-245523
John Mitchell-245523
Posted Wednesday, October 06, 2010 7:58 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 4,418,
Visits: 7,156
Roland
Try this.
John
Post Attachments
LatestBackups.txt
(
62 views,
789 bytes
)
Post #999327
Roland [GISS]
Roland [GISS]
Posted Wednesday, October 06, 2010 8:05 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 17, 2013 6:42 AM
Points: 3,
Visits: 27
Thanks John for this (complex) query. It gives me some of the correct output, but I have no idea how to filter out only the L backups...
If you pay peanuts you'll get monkey's...
Post #999340
John Mitchell-245523
John Mitchell-245523
Posted Wednesday, October 06, 2010 8:08 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 4,418,
Visits: 7,156
Mmmm.... surely by adding the line
WHERE r.type = 'L'
at the end?
John
Post #999346
Roland [GISS]
Roland [GISS]
Posted Wednesday, October 06, 2010 8:47 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 17, 2013 6:42 AM
Points: 3,
Visits: 27
Thanks! That worked. Sorry for my newbie question, the query was a little too complex for me....
Regards,
Roland
If you pay peanuts you'll get monkey's...
Post #999422
extremenovice
extremenovice
Posted Wednesday, March 23, 2011 5:36 AM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:38 AM
Points: 92,
Visits: 894
Hi All and thanks John this is a great script i will be using this from now on, but is there a way of outputting this in to a more printable format??
Thanks in advance!
Post #1082613
extremenovice
extremenovice
Posted Wednesday, March 23, 2011 5:38 AM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:38 AM
Points: 92,
Visits: 894
extremenovice (3/23/2011)
Hi All and thanks John this is a great script i will be using this from now on, but is there a way of outputting this in to a more printable format??
Thanks in advance!
In conjunction with my previous post, how would i be able to run this for all my servers at once?
Once again thanks in advance!
Post #1082614
« Prev Topic
|
Next Topic »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
