August 17, 2011 at 9:15 pm
Comments posted to this topic are about the item Find Latest Backup
August 18, 2011 at 3:46 pm
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 🙂
August 19, 2011 at 3:42 pm
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
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
😛
August 22, 2011 at 6:43 am
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
September 19, 2011 at 8:42 am
i tried this script , but its not working ,
May 12, 2016 at 7:19 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply