 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Replication / July 2006LogShipping: Get a copy of the destination DB?
On SQL Server 2000, how do you (nicely) get a copy of the Read-Only destination database? The only way I've figured out is to stop SQL Server, copy the MDF & LDF, restart SQL Server, then attach those newly copied files. But isn't there a better way which will leave the original destination DB in a state that allows the continued application of logs? Thanks! I must be missing something here - your subject refers to Log Shipping, and this is the technology you need. The database and logs are restored in standby or no recovery mode to allow further logs to be applied. If you want this for a RO reporting database (standby option), beware that the connections on the subscriber will get dropped while the logs are applied. For an alternative RO database solution you might consider transactional replication (please see this for a comparison http://www.replicationanswers.com/Standby.asp). Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Hi Paul, Yep, I'm using Log Shipping. Yep, the destination DB is set in standby or no recovery mode. Yep, connections to that db will get booted as logs are applied. Yep to EVERYTHING about Log shipping... Now, how do I get a copy that standby DB leaving everything for Log Shipping in place and NOT have to restart the SQL Server? OK - I follow your issue now. You could use the "Copy SQL Server Objects Task" in DTS. Alternatively you could replace the solution with transactional replication and republishing, or transactional replication and 2 subscribers, or more simply have 2 destinations for log shipping. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com There are over 3000 user tables in the DB. I don't really want to DTS 'em all. I was hoping for some kind of trick with BACKUP that would go ahead and take the backup even though the db is still in recovery. Or some kind of trick to ATTACH without recovery, so I could detach, copy, then reattach. Guess not. But thanks for replying! Stu, in the "Copy SQL Server Objects Task" there is the granularity to select the individual objects to send over - it's just the default that takes the whole lot. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Oh, I want the entire DB alright, I just don't want to use DTS to get it. Lets say you want a copy of a 86 GB database that contains thousands of tables...Would you use DTS to make that copy? OK - how about the other solutions: "Alternatively you could replace the solution with transactional replication and republishing, or transactional replication and 2 subscribers, or more simply have 2 destinations for log shipping". Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com No, Log Shipping will stay as is. It's needed for all the normal reaons someone uses Log Shipping. The ability to easily & efficiently copy the destination db would add MORE value to it. Thanks Stu, and what about the option of setting up the other computer as an additional standby server for log shipping? Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com A second standby server isn't my goal. We've already got one, and there isn't much value in another. Where I see getting more value out of the standby db is by copying it into a usable form. For example, as you mentioned in your first post, it could be used as a reporting database that doesn't boot people off except when it is refreshed. Or the db could become something for use in development, testing, or training. And finally, it could be used as a backup for itself so that the entire log shipping sequence doesn't need to be restarted in the event of the loss of the standby. Sure, these things could happen by taking a fresh dump from the source database. But considering that the db is large, and that the log shipping is happening across the globe under limited bandwidth, and that the entire db is just sitting there waiting for use...the simple ability to copy it would be quite beneficial. The only thing I can suggest is transactional replication from the source database or database mirroring and snapshots (SQL 2005). Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Maybe I am missing something here, but when I create log shipping I copy a backup of the source (primary) database to the destination (standby) server, and then I restore it on the standby server. I then start shipping logs to the standby server and restore the logs there. Trying to do this using any other method won't work as the lsn of the transaction log can only be applied on a database which is part of the log shipping chain.  SignatureHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > On SQL Server 2000, how do you (nicely) get a copy of the Read-Only > destination database? [quoted text clipped - 6 lines] > > Thanks! Paul thought he was missing something also (did you read that chain of messages?) I guess I wasn't clear with my question. Let's try this: Log Shipping is running. Log Shipping is not having any problems at all. Log Shipping will continue to run. The standby db is just sitting there. The standby db is only one log shy of being completely up to date. Beyond being a standby db, it could provide more value if it could be copied. So the question is: How can you copy the stand-by db, thereby creating an actually usable database, leaving log shipping functional? Stu - sorry but I think we're going round in circles and it seems your question has been answered a few times here on this thread. If you want a solution based at all on backups and restores - whether it is log shipping or hand-rolled backup and restore, there will be some downtime and loss of connectivity for your users on the new server. If this is indeed acceptable, then why not have another standby for your log-shipping primary. If not then you'll need an entirely different solution such as transactional replication. You seem to be determined to have a solution based on using the partially restored standby database but this CAN'T be backed up directly and as you mention, DTSing the contents is not desirable as it is too large. If log-shipping from your primary to another secondary is not acceptable, you could base a solution on copying the logs from the standby1 to standby2 and restoring them there in standby mode - a handrolled log shipping. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com I agree that Hilary's response caused us to go in circles. If she wasn't going to review the thread to get caught up, it seemed worth my time to fill her in, in case she had something to add. Also, you're right that I'm determined to use the partially restored standby db. I was hoping someone on here would have a miracle/undocumented feature or something that would allow either of these two things: 1) BACKUP...just take it! Yeah, it's a warm standby, but who cares? SQL Server lets me bring the DB on line as is without applying any more logs. Why not allow me to back it up as is? 2) CREATE DATABASE...FOR ATTACH that doesn't recover the DB. Why force the removal of standby when attaching? Stu - I think all your potential options have been explored in this thread. Rgds, Paul Ibison PS for anyone looking at this thread Hilary is a man! I'm sorry, I missed the point about a clone of the destination db. My answer was geared towards making a clone the source db and then keeping it in sync. SignatureHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com >I agree that Hilary's response caused us to go in circles. If she wasn't > going to review the thread to get caught up, it seemed worth my time to [quoted text clipped - 14 lines] > the > removal of standby when attaching? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.