December 15, 2016 at 5:25 pm
I have a SQL database that must be always on 'standby' mode because I must restore transaction logs from a vendor database to my local database on stand by restoring option.
It has been around 3 months since I started to download log backups from vendor's sFTP server and restore everyday to my database (on standby mode). Today, in the middle of restoring, SSMS crashed and the application closed immediately then left the database on restoring mode.
I don't want to work all over again from the beginning . Please your help if anyone knows how I can change the database to Standby mode and keep the sequence of LSNs; from the last LSN restored to next LSN of log backup file.
Thanks!
Lilly
December 15, 2016 at 9:42 pm
This was removed by the editor as SPAM
December 16, 2016 at 1:34 am
You don't need to start from the beginning to put the database in stand-by mode again. You can just restore the file that was being restored when your SSMS stopped. To be on the safe side you can also start with one or two LOG files before the one that was being restored. The SQL restore command will inform you if the file is actually being restored or not (too early to apply).
If the restore command fails it will also mention a LSN number. You can query the backup tables in the [msdb] of the source database to get the precise LOG file that you need to restore from this LSN onwards.
December 16, 2016 at 1:52 am
As mentioned, you should be able to restart the restore.
The database can't be moved to norecovery or standby if recovery has been run.
December 16, 2016 at 9:45 am
elulu2000 (12/15/2016)
I have a SQL database that must be always on 'standby' mode because I must restore transaction logs from a vendor database to my local database on stand by restoring option.It has been around 3 months since I started to download log backups from vendor's sFTP server and restore everyday to my database (on standby mode). Today, in the middle of restoring, SSMS crashed and the application closed immediately then left the database on restoring mode.
I don't want to work all over again from the beginning . Please your help if anyone knows how I can change the database to Standby mode and keep the sequence of LSNs; from the last LSN restored to next LSN of log backup file.
Thanks!
Lilly
use the following command to switch from restoring to standby
RESTORE DATABASE [database_name]
WITH STANDBY = 'standby_file_name_and_path' ]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 16, 2016 at 10:20 am
Thank you, everyone for your prompt reply!!!
It works now all fine! I able to identify the last log file restored from the database properties (easy way even, ya?!) and continued to restore on standby mode from that last log file on, then run perfect! 🙂
October 18, 2018 at 7:49 pm
elulu - Thursday, December 15, 2016 5:25 PMI have a SQL database that must be always on 'standby' mode because I must restore transaction logs from a vendor database to my local database on stand by restoring option. It has been around 3 months since I started to download log backups from vendor's sFTP server and restore everyday to my database (on standby mode). Today, in the middle of restoring, SSMS crashed and the application closed immediately then left the database on restoring mode.I don't want to work all over again from the beginning . Please your help if anyone knows how I can change the database to Standby mode and keep the sequence of LSNs; from the last LSN restored to next LSN of log backup file.Thanks!Lilly
I have very similar situation with your case, and trying to look for a solution. Could you please instruct me how to switch the database to STANDBY mode?
October 19, 2018 at 4:38 am
you cannot switch to standby. You can only restore to standby mode.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply