Find Latest Backup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Carolyn Richardson » Find Latest Backup

Find Latest Backup

Rate Topic

Display Mode

Topic Options

Author

Message

Carolyn Richardson

Posted Wednesday, August 17, 2011 9:15 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 1,397, Visits: 2,738

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

Ivanna Noh

Posted Thursday, August 18, 2011 3:46 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 777, Visits: 2,306

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

jswong05

Posted Friday, August 19, 2011 3:42 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:54 AM
Points: 27, Visits: 421

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
Tongue

Post #1162787

Oliver Wootton

Posted Monday, August 22, 2011 6:43 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:27 AM
Points: 11, Visits: 332

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

yogigollapudi

Posted Monday, September 19, 2011 8:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:21 AM
Points: 2, Visits: 68

i tried this script , but its not working ,

Post #1177275

« Prev Topic | Next Topic »

Permissions