August 7, 2008 at 6:48 am
Dear all,
I am trying to set up log-shipping between two servers running SQL Server 2000 but am not sure that I’m doing it correctly and have run into a few problems.
Firstly, the database I am trying to log-ship is over 15GB in size and due to the geographical location of the log-shipping site and the speed of our connection to it, it takes approximately 2 days to copy this amount of data to it. For this reason, I created a full backup manually and copied the .BAK file to the remote location myself (before the log-shipping wizard had been involved).
While the .BAK file was copying, I set up a maintenance plan to run a transaction log backup every 10 minutes against the source database. I chose to do this as I knew that if I didn’t, the first transaction log backup created by the log shipping job would contain 2 days worth of transactions which would take too long to copy to the target site and put the databases out of sync with each other.
Once the .BAK file and 2 or 3 days worth of transaction log backups had arrived on the target server, I restored the database along with all the transaction logs and this put the database within an hour or so of the source database. However, restoring the database wasn’t as simple as I had been expecting it to be as I couldn’t restore a transaction log backup after the full backup unless the full backup was restored using the “WITH NORECOVERY” option. And I couldn’t restore subsequent transaction log backups unless the “WITH STANDBY” option was specified for all of the previous transaction log backups to be restored. I have a feeling that this is where I went wrong but I’m not sure what I should have done.
However, when it came to setting up the log-shipping jobs, I pointed it to the database I’d manually restored at the remote site and everything seemed to work very smoothly. This was about a month ago and I’ve checked on it every day since then to make sure that the logs are finding their way to the remote server and being restored successfully and everything seems to be great.
Now though, we’re planning on testing our failover to the remote site. I am perfectly comfortable with the fact that the remote database is in Read-Only mode as I understand that it would have to be in order for the log-shipping to work. Switching the database out of Read-Only mode doesn’t seem to be quite as simple though and the only way I’ve been able to do this so far is to re-run the “RESTORE LOG” statement for the last transaction log backup that was applied but remove the “WITH STANDBY” line. Whilst I’m happy that this works, it doesn’t seem to be a particularly elegant solution and my limited experience of SQL Server leads me to believe that I must be doing something wrong or unusual to end up in this situation.
Can anybody (assuming anyone has read such a vast amount of text – sorry!) suggest what I may be doing wrong?
Many thanks in advance,
Dan
August 7, 2008 at 7:16 am
Apply the latest transaction log and then run the following command to take it out of read only mode:
RESTORE DATABASE dbname WITH RECOVERY
Keep in mind though, in order to revert back to warm standby status you will have to take a new backup on the primary server, transfer and restore it with NO RECOVERY in order to start log shipping again.
Also, Some other things you will probably have to do outside of just restoring the database is make sure to fix server logins (to avoid orphaned users), DTS packages, Jobs, SQL Mail, Maintenance Plans, etc. as these are not handled by log shipping.
Good luck!
August 7, 2008 at 8:45 am
Thanks Brian.
I guess this is good news if it means that I didn’t go far wrong with my log-shipping setup although I’m rather disappointed by the fact that once a warm-standby server becomes live there’s no way of making it a standby again without creating it from a fresh backup.
Thanks again for the tips!
Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply