How to edit the "on completion" section of Log Shipping setup

GregBrown

Hi All,

I need to edit how log files are dealt with after they are replayed, but i cannot find anywhere to make the change. Editing the job, only gives the option to edit the destination on the target server before replay. Any help would be appreciated as the only real option i can see is re-seeding the whole DB. Re-seeding this DB takes about 14 hours.

Thanks in advance!

Eddie D

Hi GregBrown

Thank you for your forum post.

What are you looking to change?

For the restore job, on completion of the restore the only parameter that comes to mind is you can only modify the path for the MOVETO command so the successful restored backup files are moved out of the network share to a different folder.

Many Thanks
Eddie

GregBrown

Hi Eddie,

Once the MOVETO has completed and the log has been played back, the file is moved to a "completed" folder, where you can set how many days or files are kept. Upon edit, this option is not available.

Thx

Eddie D

Hi GregBrown

To delete files from the MOVETO folder, simply add the ERASEFILES and FILEOPTIONS keywords to your backup job.

The easiest way to do this is to edit the restore job via SSMS ->SQL Agent ->Jobs ->Right Click the job and select properties ->Job Step->Edit.

The example syntax below will keep the latest 10 files:

"RESTORE LOG [pubs] FROM DISK = ''C:\shipped_logs\pubs\LOG_20120229_151009.sqb'' WITH MOVETO = ''C:\processed_logs'', ERASEFILES = 10b, FILEOPTIONS = 1, NORECOVERY"

ERASEFILES = 10b, -- value of 10b keep the latest 10 files
FILEOPTIONS = 1 -- value of 1 indicates Delete backup files in the MOVETO folder if they are older than the number of days or hours specified in ERASEFILES.

Further reading is available here.

Many Thanks
Eddie