April 27, 2006 at 5:42 pm
Greetings, I have just set up log shipping for one of my databases and it is functioning properly. I am using a maintenance plan to do the log shipping. My question now is what is the best way to back up the primary. I have seen some cautions regarding backing up the primary suggesting that if it is not done properly or if the timing is off the transaction log will be emptied and so the standby runs the risk of not getting all transactions and getting out of synch with the primary. The notes I read have also suggested one should not back up the database as part of the log shipping maintenance plan.
The second part of the question has to do with the transaction logs backed as part of the log shipping maintenance plan. I am leaving them on the primary server for 2 days. If I do a daily backup of the primary can these log files be used to recover to a point in time if I have a backup of the database or will that cause some problem with the log shipping maintenance plan.
In this situation the issue is not disaster recovery but instead, the possible need to recover the database to a particular point in time should a user make a mistake and alter data or drop an object by mistake.
ANy comments or suggestions will be greatly appreciated. Thanks.
April 28, 2006 at 8:27 am
First I would go back to the reason. Replication is usually for DR, building an Ad-Hoc reporting server and keepin synched, or a multitude of other things. What you are talking about i a chang control issue.
1) Users should never have enough rights to drop any object on a production server, or for that fact run any DDL statement.
2) Users should never b able to direct access and chang the data (EM type access and preferably not usin Access to hit the tables by hand).
3) Users changes shoul be processed by Stored Procedure preferably to limit direct access to tables.
4) Save yourself a headache and build Audit tables which clear on a periodic basis.
Example
I have a table fo Properties which has the address and several other things. When first keyed they have TBD for the address bcause somtimes they are a spot of dirt. They also contain a chargeback account that is responsible for payments out of their budget wit regards to the site. Now I also have an audit table to capture previous data which for me is saved permanently but could be limited to a months data or a week depending on your conceptual need. There is a trigger on the primary table for updates and deletes. When a user make a chang or deletes the record, the original values are copied into the audit table. Should someone say "Oops I deleted property XYZ in error" or "I changed the chargeback account of the wrong loation and don't know the original value" I can simply restore the last value from audit in the case of the first comment or check when the chargeback accoun changed last in the second example to verify I don't undo any other changes that have occurred. Now I can keep the syste live without a restore to a point in time and I don't potntially loose any transactions that have occurred since the error was made.
I think for you issue you need to first consider what your goal is and what the most effective way there is to deal with it. As well, ask for suggestions if you are not sure.
April 28, 2006 at 11:27 am
Thanks for the response. I understand what you are saying. The problems encountered were due to an update job which runs daily, failing and I wanted to recover the database to a point in prior to the update job running. I should have left off dropping an object, you are correct about this. Nevertheless my question still stands. What is the recommended methodology for backing up the primary database when log shipping has been implemented. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply