Moving processed files
eaglesoft
Posts: 8
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
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8