SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to make log-shipped database to be available for queries


How to make log-shipped database to be available for queries

Author
Message
dakshinamurthy-655138
dakshinamurthy-655138
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 1056
Hi,

How to make log-shipped database to be available for queries.

I have MyDB in one Server which is used for Log-shipping and in the other Server MyDB is getting Logshipped. Now how do i cross check the DML Operations done in MyServer for MYDB is also been made in the other Server which is getting log-shipped.

In the sence can i execute a Select Statement to the logshipped DB in the other Server, even still my DB in My Server is active.


With Regards
Dakshin
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3721 Visits: 6512
When you create the log shipping you have 2 choices. You can configure restore log operation to be done with norecovery or with standby option. If you use the norecovery option, you can not issue select statements on it. If instead of norecovery you use the standby option, you can run select queries on the database. Notice that in that case you also have to make the decision what to do when it is time to restore the log, but there are users that are using the database. You’ll have to wait with the restore or to kill the users’ processes before issuing the restore log statement. All of those options can be configured with the wizard.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
dakshinamurthy-655138
dakshinamurthy-655138
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 1056
Thanks for the information, i will try the same and will get back to you if i have any queries. I Wish You A Very Happy New Year 2009


With Regards
Dakshin
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 3650
This appears to be a duplicate post. Also refer to post - http://www.sqlservercentral.com/Forums/Topic616876-146-1.aspx

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20332 Visits: 17244
Daksin

as Adi pointed out you cannot query the database in the norecovery state. Bear in mind with the standy option when log file restores occur users will be kicked out without warning by the restore process. If you're needing to query the database that much then Log shipping probably isn't your best option.

If you're asking how can you bring the database back online when in the norecovery state then simply issue

RESTORE DATABASE MYDATA WITH RECOVERY

Bear in mind that the log shipping config will be broken

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3721 Visits: 6512
Perry Whittle (12/31/2008)
Daksin

as Adi pointed out you cannot query the database in the norecovery state. Bear in mind with the standy option when log file restores occur users will be kicked out without warning by the restore process. If you're needing to query the database that much then Log shipping probably isn't your best option.

If you're asking how can you bring the database back online when in the norecovery state then simply issue

RESTORE DATABASE MYDATA WITH RECOVERY

Bear in mind that the log shipping config will be broken


Acutely when you configure the log shipping with standby option, you can also select between 2 choices – kill all processes in the secondary database and perform log restore or don’t perform log restore if the database is being used. Of course if you select the second option, the restore operation might never run if someone opens a connection to the database and doesn’t close it, so it is better to use the first option.

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Paresh Prajapati
Paresh Prajapati
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 464
dakshinamurthy (12/31/2008)
Hi,

How to make log-shipped database to be available for queries.

I have MyDB in one Server which is used for Log-shipping and in the other Server MyDB is getting Logshipped. Now how do i cross check the DML Operations done in MyServer for MYDB is also been made in the other Server which is getting log-shipped.

In the sence can i execute a Select Statement to the logshipped DB in the other Server, even still my DB in My Server is active.


With Regards
Dakshin


When u sertup log shipping , then make secondary database on stand by mode
or
restore database dbname with recovery

_____________________________________________________________________________________________________________
Paresh Prajapati
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
dakshinamurthy-655138
dakshinamurthy-655138
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 1056
Thanks a Lot for the timely needed information.

With Regards
Dakshin
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search