Script to restore from multiple unknown named logs
nbullock
Posts: 4 New member
in SQL Backup
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.
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 B
Posts: 287 Gold 5
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 B
Posts: 1,157 Diamond 4
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
Answers