February 28, 2010 at 11:33 pm
Hi Guys,
I wanted to know if there is any script to copy backup files from one server to another server,
thanks in advance,
March 1, 2010 at 3:33 am
SQL backup files? Why dont you just write a batch file? If what you want is a nightly scheduled job to move files offsite (once created) i would suggest using Robocopy to do the work and then you can execute the robocopy batch from sql.
What i have done is go one further and wrap that up into a ssis with a wmi watcher to wait for completeion and the email a detailed summary.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 2, 2010 at 6:50 am
Adam,
Could you please share your SSIS package as I am also in similar situation?
Thanks in advance.
MJ
March 2, 2010 at 6:57 am
There are a number of scripts on this site to help with that. Here's one
March 2, 2010 at 9:00 am
Steve Jones - Editor (3/2/2010)
There are a number of scripts on this site to help with that. Here's one
Thanks Steve.
Sorry cant share package, too much sensitive info to release and would take me too long to sensor.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 3, 2010 at 10:53 am
If I understand you correctly use the windows XCOPY routine,
xcopy G:\backup\*.* \\otherserver\E$\backup2\ /d /c /y
March 4, 2010 at 7:55 am
You can also use vbscripting, powershell, the DOS FORFILES command, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 5, 2010 at 9:55 am
Hello,
I like powershell to do the job. Below is the code that I use: (modify as per your usage)
$file = "F:\temp\Job_logs" ## this will be your backup file folder
$archive = "F:\temp\archive\" ## this will be your destination folder
foreach ($file in gci $file -include *.bak -recurse)
{
Move-Item -path $file.FullName -destination $archive ## Move the files to the archive folder
}
HTH,
//K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 16, 2010 at 10:03 am
I have a process that does a backup and restore on remote servers. Both the backup and restore steps are initiated from a remote administration server. Here is the procedure I use to copy the files from server to server. This uses the fastcopy utility but you can modify what xp_cmdshell to whatever suits you.
Note, this is quick version I modified recently because we used to use the DOS MOVE command. There are undoubtedly several improvements that can be made but I'm not after beautiful code right now, just working code :).
/*
Handles moving files from one location to another. Intended to be used to copy backup files
from the server the file(s) reside on to another server for purposes of a restore.
@BackupServerName: The server name the backup files reside on.
@BackupPathList: Comma separated list of all the paths the backup file(s) reside on.
@BackupFileList: Comma separated list of all the backup file names. The order of @BackupPathList and @BackupFileList must match.
@RestoreServerName: The server name the backup files will be copied to
@RestoreFilePath: The location to copy the file(s) to. This currently does not support multiple paths, unlike the @BackupPathList parameter.
*/
CREATE PROCEDURE dbo.usp_dba_MoveRemoteBackupFiles
(
@BackupServerName SYSNAME,
@BackupPathList NVARCHAR(MAX),
@BackupFileList NVARCHAR(MAX),
@RestoreServerName SYSNAME,
@RestoreFilePath NVARCHAR(1000)
)
AS
SET NOCOUNT ON
/* Error handling variables */
DECLARE @ErrorSeverity INT, @ErrorState INT, @ReturnCode INT;
DECLARE @StackTrace NVARCHAR(MAX);
DECLARE @ProcedureName SYSNAME; SET @ProcedureName = OBJECT_NAME(@@PROCID);
/* SQL template variables */
DECLARE @SQLCommandTemplate NVARCHAR(MAX);
DECLARE @SQLCommand NVARCHAR(MAX);
SET @SQLCommandTemplate =
'
EXEC xp_cmdshell ''''@del c:\fastcopy\fastcopy.log'''', NO_OUTPUT;
EXEC xp_cmdshell ''''c:\fastcopy\fastcopy /cmd=move /speed=full /force_close /log "%filename%" /to="%targetpath%"'''', NO_OUTPUT;
EXEC xp_cmdshell ''''@type c:\fastcopy\fastcopy.log'''';
';
DECLARE @SQLRemoteMoveTemplate NVARCHAR(MAX);
DECLARE @SQLRemoteMove NVARCHAR(MAX);
SET @SQLRemoteMoveTemplate =
'
EXECUTE(''%movesql%'') AT [%linkedserver%];
';
DECLARE @BackupFileEntry TABLE (Sequence INT, BackupFileName SYSNAME);
DECLARE @BackupFileName SYSNAME;
BEGIN TRY
/* Strip any quoted parameters */
SET @BackupServerName = PARSENAME(@BackupServerName, 1);
SET @RestoreServerName = PARSENAME(@RestoreServerName, 1);
/* Parse @BackupFileList using a CTE to split the string out into individual elements */
WITH BackupFile (Sequence, Start, Length)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT), CHARINDEX(',', @BackupFileList)
UNION ALL
SELECT Sequence + 1, Length + 1, CHARINDEX(',', @BackupFileList, Length + 1)
FROM BackupFile
WHERE Length > 0
), BackupPath (Sequence, Start, Length)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT), CHARINDEX(',', @BackupPathList)
UNION ALL
SELECT Sequence + 1, Length + 1, CHARINDEX(',', @BackupPathList, Length + 1)
FROM BackupPath
WHERE Length > 0
)
INSERT INTO @BackupFileEntry (Sequence, BackupFileName)
SELECT
F.Sequence,
LTRIM(RTRIM(SUBSTRING(@BackupPathList, P.Start, CASE WHEN P.Length > 0 THEN P.Length - P.Start ELSE LEN(@BackupPathList) END)))
+ LTRIM(RTRIM(SUBSTRING(@BackupFileList, F.Start, CASE WHEN F.Length > 0 THEN F.Length - F.Start ELSE LEN(@BackupFileList) END)))
FROM BackupFile AS F
INNER JOIN BackupPath AS P
ON F.Sequence = P.Sequence
OPTION (MAXRECURSION 0);
/* Iterate through each file and move */
DECLARE curFiles CURSOR LOCAL FAST_FORWARD
FOR
SELECT BF.BackupFileName
FROM @BackupFileEntry AS BF
ORDER BY BF.Sequence;
OPEN curFiles;
FETCH curFiles INTO @BackupFileName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* Format the xp_cmdshell statement */
SET @SQLCommand = LTRIM(RTRIM(REPLACE(@SQLCommandTemplate, CHAR(13) + CHAR(10), '')));
SET @SQLCommand = REPLACE(@SQLCommand, '%filename%', @BackupFileName);
SET @SQLCommand = REPLACE(@SQLCommand, '%targetpath%', @RestoreFilePath);
/* Now execute this on the source server */
SET @SQLRemoteMove = LTRIM(RTRIM(REPLACE(@SQLRemoteMoveTemplate, CHAR(13) + CHAR(10), '')));
SET @SQLRemoteMove = REPLACE(@SQLRemoteMove, '%movesql%', @SQLCommand);
SET @SQLRemoteMove = REPLACE(@SQLRemoteMove, '%linkedserver%', @BackupServerName);
EXECUTE @ReturnCode = sp_executesql @stmt=@SQLRemoteMove
IF (@ReturnCode != 0)
BEGIN
RAISERROR('Failed to run backup command on remote server ''%s'' (return code: ''%u'').', 15, 1, @RestoreServerName, @ReturnCode)
END
FETCH curFiles INTO @BackupFileName
END;
CLOSE curFiles;
DEALLOCATE curFiles;
RETURN 0;
END TRY
BEGIN CATCH
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
SET @StackTrace =
'Msg ' + CAST(ERROR_NUMBER() AS VARCHAR)
+ ', Level ' + CAST(ERROR_SEVERITY() AS VARCHAR)
+ ', State ' + CAST(ERROR_STATE() AS VARCHAR)
+ ', Procedure ' + ERROR_PROCEDURE() + ' (caller: ' + @ProcedureName+ ')'
+ ', Line ' + CAST(ERROR_LINE() AS VARCHAR)
+ '.' + CHAR(13) + CHAR(10) + ERROR_MESSAGE();
RAISERROR(@StackTrace, @ErrorSeverity, @ErrorState);
END CATCH
GO
March 17, 2010 at 12:52 pm
Write a vbScript or power shell script to create a FTP script file and invoke http://FTP.exe on the file. You will need to set up the FTP site in IIS on the remote server. I used vbScript. Here is part of my script:
[font="Courier New"]
sub ftpUploadFile(strFilename)
Dim oFS
Dim oFile
Dim oShell
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
strFtpFolder = "C:\MSSQL\Backup\FTP\"
strFtpFile = strFtpFolder & "FTP.txt"
strFtpLog = strFtpFolder & "FTP.log"
' Open the new empty FTP script for writing
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oFile = oFS.OpenTextFile (strFtpFile, ForWriting, True)
' write job specific commands to the FTP script
oFile.WriteLine "open {server name}"
oFile.WriteLine "{ftp user}"
oFile.WriteLine "{fpt user password}"
oFile.WriteLine "binary"
oFile.WriteLine "lcd " & strFtpFolder
oFile.WriteLine "cd {ftp virtual directory}"
oFile.WriteLine "put " & strFilename
oFile.Write "quit"
oFile.Close
Set oFile = Nothing
Set oFS = Nothing
' Execute the FTP script and write the results to the log file
Set oShell = CreateObject("WScript.Shell")
oShell.Run "%comspec% /c http://ftp.exe -d -i -s:" & strFtpFile & " >" & strFtpLog, 0, True
Set oShell = nothing
end sub
[/font]
March 17, 2010 at 10:41 pm
Thanks All, All your replys were very helpful to me,
March 19, 2010 at 3:26 am
have you looked into the log shipping executables used by SQL to transfer log files when implementing logshipping?
That would be a really good way of copying as you could probably track progress and errors using the log tables it populates.
sqllogship.exe
March 19, 2010 at 11:37 am
How about simply backing up directly to a UNC file share on the other server?
June 27, 2012 at 2:14 pm
Hi ,
I am using XCOPY Command to move files from one server to another which is run under SQL Agent in midnight taking less time compare to put backup files directly on network location.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply