Script to list all backup files starting from the latest full backup

• Roust_m

  SSCoach

  Points: 17410

  More actions

  May 14, 2020 at 5:10 am

  #3751399

  Hi,

  I am trying to write a query to list all backup files starting from the latest full backup, following by the latest diff backup (if one exists and is later then the full backup) and all log backups since the latest full or diff backup.  The purpose of this is to automatically copy those files to a dev environment and restore them in that environment, also automatically.

  Below is what I came up with so far.  It works fine if the backups go to a single file.  But in case of multiple stripes it does not work because of this section:

   rownum = 
  row_number() over
  (
  partition by database_name, type 
  order by backup_finish_date desc
  )

  I need to number the stripes from the same set with the same number and can't find a way to do so.

  Any ideas?

  Thanks.

  declare @backup_list table (ServerName sysname, DBName sysname, BackupType varchar(50), BackupFinishDate datetime, PhysicalDeviceName varchar(512))
  
  declare @GroupID int 
  
  select @GroupID = 1
  ;with backup_cte as
  (
  select
  database_name,
  backup_type =
  case type
  when 'D' then 'database'
  when 'L' then 'log'
  when 'I' then 'differential'
  else 'other'
  end,
  backup_finish_date,
  rownum = 
  row_number() over
  (
  partition by database_name, type 
  order by backup_finish_date desc
  ),
  m.physical_device_name
  from msdb.dbo.backupset s
  INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
  )
  insert into @backup_list(ServerName, DBName, BackupType, BackupFinishDate, PhysicalDeviceName)
  select 
  @@SERVERNAME,
  database_name,
  backup_type,
  backup_finish_date, 
  physical_device_name
  from backup_cte
  where rownum = 1 and backup_type in ('database') and is_copy_only = 0
  order by database_name;
  ;with backup_cte as
  (
  select
  database_name,
  backup_type =
  case type
  when 'D' then 'database'
  when 'L' then 'log'
  when 'I' then 'differential'
  else 'other'
  end,
  backup_finish_date,
  rownum = 
  row_number() over
  (
  partition by database_name, type 
  order by backup_finish_date desc
  ),
  m.physical_device_name
  from msdb.dbo.backupset s
  INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
  )
  insert into @backup_list(ServerName, DBName, BackupType, BackupFinishDate, PhysicalDeviceName)
  select
  @@SERVERNAME,
  database_name,
  backup_type,
  backup_finish_date, 
  physical_device_name
  from backup_cte c
  join @backup_list l
  on c.database_name = l.DBName
  where rownum = 1 and backup_type in ('differential') and c.backup_finish_date > l.BackupFinishDate and is_copy_only = 0
  order by database_name;
  ;with backup_cte as
  (
  select
  database_name,
  backup_type =
  case type
  when 'D' then 'database'
  when 'L' then 'log'
  when 'I' then 'differential'
  else 'other'
  end,
  backup_finish_date,
  rownum = 
  row_number() over
  (
  partition by database_name, type 
  order by backup_finish_date desc
  ),
  m.physical_device_name
  from msdb.dbo.backupset s
  INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
  )
  insert into @backup_list(ServerName, DBName, BackupType, BackupFinishDate, PhysicalDeviceName)
  select
  @@SERVERNAME,
  database_name,
  backup_type,
  backup_finish_date, 
  physical_device_name
  from backup_cte c
  join (select DBName, MAX(BackupFinishDate) BackupFinishDate from @backup_list group by DBName) l on c.database_name = l.DBName
  where backup_type in ('log') and c.backup_finish_date > l.BackupFinishDate and is_copy_only = 0
  order by database_name;
  select ServerName, @GroupID as GroupID, DBName, BackupType, BackupFinishDate, PhysicalDeviceName from @backup_list order by DBName, BackupFinishDate

   

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 14, 2020 at 7:23 am

  #3751415

  Try partitioning on backup_set_id.

  John