August 20, 2008 at 2:48 am
Hi,
I am trying to setup Log Shipping on my local machine with a Named instance and a Default instance of Sql Server 2005. However, the backup database on secondary server shows status - (Restoring...). How to stop the Restore so as to verify that any change to primary is reflected in secondary server.
Even after disabling all the Shipping Jobs on both servers I am not able to remove the restoring status.
Also, how to find out if the Backup is being done in StandBy or No Recovery mode?
Thanks in Advance.
August 20, 2008 at 2:52 am
Basically you have chosen the option during the Loggshipping setup to restore the database in No Recovery Mode that is why it is showing you the status of Restoring... Look at the properties of the log shipping on your primary datatabase and set the status to Stand By mode.
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
August 20, 2008 at 4:05 am
Exactly, I wonder why the No Recovery option is provided.. :satisfied:
Thanks.
August 20, 2008 at 4:44 am
When you restore the transaction logs to the secondary database, you can specify that the database be in one of two modes: offline or read-only (step 7 in this document). It appears that your secondary database is in offline mode, as its status is 'Restoring'. If you want to bring it to read-only mode temporarily, you could restore the last transaction log again using the STANDBY option e.g.
RESTORE LOG AdventureWorks FROM DISK = ... WITH STANDBY = 'G:\UndoLogs\AdventureWorks.dat'
and do whatever checks you need. Remember to switch your database context out from that database once you're done, otherwise you'll hold up the log shipping process.
Or if you want to put the secondary database in read-only mode permanently, you could modify the log shipping properties of the secondary database. Using SSMS, select the primary database on the primary server, and view its properties. You should see a 'Transaction Log Shipping' item on the left. Select that item, and view the properties of the secondary databases. On the 'Restore Transaction Log' tab, you can change the secondary database state.
Also, how to find out if the Backup is being done in StandBy or No Recovery mode?
Those options are applicable to the restore task, not the backup task.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 20, 2008 at 9:07 am
varunsamvedi (8/20/2008)
Exactly, I wonder why the No Recovery option is provided.. :satisfied:Thanks.
so the restore job can successfully restore transaction logs to the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 21, 2008 at 2:15 am
My Understanding is that StandBy is useful only if a read-only access is required to the Database while Log Shipping process is on.
And NoRecovery does the restore in one shot in case of -
[p] - Failover (which again depends on whether primary server is available or not) [/p]
[p] - bringing it to read-only mode temporarily
If you want to bring it to read-only mode temporarily, you could restore the last transaction log again using the STANDBY option e.g.
RESTORE LOG AdventureWorks FROM DISK = ... WITH STANDBY = 'G:\UndoLogs\AdventureWorks.dat'
and do whatever checks you need. Remember to switch your database context out from that database once you're done, otherwise you'll hold up the log shipping process
(dunno how to bring out of that context 🙂)
[/p]
Please correct me and post the associated T-SQL
Thanks a Lot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply