SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Latest Backup


Find Latest Backup

Author
Message
Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1870 Visits: 3486
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
Ivanna Noh
Ivanna Noh
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 3024
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 Smile
jswong05
jswong05
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 476
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
:-P
Oliver Wootton
Oliver Wootton
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 555
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
yogigollapudi
yogigollapudi
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 231
i tried this script , but its not working ,
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13776 Visits: 885
Thanks for the script.
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