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

Find Latest Backup Expand / Collapse
Author
Message
Posted Wednesday, August 17, 2011 9:15 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 1,462, Visits: 3,033
Comments posted to this topic are about the item Find Latest Backup

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #1161528
Posted Thursday, August 18, 2011 3:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 5:56 PM
Points: 864, Visits: 2,520
For some reason, when I ran this query in it's original form, it returned NULL rather than the name of the latest .bak file

I changed the last line of the final select to:

WHERE FileNames like ''+'%'+ @DatabaseName +'%'+'' 



It seems to be working ok now

a useful code snippet - thanks :)
Post #1162151
Posted Friday, August 19, 2011 3:42 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475
If someone took a backup into a folder only s/he knows, you are not going to find it this way. You should query msdb to find backup information.

Regards,
Jason
http://usa.redirectme.net

By the way, you may not have privilege to change sp_configure options. even with backup permission. Backup and admin are different things, permission can be granted separately.

BOL: To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server role.


Jason
http://dbace.us
Post #1162787
Posted Monday, August 22, 2011 6:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:49 AM
Points: 16, Visits: 432
Why not use this script to get the latest backups ...

SELECT DatabaseName=a.database_name, BackupDate=a.backup_date,
PhysicalDeviceName=physical_device_name,
BackupSize=backup_size,
Duration=duration
FROM
(SELECT sd.name AS database_name,
MAX(bs.backup_finish_date) AS backup_date
FROM master.dbo.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name
LEFT OUTER JOIN
(
SELECT sd.name AS database_name,
MAX(bs.backup_finish_date) AS backup_date,
bm.physical_device_name,
bs.backup_size/1024/1024 as backup_size,
DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) as duration
FROM master.dbo.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name
LEFT OUTER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = bs.media_set_id
GROUP BY sd.name, bm.physical_device_name, bs.backup_size/1024/1024,
DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date)
) sq1
ON sq1.database_name = sd.name
AND sq1.backup_date = bs.backup_finish_date
GROUP BY sd.name ) a,
(SELECT sd.name AS database_name,
MAX(bs.backup_finish_date) AS backup_date,
sq1.physical_device_name,
sq1.backup_size,
sq1.duration
FROM master.dbo.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name
LEFT OUTER JOIN --Must put in nested join
(
SELECT sd.name AS database_name,
MAX(bs.backup_finish_date) AS backup_date,
bm.physical_device_name,
bs.backup_size/1024/1024 as backup_size,
DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) as duration
FROM master.dbo.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name
LEFT OUTER JOIN msdb.dbo.backupmediafamily bm ON bm.media_set_id = bs.media_set_id
GROUP BY sd.name, bm.physical_device_name, bs.backup_size/1024/1024,
DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date)
) sq1
ON sq1.database_name = sd.name
AND sq1.backup_date = bs.backup_finish_date
GROUP BY sd.name, bs.backup_finish_date, sq1.physical_device_name, sq1.backup_size, sq1.duration
) b
where a.database_name=b.database_name
and a.backup_date=b.backup_date
ORDER BY DatabaseName

Post #1163251
Posted Monday, September 19, 2011 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:54 PM
Points: 2, Visits: 152
i tried this script , but its not working ,
Post #1177275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse