Handy Script to Copy the latest backup file between locations

/*
Written by : Mohammad Sufian
Location : Singapore

Copywrite : None.
Suggestion/Feedback : gosgenq@gmail.com

Supported versions: SQL Server 2008 R2 to SQL Server 2014
*/


CREATE PROCEDURE dbo.usp_copybackupfiles 
@searchdirectory	 nvarchar(4000),
@filefilter			 varchar(7),
@database			 varchar(128),
@Destination		 nvarchar(4000)

AS
BEGIN

  SET NOCOUNT ON


  DECLARE @command		varchar(8000)
  --DECLARE @searchdirectory	nvarchar(4000)	='H:Backups'
  --DECLARE @filefilter		varchar(7)	='.bak'
  --DECLARE @database		varchar(128)	='AdventureWorks2014'
  DECLARE @useallmatchingfiles	int		=		0

  DECLARE @RestoreDataLocation	nvarchar(4000)
  DECLARE @RestoreLogLocation	nvarchar(4000)

  DECLARE @start int=1
  DECLARE @end int

  DECLARE @Source			nvarchar(4000)
  --DECLARE @Destination		nvarchar(4000)='H:Backupscopyfiles'
  DECLARE @cmd				nvarchar(4000)
  DECLARE @cmd2				nvarchar(4000)
  DECLARE @backupfile			nvarchar(4000)
  DECLARE @checkfile			nvarchar(4000)
  DECLARE @combineBackups		varchar(max)

  IF NOT EXISTS (select * from master.sys.sysobjects where name='CopyBackupFileStatus')
    BEGIN
      CREATE TABLE master.dbo.CopyBackupFileStatus
      (
      id int identity,
      sourcelocation nvarchar(4000) not null,
      destinationlocation nvarchar(4000) not null,
      [filename]			nvarchar(4000) not null,
      [copydate]			datetime
      )
    END



  create table #Output (outputtext varchar(max))

  DECLARE @BackupFiles Table (id int identity,BackupFileName nvarchar(4000))
  DECLARE @fileexists TABLE (File_Exists BIT, File_Is_A_Directory BIT, Parent_Directory_Exists BIT)
  DECLARE @MovedBackupFiles Table (id int identity,BackupFileName nvarchar(4000))
  CREATE TABLE #vDBHList 
  (
  DBID INT NOT NULL       IDENTITY(1, 1),BackupName        nvarchar(128),BackupDescription  nvarchar(255),
  BackupType              smallint,ExpirationDate          datetime,Compressed              tinyint,
  Position                smallint,DeviceType              tinyint,UserName                 nvarchar(128),
  ServerName              nvarchar(128),DatabaseName       nvarchar(128),DatabaseVersion    int,
  DatabaseCreationDate    datetime,BackupSize              numeric(20,0),FirstLSN           numeric(25,0),
  LastLSN                 numeric(25,0),CheckpointLSN      numeric(25,0),DatabaseBackupLSN  numeric(25,0),
  BackupStartDate         datetime,BackupFinishDate        datetime,SortOrder               smallint,
  CodePage                smallint,UnicodeLocaleId         int,UnicodeComparisonStyle       int,
  CompatibilityLevel      tinyint,SoftwareVendorId         int,SoftwareVersionMajor         int,
  SoftwareVersionMinor    int,SoftwareVersionBuild         int,MachineName                  nvarchar(128),
  Flags                   int,BindingID                    uniqueidentifier,RecoveryForkID  uniqueidentifier,
  Collation               nvarchar(128),FamilyGUID         uniqueidentifier,HasBulkLoggedData       bit,
  IsSnapshot              bit,IsReadOnly                   bit,IsSingleUser            bit,
  HasBackupChecksums      bit,IsDamaged                    bit,BeginsLogChain          bit,
  HasIncompleteMetaData   bit,IsForceOffline               bit,IsCopyOnly              bit,
  FirstRecoveryForkID     uniqueidentifier,ForkPointLSN    numeric(25,0) NULL,RecoveryModel           nvarchar(60),
  DifferentialBaseLSN     numeric(25,0) NULL,DifferentialBaseGUID    uniqueidentifier,
  BackupTypeDescription   nvarchar(60),BackupSetGUID       uniqueidentifier NULL
  )
  Declare @version varchar(2)
  select @version = substring(convert(nvarchar(100),serverproperty('ProductVersion')),0, 
                                    charindex('.',convert(nvarchar(100),serverproperty('ProductVersion'))))
  --print @version
  if (convert (int,@version ) <12)
    begin
     ALTER TABLE #vDBHList  ADD CompressedBackupSize int
     ALTER TABLE  #vDBHList  ADD Containment int	 
    end

  else if (convert (int,@version ) >=12)
    begin
     ALTER TABLE #vDBHList  ADD CompressedBackupSize int
     ALTER TABLE  #vDBHList  ADD Containment int	 
     ALTER TABLE  #vDBHList  ADD KeyAlgorithm nvarchar(32)	 
     ALTER TABLE  #vDBHList  ADD EncryptorThumbprint varbinary(20)	 
     ALTER TABLE  #vDBHList  ADD EncryptorType nvarchar(32)	 
    end

  --select @command = 'powershell "$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (1500, 25); $a = Get-Date; $a = $a.AddDays(-6); ' 
  select @command = 'powershell "' 
  select @command += '$path = '''+@searchdirectory+''';$files = @();$path.split('','') |% {$innerPath = $_; $files += get-childitem $innerpath}; '
  --select @command += '$files = $files  |? {$_.CreationTime -gt $a  -AND $_.Extension -eq '''+UPPER(@filefilter)+''' -AND $_.name.toupper().contains('''+UPPER(@database)+''')};  ; '
  select @command += '$files = $files  |? {$_.Extension -eq '''+UPPER(@filefilter)+''' -AND $_.name.toupper().contains('''+UPPER(@database)+''')};  ; '
  select @command += ' $files '

  if @useallmatchingfiles = 0 
  select @command += ' |? {$_.CreationTimeUTC -gt (($files | sort CreationTimeUTC -desc | select CreationTimeUTC -unique -first 1).CreationTimeUTC).AddSeconds(-1)} '

  select @command += ' | ft fullname;"'

  ----print @command
  insert into #Output exec xp_cmdshell @command

  DELETE from #Output where outputtext IS NULL or outputtext ='FullName                                                                       ' 
  or  outputtext='--------                                                                       '
  insert into @BackupFiles
  SELECT * from #Output
  SET @end=@@ROWCOUNT

    WHILE @start <= @end

      BEGIN

        SELECT @Source=BackupFileName FROM @BackupFiles WHERE id=@start	
        SET @cmd='copy '+@Source+ ' ' +@Destination
        --print @Source

        SET @backupfile=REVERSE(SUBSTRING (REVERSE(@source),0,CHARINDEX('',REVERSE(@source))))

          --//--
          --Check if file exists and do the verfication
          --//--
              SET @checkfile=@Destination+@backupfile
        
              insert into @fileexists
              exec master..xp_fileexist  @checkfile
        
              if (select File_Exists from  @fileexists)=1
                begin								
                  set @cmd2='RESTORE HEADERONLY FROM DISK= '+''''+@checkfile+''''
                  insert into #vDBHList
                  exec(@cmd2)								
                  insert into @MovedBackupFiles values (@checkfile)
                end						
              else
                begin
                  EXECUTE xp_cmdshell @cmd,no_output
                  insert into @MovedBackupFiles values (@checkfile)
                  insert into master.dbo.CopyBackupFileStatus(sourcelocation,destinationlocation,[filename],copydate) values (@Source,@Destination,@backupfile,GETDATE())
                end
              DELETE FROM @fileexists
              --print @backupfile			
        SET @start=@start+1
      END
    
  drop table #vDBHList
  drop table #Output

  SET NOCOUNT OFF

END

 

How to use it?

usp_copybackupfiles 
@searchdirectory='H:\Backups',
@filefilter='.bak',
@database='AdventureWorks2014',
@Destination='H:\Backupscopyfiles'

The script simple reads the file information from disk and copy the files to destination.