What are the challenges you face when working across database platforms? Take the survey
Options

Moving processed files

eaglesofteaglesoft Posts: 8
edited September 6, 2007 5:25AM in SQL Backup Previous Versions
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

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Could you please post the T-SQL command that's ran by the job step that performs the restore on the standby server? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.