September 14, 2006 at 6:02 am
I implemented an log-shipping mechanism between two SQL Server 2005 servers.
I face this problem:
The backup to the primary and the copy process from the secondary have no problem
But during the restoration process I get this error
The restore operation cannot proceed because the secondary database 'DBSecondary' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping)
Could you please tell me where is the problem?
I tried to configure the secondary server (through the primary SSMS) in order to use NoRecovery or StandBy
Why I get this message?
Thanks for your help
September 14, 2006 at 7:27 am
The secodnary database of a log shiping is continously on recovery mode. You cannot access it. So you need to set the db to Standby mode.
However, you can create db snapshot on the secondary database periodically and accee the snapshot.
September 14, 2006 at 9:06 am
Dear peterhe,
first of all thank you for your answer but the problem in my case is that the restore job fails giving this message The restore operation cannot proceed because the secondary database 'SecondaryDB' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping)
The backup job on the primary and the copy job on the secondary finish successfully but
There is no recovery of the T-Log because of the fail of recovery job. But why?
Thanks
September 14, 2006 at 2:57 pm
The problem is not the job failed. It's your database in a state that cannot apply the transaction log. The error message is very clear.
You can run
SELECT * FROM sys.databases
and check the state , and is_standby column.
For a second Db of log shiping, the vlaue SHOULD be either 0,1 (online & stand by) or 1,0(restoring, not in standy). Your second db probably has different value as stated by the error message
Do you setup the logshipping manually or by the wizard? If you setup manully, when you restore the database, you need to have WITH NORECOVERY or STANDBY='undo filename', e.g.:
RESTORE DATABASE YourDb
FROM DISK = 'YourBackupFile.bak'
WITH NORECOVERY
If you don't want to re-setup your logshipping, you can try to find out the first copied log file, manually restore the log with NORECOVERY, and then try the job again. I am not sure this can solve your problem or not.
September 15, 2006 at 12:12 am
Dear peterhe,
thanks for your reply.
Now, I restored the secondary db manually with NORECOVERY option and then I started the log shipping process. My secondary db was always in restore mode ( i couldnt see its content as you noticed above).
During this phase, every t-log that is restored to the database , is commited or not?
How could I check if the t-log restored successfully?
I had the following problem: the restore job reported that the job succedded. After many succesful executions of this job,I wanted the secondary db to be usable. So, i executed RESTORE DATABASE secondaryDB with RECOVERY and I realized that the contents of the database was the same as the contents after restoring the initial backup! No t-log had been restored! What goes wrong?
If my primary db failed (due to hard disk drive failure ) and I had no availiable t-logs to restore, what is the best way to make secondary db usable without losing the t-logs applied?
Thanks
September 15, 2006 at 2:02 am
Peterhe,
I checked
SELECT * FROM sys.databases
and check the state , and is_standby column
and initially (after the creation of the secondary database from the primary's restore -I always use the wizard-) my secondary db in the SSManagement Studio appers gray and I can query it. After two failures in the restore process , the database stops to be in this mode and appears yellow but it is not accessible. After this, follows a succesful restore but the database remains in that mode (state =3, is_standby=0) and i can access it (message: It is in the middle of a restore.) Why my database changes state and remains in a state other than stand by?
Thanks
September 15, 2006 at 6:46 am
The second DB of a log shipping either in standby mode or recovering mode.
Stand by: you can use the database and it's read only. The state of the DB will show as ONLINE
Recovering mode: i.e. NORECOVERY when restore the t-log, The database cannot be accessed.
You need to decided which way you want to setup in the begining.
September 18, 2006 at 6:53 am
peterhe,
I tried everything you advised , thank you very much.
The restore job fails (the backup and copy jobs completed successfully) giving the following error
Error: Could not apply log backup file '\\secServer\SharedFolderSecondary\PrimaryDB_20060915071600.trn' to secondary database 'SecondaryDB'.(Microsoft.SqlServer.Management.LogShipping) ***<nl/>*** Error: During startup of warm standby database 'SecondaryDB' (database ID 11)its standby file ('\\secServer\SharedFolderSecondary\SecondaryDB_20060915071828.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(error not found)'. Diagnose the operating system error<c/> correct the problem<c/> and retry startup.<nl/>RESTORE LOG is terminating abnormally
Have you any idea about the reason of this failure?
Thanks
September 18, 2006 at 10:01 am
The running account of SQL server service could not access the stand by file SecondaryDB_20060915071828.tuf in the folder \\secServer\SharedFolderSecondary\. When the second db is in stand by mode, this file is required to save the uncommited transactions rolledback in the second server.
You need to grant the access privilege of the folder to the account that sqk server service runs in windows. If you are using local system, try to use local folder instead of a shared folder (not sure about this).
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply