Hi Ive a quick question and I hope that someone may help. Im hoping to implement log shipping using scripts (sql server 2000 standard edition) my question arises with regard to how log shipping is implemented. Does it involve a one time backup of the production database and a restore to the standby server ONCE, and subsequently backups of the transaction logs are restored against the standby server.? does it require any further restore of a backup of the productin database..? If we also backup the production database, and tlogs outside of the log shipping i.e. theyre not being used with log shipping , just part of the backup strategy, will this affect log shipping, or is just a matter of keeping these backups of db and tlogs separately...
Any help to clear this up greatly appreciated..
Well, log shipping is a very useful tool. In a nut shell, to set it up, you take a full backup of your database (making sure it is in full recovery mode). Then you take that backup and restore it INTO READ-ONLY MODE on the target server. (Use the "with standby" statement if using query analyzer) You should not ever need to restore another full backup of your database for log shipping to work. (Unless something goes wrong of course) Then, as transactions hit your main database, you will dump your transaction logs that then get shipped over to the target server and applied. The thing to remember is transaction logs in SQL Server must be sequential. In the header of the .trn file, there is an LSN number. SQL knows the order of these. If you are missing an LSN or try to apply an LSN out of order, it will fail.
In short, you can take as many full backups of your database as you want without affecting log shipping, but every transaction log that is dumped will have to be shipped and applied to the target server. If not, your LSNs will get out of sync and both your log shipping and disaster recovery plan will fail.