September 3, 2009 at 6:42 am
I have to get name of first sunday backup of last month for cleanup routine.
I cannot use maintenance plan.
I wrote the following query to get the backup name.
Is there any thing that I missed?
I am planning to move this file to a different location and delete all other backups in a folder
I am just worried that I should not end up with no backups.
declare @Backup_to_archive varchar(255)
set @Backup_to_archive=
( select top 1 substring(physical_device_name,14,len(physical_device_name)-13) from msdb.dbo.backupset b left join msdb.dbo.backupmediafamily m
on b.media_set_id =m.media_set_id
where b.database_name=@databaseName and
month(backup_start_date)=month (getdate())-1 and
datename(dw,backup_start_date)='Sunday' order by backup_start_date )
September 3, 2009 at 7:11 am
You aren't limiting the list to a particular type of backup (Full, Differential or Log)... is this really what you want?
I suspect the script should include AND Type = 'D'
September 3, 2009 at 7:22 am
Thanks Ian. You are correct, I needed only full backups. I have included that in my query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply