SQL Server – T-SQL procedure to Copy a File from within SQL Server

--> (Word) --> (PDF) --> (Epub)
This article has been published [fromdate]

Usually in a backup script we want not just to do the backup but also want to copy the results of the backup to another place (for safe keeping). If The Backup job is a windows shell script , this is simple But if the whole of the job is in T-SQL and runs from within the SQL Server environment , this rases a problem of how to copy the file across the network.

To That purpose I coded a simple (but usefull procedure) called sp_CopyFile. The procedure gets a source file Name parameter (including the Path) and a Destination File Name parameter (including the Path) and does the copy in T-SQL. That way the logical sequence of doing a Backup and then copying the file is made simple.

The procedure code:

Use master
go
Create Proc sp_CopyFile (@sourceFile varchar(100),
    @destFile   varchar(100))
as
 begin
  declare @WinCmd varchar(300)

 set nocount on
 set @WinCmd = 'Copy ' + @sourceFile +
        ' ' +
        @destFile
 exec master..xp_cmdShell @WinCmd
 set nocount off
 end
go

Example of How to Use the procedure:

-- Backup Master database the local Hard drive and then copy;

-- the backup file to a network;

-- location (double backup);

BackUp database Master to Disk='c:\BackUp\msSQL\Master.bak'

exec master..sp_CopyFile 'c:\BackUp\msSQL\Master.bak',

'\\NTMAHR11\13411$\XP\I\Master.bak'

The expected result : (according to database master's size)

Processed 2040 pages for database 'Master', file 'master' on file 2.
Processed 1 pages for database 'Master', file 'mastlog' on file 2.
BACKUP DATABASE successfully processed 2041 pages in 1.136 seconds (14.711 MB/sec).

Output:

Processed 2040 pages for database 'Master', file 'master' on file 2.
Processed 1 pages for database 'Master', file 'mastlog' on file 2.
BACKUP DATABASE successfully processed 2041 pages in 1.136 seconds (14.711 MB/sec).
output
-------------------------------------------------------------------------        
1 file(s) copied.
SOURCE

LINK (Sswug.org)

LANGUAGE
ENGLISH