Copy Files from one server to another

  • Hi Guys,

    I wanted to know if there is any script to copy backup files from one server to another server,

    thanks in advance,

  • 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

  • Adam,

    Could you please share your SSIS package as I am also in similar situation?

    Thanks in advance.

    MJ

  • There are a number of scripts on this site to help with that. Here's one

    http://www.sqlservercentral.com/scripts/Log+Shipping/67173/

  • Steve Jones - Editor (3/2/2010)


    There are a number of scripts on this site to help with that. Here's one

    http://www.sqlservercentral.com/scripts/Log+Shipping/67173/

    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

  • If I understand you correctly use the windows XCOPY routine,

    xcopy G:\backup\*.* \\otherserver\E$\backup2\ /d /c /y

  • 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

  • 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 🙂

  • 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

  • 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]

  • Thanks All, All your replys were very helpful to me,

  • 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

  • How about simply backing up directly to a UNC file share on the other server?

  • 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