Script to restore from multiple unknown named logs

nbullocknbullock Posts: 4 New member
We have log files provided via FTP and they need to be restored to a database several times per day.  I have seen that this can be done via script like this:

EXECUTE master..sqlbackup '-SQL "RESTORE LOG [CHR] 
FROM DISK = ''G:\DownLoads\Import\*.sqb'' WITH PASSWORD...

But I cannot seem to get the syntax right.  Running the code below works fine but I will not know the count of or name of the files when the script runs, any suggestions will be appreciated.

EXECUTE master..sqlbackup '-SQL "RESTORE LOG [CHR] FROM DISK = ''G:\DownLoads\Import\*.SQB'' WITH PASSWORD = ''********'', NORECOVERY, DISCONNECT_EXISTING"'

EXECUTE master..sqlbackup '-SQL "RESTORE LOG [CHR] FROM DISK = ''G:\DownLoads\Import\LOG_us_CHH_multi_replica_2018080818.SQB'' WITH PASSWORD = ''********'', NORECOVERY, DISCONNECT_EXISTING"'

EXECUTE master..sqlbackup '-SQL "RESTORE LOG [CHR] FROM DISK = ''G:\DownLoads\Import\LOG_us_CHH_multi_replica_2018080819.SQB'' WITH PASSWORD = ''********'', NORECOVERY, DISCONNECT_EXISTING"'

EXECUTE master..sqlbackup '-SQL "RESTORE LOG [CHR] FROM DISK = ''G:\DownLoads\Import\LOG_us_CHH_multi_replica_2018080820.SQB'' WITH PASSWORD = ''********'', STANDBY = ''E:\SQLBackup\Undo_CHR.dat'', DISCONNECT_EXISTING"'




Tagged:

Best Answers

  • Dan BDan B Posts: 287 Gold 5
    Accepted Answer
    Hi nbullock,

    You should be able to use the below which will restore all of the logs you need. The only difference to your first script is RESTORE LOGS vs RESTORE LOG. Let me know if you need any further assistance.
    EXECUTE master..sqlbackup '-SQL "RESTORE LOGS [CHR] FROM DISK = ''G:\DownLoads\Import\*.SQB'' WITH PASSWORD = ''********'', NORECOVERY, DISCONNECT_EXISTING"'

    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • Alex BAlex B Posts: 962 Diamond 3
    Accepted Answer
    Hi @nbullock,

    You will need to use the MOVETO argument to move the successfully restored files to a different folder (perhaps a subfolder) so that only the files that have not been restored remain in the folder location to be picked up by the RESTORE command.

    So the command could look like:
    EXECUTE master..sqlbackup '-SQL "RESTORE LOGS [CHR] FROM DISK = ''G:\DownLoads\Import\*.SQB'' WITH MOVETO = ''G:\DownLoads\Import\Success'', PASSWORD = ''********'', NORECOVERY, DISCONNECT_EXISTING"'

    Hope that helps!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?

Answers

Sign In or Register to comment.