Log Shipping - Cleaning up Restored Files

Scott Herbert

I've done a fair bit of searching the forums etc., so forgive me if this has been answered elsewhere:

I'm running the below code to restore the t-logs:

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [DBMonitor] FROM DISK = ''\\192.168.120.183\d$\Backups\SQLCLUSTER\DBMonitor\LOG_DBMonitor_*.sqb'' WITH ERASEFILES = 1, FILEOPTIONS = 1, STANDBY = ''E:\RedGateSQLBackup\DBMonitor\UNDO_DBMonitor.dat'', MOVETO = ''E:\RedGateSQLBackup\DBMonitor\RestoredTLogs''  "', @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

The files in the directory specified in the MOVETO clause are piling up. What part of the ERASEFILES parameter am I getting wrong?

petey

The syntax looks correct i.e. you have ERASEFILES = 1 (erase files of the same database older than 1 day), and FILEOPTIONS = 1 (erase old files in the MOVETO folder).

Does the SQL Backup Agent service startup account have rights to delete files in the MOVETO folder (E:\RedGateSQLBackup\DBMonitor\RestoredTLogs)? Do the SQL Backup logs record any warnings? The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

Thanks.

Scott Herbert

Forgive me, I had inconsistently applied the syntax on different databases' restore jobs. Deleting this thread might be best for everyone involved! :oops: