September 10, 2014 at 7:29 am
Hello,
I am still trying to configure log shipping. I have managed to configure a job that runs without error. The only problem is the destination database has "(Restoring...)" next to its name and this never goes away.
The steps I am following are:
1. For the source database, right-click, properties.
2. Click "Transaction Log Shipping"
3. Click "Backup settings". (Enable this as a primary database in a log shipping configuration is already checked.)
4. Enter the network path. I am seeing many files there. This is on the destination server. It looks like this:
\\destinationserver\Logshipping
5. Click OK
6. In the "secondary databases" section I add a new server instance. This is the same server as the destination server in #4. I connect to it.
7. In "Initialize Secondary Database", I have experimented with both options. This time I check "Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if it doesn't exist.)"
8. In the "Copy Files" tab I put: c:\Logshipping
9. On the "Restore Transaction Log" tab I have experimented between "No recovery mode" and "Standby mode" without seeing any change.
10. When I am finished and click "OK", the job immediately runs. The Status message is "Success" for each line.
When I look at the destination database, it says "Restoring..." This never changes.
Does anyone know how I can actually have the database restored without this "restoring..." message? Thanks.
September 10, 2014 at 7:44 am
A log shipping secondary is supposed to be in the restoring state.
If it wasn't, then further log backups wouldn't able to be restored and the log shipping would be broken
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 9:06 am
You can restore with STANDBY and it's then readable when it's not restoring, but it's still really in the restoring state because more logs can be added.
September 10, 2014 at 11:34 am
Hi Steve, thanks for the tip. I changed it to "standby" and I now have a secondary database with all the tables I expect to see. Thanks!
GilaMonster, I also appreciate your reply. However, I don't fully understand what you're saying. (Why would I want a destination database that is inaccessible because it is always "restoring..."?)
September 10, 2014 at 11:37 am
Because if you recover the DB (bring it online), thereby removing the restoring status as you asked, you won't be able to restore more log backups and hence your log shipping secondary will be out of date.
Log shipping is for disaster recovery. When you have a passive standby for DR purposes, it's immaterial whether or not it can be accessed, it's there for disasters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 12:07 pm
I am trying to get the log shipping going so that we can copy a production database several times a day for the purpose of doing reporting on the secondary database. I didn't decide to approach the problem using log shipping. I have had success in the past creating a BAK file and restoring it. Unfortunately, I am not the DBA in this environment. I'm trying to help our DBA by getting Log Shipping to work in a test case.
I'm finding that Log Shipping is pretty strange. It only seems to work once I have configured it. (That is, after I click the final "OK" button the process runs and we have an exact replica on the destination server as the source server.)
To illustrate this point, after configuring log shipping and having it successfully run, I then edit a table on the source server. Then right-click the log shipping job. It runs successfully. I check the table on the destination server and it does not have the edit that I put into place. So, I don't know why I am not seeing the latest version of this table on the destination.
I have also tried to delete the destination database. Right-click and run the log shipping job. It says success. Check the destination server and do not see the destination database, even though I configured it to create the destination DB if it doesn't exist.
Then I right-click on the source database, go through the steps outlined at the top of this thread (but substituting "standby"), click "OK" and the job immediately runs successfully (i.e., it has the latest data on the destination box).
Does anyone have any suggestions about what I am doing wrong? Is Log Shipping a viable option for making copies of a database for reporting?
Thanks,
September 10, 2014 at 1:28 pm
cafescott (9/10/2014)
I am trying to get the log shipping going so that we can copy a production database several times a day for the purpose of doing reporting on the secondary database.
To be honest, wrong tool for that purpose.
To illustrate this point, after configuring log shipping and having it successfully run, I then edit a table on the source server. Then right-click the log shipping job. It runs successfully. I check the table on the destination server and it does not have the edit that I put into place. So, I don't know why I am not seeing the latest version of this table on the destination.
Because there's a delay. The log backup has to be taken on the source server, copied to the secondary server, then a job on the secondary has to restore that log (and by default it will wait until no one is using the secondary to do so). Only once the log backup has been restored on the secondary will the changes be visible.
I have also tried to delete the destination database. Right-click and run the log shipping job. It says success. Check the destination server and do not see the destination database, even though I configured it to create the destination DB if it doesn't exist.
The log shipping job which you run takes a log backup of the primary and copies it to the secondary. That's all. That will succeed even if the destination DB is not there.
The 'create if it doesn't exist' only refers to when you are setting up the log shipping.
Then I right-click on the source database, go through the steps outlined at the top of this thread (but substituting "standby"), click "OK" and the job immediately runs successfully (i.e., it has the latest data on the destination box).
Because you're reconfiguring it from scratch. That involves taking a full backup of primary, copying that (a process that can take hours for large DBs), and restoring it.
The log shipping job, when it runs on schedule or manually, is not doing all that setup from scratch, for very good reason. All it's doing is taking a log backup of primary and copying that log backup.
Does anyone have any suggestions about what I am doing wrong?
You're not doing anything wrong. You're just expecting results which log shipping isn't designed to give. It is not primarily a way to create a copy of the DB for reporting. It's primarily a DR technology.
There's always going to be a delay between changes being made on the primary and them appearing on the secondary because a log backup has to be taken on the primary (according to the schedule you chose when configuring the log shipping), copied to the secondary and then restored. The restore requires that either no one is using the secondary or that you chose the option to disconnect all users before restoring when you configured the log shipping.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2014 at 2:12 pm
GilaMonster, thanks for the informative reply. I need to digest what you've written. Until I do, thanks!!
September 11, 2014 at 6:50 am
GilaMonster, thanks again for the information in your reply. I am going to look into another way to copy a production database to another server so that the latter can be used for reporting. In the past I have used BAK files. I made them during off-hours. This meant that after it was restored, the destination database could only reflect the source database at Close of Business.
Does anyone have a suggestion about copying a production database during business hours? The solution has to be within SSMS. (In other words, no Baremetal backup.) Thanks,
September 11, 2014 at 6:52 am
Transactional replication?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2014 at 7:01 am
Based on the opinions expressed here:
http://stackoverflow.com/questions/2043726/best-way-to-copy-a-database-sql-server-2008
The BAK file and restore method seems to be the preferred way to copy a production database during business hours.
September 11, 2014 at 7:06 am
Backup/restore works fine when you need to refresh a dev instance once every few days (which is what that question asks). It'll work for once a day, providing the DB isn't too large (several hundred GB the copy and restore time starts to run into the many hours)
If you're wanting to just refresh a dev DB once a week, sure, backup/restore is by far the easiest.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2014 at 7:49 am
GilaMonster, thanks again for your insight. Based on your earlier comment I'm going to research Transactional Replication as it seems the best way to copy a production DB two or three times a day during work hours. Thanks!
September 11, 2014 at 7:52 am
GilaMonster (9/11/2014)
Transactional replication?
Agree 100%. I've used this for shifting reporting off live before to great effect. One of the benefits is, if your reporting is focused on particular tables only, you can pull only those tables over.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 11, 2014 at 7:54 am
cafescott (9/11/2014)
Based on your earlier comment I'm going to research Transactional Replication as it seems the best way to copy a production DB two or three times a day during work hours.
That's not what transactional replication does. Replication is *continuous*
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply