June 3, 2011 at 8:53 pm
Server Config: SQL 2008 EE, SP2, the latest MSFT updates installed, 64-bit, 32GB ram
I've set up log shipping on a DR server (This has been done locally before I ship it off to our alternate datacenter) While everything is up and running ok, I ran into issues putting the DB's into STANDBY/READ-ONLY mode with some of the larger databases.
I'd like the ability to have all the log shipped DB's on the secondary server in STAND-BY mode so data can still be read.
I restored a full backup of the DB in NORECOVERY mode and then went through the simple wizard choosing "No, The secondary database is initialized", then choosing "Standby mode" under Restore Transaction log but the DB remains in NORECOVERY mode....which is inaccessbile to queries. (again, log shipping is working perfectly).
What is the issue?
I've tried this so many ways and am getting frustrated! When I choose a smaller DB I can choose the "Yes, generate a full backup of the primary database..." option and it will work correctly every time.
Any thoughts (other than I'm nuts)?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 4, 2011 at 12:03 am
You must do the full restore using the STANDBY option of the restore command, and specify the STANDBY option on each log restore using the same STANDBY file for each. You should read about the RESTORE command in SQL Server 2008 Books Online.
RESTORE DATABASE MyDatabase
FROM
DISK = 'backup_path_and_file'
WITH
STANDBY = 'standby_path_and_file_name'
... other restore options...
June 4, 2011 at 7:43 am
Thanks for the post! To clarify: I know how to restore a DB in either state :), my muddled question was if anyone knew why it might not be wokring using the wizard options that I chose. I had checked "Standy" both times...and still no workie.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 4, 2011 at 10:04 am
You said you restored the full backup NORECOVERY. Try restoring it in STANDBY instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2011 at 1:23 pm
its perfectly reasonable to start with the full restore in norecovery and have the log restores in standby mode.
The log shipping jobs may be running and suceeding but are you sure any logs have actually been restored, you may have a delay set. Check the errorlogs and log shipping tables.
---------------------------------------------------------------------
June 4, 2011 at 3:29 pm
george sibbald (6/4/2011)
its perfectly reasonable to start with the full restore in norecovery and have the log restores in standby mode.
I'm just wondering if the wizard does something stupid if it's asked to restore the logs in Standby and the DB is recovering, not standby... Wouldn't be the first time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2011 at 3:43 pm
GilaMonster (6/4/2011)
george sibbald (6/4/2011)
its perfectly reasonable to start with the full restore in norecovery and have the log restores in standby mode.I'm just wondering if the wizard does something stupid if it's asked to restore the logs in Standby and the DB is recovering, not standby... Wouldn't be the first time.
the OP said they did the full database restore seperately (if I read it right) - but personally I never use the wizard for the full restore so who knows it could be fussy (I seem to remember it was hence why I don't use it)
---------------------------------------------------------------------
June 4, 2011 at 9:56 pm
It would all seem to be a matter of "timing". Yes I did set a delay (1 hour) but I guess I didn't wait enough...when I checked it again this morning all of the larger DB's were in STANDBY.
While the DB's started out in NORECOVERY, with the restoration of the tlogs, they went into the proper mode.
Thanks for all the info (and sorry to have wasted anyone's time!)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply