December 18, 2002 at 9:17 am
Can you restore a transaction log backup while someone is connected to the db? I know with a full database restore you need exclusive access but how about during a log restore?
December 18, 2002 at 9:32 am
You cannot restore a transaction log backup to an in progress DB. You also have to restore like so. Last Full backup first with norecovery, then each Tl backup in order with norecovery on all except last. So because of the Full backup requirement you fall into exclusive access needed.
December 18, 2002 at 9:44 am
So if you had a reporting server for which you wanted to keep the data refreshed as frequently as possible, how would be the recommended way of doing it? I was thinking log shipping but users will hitting the db so, from what you said, I can not restore it.
December 18, 2002 at 9:53 am
Sorry, that is a different animal and I haven't had real time to experiment with it. I do believe you are on the right track thou, but I am not able to answer this in full.
December 26, 2002 at 9:20 am
Log shipping isnt a good fit unless you're going to really keep the latency high, only do the restore a couple times per day or at some defined period where you force everyout out without holding up business.
Andy
January 2, 2003 at 5:17 am
Have you considered a database in Standby mode ? This allows read-only access. Read "Using Standby Servers" in BOL.
January 3, 2003 at 6:25 am
quote:
Have you considered a database in Standby mode ? This allows read-only access. Read "Using Standby Servers" in BOL.
We tested log shipping here, but on a Production server with more than 40 databases (~75 GB in total occupied disk space) it just looked like a bear to monitor. If I recall correctly, log shipping generated a set of backup files for every database, and the process involved:
- Backing up the databases to disk on the source server
- Copying those backups to the destination server
- Then restoring the backups
That extra copy of the backup files just seemed overly redundant to me. Why not just backup the files to the destination server and restore the destination database from there? (I don't recall seeing any obvious way to change the copying behavior.)
We eventually started restoring in standby mode. That seems to work fine. We use the standby server for report generation during the day (to offload work from the real Production server), and we can roll it forward with the transaction logs if we need to do so.
Jon
January 8, 2003 at 3:22 pm
You can not restore log if DB is using. One way you can try if DB application is allowed is to write script to kill the process before running restore log.
For my case, I have 15 minutes log backup and ship to reporting server. There is race between restore and using DB. If DB is using when start restore, it will try next time and will restore all available logs eventually. I have a log of reports as well as web application looking at this box. If application hits DB when it is restoring, I have the page to handle retry it later.
I currently satisfy this way.
jiema
Jie Ma
January 9, 2003 at 6:59 am
I have used log shipping, transactional replication, and merge replication. To create several types of report servers and backup servers, with 30+ databases. Log shipping is the best for report servers since you can but the databases in standby
mode. And since you are probably backing up the logs every hour anyway, that is most of the maintenance headache.
January 9, 2003 at 7:04 am
I agree with PLanigan. Replication could be the best option.
By using replication, you won't have to worry about the production server being used. Your reporting server as a subscriber for the production server (publisher), will keep your most current data.
One thing though, you need to be careful on how to set your rreplication (turn it off and on as needed) to enable your subscribers to reflect any design changes (add new tables or add new columns to a table)
BOL can help you with that. Good luck!
January 9, 2003 at 7:55 am
You must have in mind when you use the tl backup and restore schema, the issue with the non-logged operations that affects the log backups (Error 4213)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply