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

Log Shipping - Cleaning up Restored Files

Scott HerbertScott Herbert Posts: 14
edited February 5, 2009 5:25PM in SQL Backup Previous Versions
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 = ''\\\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)
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)

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


  • Options
    peteypetey Posts: 2,358 New member
    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>.

    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Forgive me, I had inconsistently applied the syntax on different databases' restore jobs. Deleting this thread might be best for everyone involved! :oops:
Sign In or Register to comment.