November 7, 2014 at 4:18 pm
Hi All,
I am working with backup and restores.
We use to keep 4 days backups. There is a requirement to restore the latest backup of database for the testing environment.
I need the script to identify the latest backup of individual or all databases in that network path
November 7, 2014 at 5:49 pm
Here is a script that gives you the most recent full that is less than seven days old for a given database:
declare
@max_backup_set_id int,
@db_name varchar(50),
@file_name varchar(255)
select
@db_name = '$($dbName)'
select
@max_backup_set_id = max(backup_set_id)
from
msdb.dbo.backupset
select top1
@file_name = b.physical_device_name
from
msdb.dbo.backupset a
join msdb.dbo.backupmediafamily b
on a.media_set_id = b.media_set_id
where
backup_set_id > (@max_backup_set_id - 10000) and
type = 'D' and
database_name = @db_name and
b.physical_device_name not like 'VDI%' and
backup_finish_date > dateadd(hh,-(7*24),getdate())
order by
backup_finish_date
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply