February 15, 2007 at 11:20 am
Greetings,
I implemented log shipping. After implementing it I see that the standby database is in loading mode and an attempt to view the properties returns the message "Database cannon be opened, it is in the middle of a restore." I looked at the directory for the transaction logs and they are all there but there was no .tuf file. After some research I cam to the conclusion that the standby is on NORECOVERY mode rather than STANDBY mode. I looked at the log shipping maintenance plan on the primary and under log shipping I edited the destination and saw that the initialization state of the destination server was in fact set to norecovery rather than standby. I changed it to standby and applied the changes. Now there is a .tuf file on the standby server but the database state is now indicated to be loading/suspect according to the enterprise manager.
The question, how can I change the status of the standby from NORECOVERY mode to STANDBY mode and also insure that all changes are applied?
Thanks.
February 15, 2007 at 1:01 pm
I believe all you need to do is:
RESTORE LOG dbname WITH STANDBY = undofilename
from BOL:
STANDBY = undo_file_name
Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.
If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.
-SQLBill
February 15, 2007 at 1:23 pm
This is getting a bit more confusing. Watching the database in the enterprise manager it changes state between loading and read_only and when it is in read_only mode the .tuf file is present. That makes sense if I understand the role of the .tuf file.
So, in the sytax you listed is the undo_file_name the .tuf file? And also, if I do this will the DB then remain in standby mode, i.e. readonly until I make another change?
Thanks.
February 15, 2007 at 4:32 pm
I've never done this, so....
I'm guessing that the tuf stands for transaction undo file. Once a database is in standby it stays that way until:
1. you restore it with recovery
2. you restore additional backups.
So, since you are seeing it switch back and forth, are you restoring additional backups? Are you replicating/log shipping to that database? If so, it would go into loading with every replication or log shipping.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply