How can we help you today? How can we help you today?

Script to restore from multiple unknown named logs

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"'




nbullock
0

Comments

3 comments

Sort by
  • Dan B
    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"'
    Dan B
    0
  • nbullock
    Thank you very much,  I ended up figuring that out but now I am trying to figure out how to restore only logs that have not yet been done.
    nbullock
    0
  • Alex B
    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
    Alex B
    0

Add comment

Please sign in to leave a comment.