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.