Post reply

Scripting help needed

  • March 2, 2008 at 8:13 pm

    #187463

    Any help from all the SQL Guru's will be appreaciated.

    I am looking for a script or code that I can use to transfer transaction logfiles constantly every 15 mins from one server's folder to another remote server's folder. I can add this script or logic inside a sql task in ssis or inside an execute process task in ssis. Transaction log occurs every 15 mins on the source server and will need to be transfered to the destination server. The only option for this is throught FTP but I need the code for the process.

    Thanks

  • March 3, 2008 at 1:01 am

    #784312

    Just try following link:

    http://www.nyx.net/~bwunder/sql/logSync.htm

    Just post your feedback.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • March 3, 2008 at 8:32 am

    #784451

    Why not just have the log backup go directly to the destination server?

    Within the backup task and the create backup file -> folder value could be

    \\

    Firewalls may need to be opened up if they are in place in your environment, and the account doing the backup has the rights to both systems.

    This is how we have all our backups going. Additionally we have a symantec backup routine that then backs up the folder contents to tape. By that happening nightly the size if the folder can be controlled to a smaller amount of backups on disk.

  • March 4, 2008 at 7:14 am

    #784926

    can u try thired party tools, i have saw some can do this.

    ..>>..

    MobashA

  • March 4, 2008 at 7:32 am

    #784936

    sorry but I see in my reply that the mapping declaration is missing

    \\ipaddress or dns name\shared folder

    is what should have shown up

  • March 4, 2008 at 9:59 am

    #785041

    mobasha (3/4/2008)

    can u try thired party tools, i have saw some can do this.

    That's nice... what are they?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 4, 2008 at 12:22 pm

    #785140

    I had moved files using xcopy and robocopy on different server on same network in past. But, never tried if they work on ftp server or not.

    A question for all,

    In sql server 2005 when we can take remote backup directly on any server on same network, but does this work on ftp server?

    SQL DBA.

  • March 4, 2008 at 12:27 pm

    #785143

    I'm pretty sure that backups can only happen to drives that are directly connected and/or can be mapped to. FTP is not an option.

    You would want to back up locally and then PUT the file somewhere remote using an FTP client at that point.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 4, 2008 at 11:44 pm

    #785317

    u can try SQL Server Backup v7.1.6

    or http://www.redgate.com SQL backup

    ..>>..

    MobashA

  • March 5, 2008 at 5:27 am

    #785410

    Heh Grasshopper try this out .... :w00t:

    let me know what you think

    CREATE PROCEDURE dbo.spSetCopyFiles

    @FilePath nvarchar(1000), -- eg: N'c:\logs\*.ldf'

    @DestinationPath nvarchar(1000) -- eg: N'\etwork folder\'

    AS

    BEGIN

    -- Developer:JL (South Africa) 05-March-2008

    -- Schedule:Create a SQL Job that executes this stored procedure every 15 minutes

    -- Notes:Make sure the SQL server has permission to @DestinationPath

    --User triggering sql job must belong to sysadmin

    --If you get Error creating Scripting.FileSystemObject ( Invalid class string)

    --Note its a problem with Windows XP upgraded with the latest service packs

    --Find key: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Scripting.FileSystemObject

    -- then add server\users group with read permissions

    -- Object variables

    DECLARE@FSObject integer,

    @HResult integer,

    @TSObject integer,

    @ReturnVal integer,

    @Result nvarchar(100),

    @ErrSource nvarchar(1000)

    -- Instantiate the file system object.

    EXEC @HResult = sp_OACREATE 'Scripting.FileSystemObject', @FSObject OUTPUT

    IF @HResult <> 0 -- There was an error

    BEGIN

    EXEC @HResult = sp_OAGetErrorInfo @FSObject, @ErrSource OUT, @Result OUT

    SET @Result = 'Error creating Scripting.FileSystemObject (' + @Result + ')'

    SELECT @Result

    RETURN

    END

    -- Copy the file(s)

    EXEC @HResult = sp_OAMethod @FSObject, 'CopyFile', NULL, @FilePath, @DestinationPath

    IF @HResult <> 0 -- There was an error

    BEGIN

    EXEC @HResult = sp_OAGetErrorInfo @FSObject, @ErrSource OUT, @Result OUT

    SET @Result = 'Copy log file call failed (' + @Result + ')'

    SELECT @Result

    RETURN

    END

    -- Destroy the FileSystemObject

    EXEC @HResult = sp_OADestroy @FSObject

    IF @HResult <> 0 -- There was an error

    BEGIN

    EXEC @HResult = sp_OAGetErrorInfo @TSObject, @ErrSource OUT, @Result OUT

    SET @Result = 'Destroy object call failed (' + @Result + ')'

    SELECT @Result

    RETURN

    END

    END

  • March 5, 2008 at 9:58 am

    #785609

    mobasha (3/4/2008)

    u can try SQL Server Backup v7.1.6

    or http://www.redgate.com SQL backup

    Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply