How can we help you today? How can we help you today?

Moving processed files

We have set up a log shipping process which creates the initial log backup on the database server, moves the backup file to a network share (on the target server), restores the file, then moves the processed file to another folder on the target server.

Is it possible to make the final part of the process into a true "move" rather than a copy? Some of our logs can be large (e.g. 5GB) which can take several minutes to copy. As the processed folder is on the same drive as the share folder, the file could actually just be "moved" which would take virtually no time at all. (enhancement request?)

Thanks
Vince
eaglesoft
0

Comments

3 comments

Sort by
  • petey
    Could you please post the T-SQL command that's ran by the job step that performs the restore on the standby server? Thanks.
    petey
    0
  • eaglesoft
    Hi Peter

    Unfortunately, I don't have the original seed restore command. We are in the middle of a server migration and already running behind schedule so I can't go through the process again.

    The problem (I think) is in the way the shipped backups are moved to the processed folder. I'm assuming this is performed using an OS "copy" command rather than a "move".

    This is the log shipping restore command if that helps.

    DECLARE @errorCode INT
    DECLARE @sqlerrorCode INT
    EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [UKREPDIWPRD] FROM DISK = ''\\UKBHSR195\LOG_SHIPPING\UKREPDIWPRD\LOG_UKREPDIWPRD_*.sqb'' WITH ERASEFILES = 48h, NORECOVERY, MOVETO = ''G:\LOG_SHIPPING\UKREPDIWPRD\PROCESSED''"', @errorCode OUT, @sqlerrorCode OUT;
    IF (@errorCode >= 500) OR (@sqlerrorCode <> 0)
    BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorCode, @sqlerrorcode)
    END
    eaglesoft
    0
  • petey
    The log shipping command was what I wanted.

    If "\\UKBHSR195\LOG_SHIPPING\UKREPDIWPRD" maps to ''G:\LOG_SHIPPING\UKREPDIWPRD" on the standby server, then change "\UKBHSR195\LOG_SHIPPING\UKREPDIWPRD" to ''G:\LOG_SHIPPING\UKREPDIWPRD" as the source of the files e.g.

    EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [UKREPDIWPRD] FROM DISK = ''G:\LOG_SHIPPING\UKREPDIWPRD\LOG_UKREPDIWPRD_*.sqb'' WITH ERASEFILES = 48h, NORECOVERY, MOVETO = ''G:\LOG_SHIPPING\UKREPDIWPRD\PROCESSED''"', @errorCode OUT, @sqlerrorCode OUT;

    This should trigger the expected 'move' behaviour.
    petey
    0

Add comment

Please sign in to leave a comment.