 --> (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 |
 |  | ||