Backup File Transfer using T-sql

Create procedure backupfiletransfer

as begin

create table #backupdirectory

(

physicallocation varchar(1000),

Transferstatus int,

dbname sysname

)

insert into #backupdirectory

SELECT top (1)

 c.physical_device_name,0 as tstatus,

 a.name

FROM master..sysdatabases a

LEFT OUTER JOIN msdb..backupset b

ON a.name = b.database_name

LEFT OUTER JOIN msdb..backupmediafamily c ON b.media_set_id = c.media_set_id

where a.name in ('DBA') and  b.type = 'D'

GROUP BY a.name, b.type,c.physical_device_name,b.backup_finish_date

ORDER BY b.backup_finish_date DESC

insert into #backupdirectory

SELECT top (1)

 c.physical_device_name,0 as tstatus,

  a.name

FROM master..sysdatabases a

LEFT OUTER JOIN msdb..backupset b

ON a.name = b.database_name

LEFT OUTER JOIN msdb..backupmediafamily c ON b.media_set_id = c.media_set_id

where a.name in ('msdb') and  b.type = 'D'

GROUP BY a.name, b.type,c.physical_device_name,b.backup_finish_date

ORDER BY b.backup_finish_date DESC

Declare @count int

set @count =1

while @count <= (select count(*) from #backupdirectory where Transferstatus=0)

Begin

Declare @physicallocation varchar(1000)

Declare @dbname sysname

select top(1) @physicallocation=physicallocation, @dbname=dbname  from #backupdirectory where Transferstatus=0

DECLARE @cmd varchar(4000)

Declare @transferLocation varchar(1000)

set @transferLocation='network path'

SET @cmd = 'copy'+' '+ @physicallocation + ' ' + @transferLocation+@dbname+'.bak'

EXECUTE AS login = 'service account'

EXEC master.dbo.xp_cmdshell @cmd

update #backupdirectory set Transferstatus=1 where dbname=@dbname

End

End