Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQLserver , log shipping

Posted on 2014-09-08
16
Medium Priority
?
526 Views
Last Modified: 2014-09-09
Hello,
  I stop logshipping for one of my DB. The database on the secondary server still show 'restoring' . How do I bring this db up with recovery,
I tried RESTORE DATABASE mydb with recovery

But i get an error
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing on file 'mydb_log'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I don't have  the logs. How can I remove the Db and restart.

Thanks
0
Comment
Question by:SiemensSEN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40311994
Seems like you had an abrupt stop of a transaction log restore. I'm afraid that you can't do nothing without the last log backup.
What you did with the log backups from the logshipping?
0
 

Author Comment

by:SiemensSEN
ID: 40312000
Can I redo the log shipping?
0
 

Author Comment

by:SiemensSEN
ID: 40312012
I stop the log shipping on the primary
stop SQL server on the secondary and rename the LDF amd MDF file
Then restart SQL server and delete the DB

Now I try to redo the logshipping by following step;
Took the back from the last night
copy it to my secondary server and restore with standby
Create the log shipping with SSMS

Now I see the first log is Hugh same size as DB and if ever 15 min it keep generating the same size file. What am I doing wrong? The file are copied to the secondary server but  not applied
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40312015
You can give a try.
If the error continues then you need to recreate the log shipping that will start with a full backup of the original database.
0
 

Author Comment

by:SiemensSEN
ID: 40312023
I did . Please see my last comment
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40312028
You have to set a restore job in the secondary server to apply the transaction log backups.
0
 

Author Comment

by:SiemensSEN
ID: 40312038
There is a restore job. It is created by SSMS when I configure log shipping. Its not applying logs and logs is Hugh (should not be because it from last night)

Is there cleanup I need to do? It seem that SQL Server is having an issue because I drop the DB from the last log shipping and it is still looking for the old logs??
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40312071
Maybe. It's better you make a clean drop logshipping and the you recreate it. Be sure that you delete all jobs.
It's a SQL Server 2005 instance, right? Follow the MS document: http://technet.microsoft.com/en-us/library/ms189071(v=sql.90).aspx
0
 

Author Comment

by:SiemensSEN
ID: 40312102
Thanks , I did

I went SSMS,
Right the DB , delete the log shipping
SSMS deleted the LS_backup from the primary
SSMS delete the LS_copy and restore from the secondary

I redo the logshipping , same result. IS there something in the DB that is not clean up?.

I have created this many other DB. This is step
1: Back up DB
2: copy to secondary
3:  on the secondary ,, restore with standby
4:  On the primary, open SSMS and configure log Shipping

.... Do I need to back the transaction log and applied it to secondary before configuring log shipping?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40312124
I usually don't use the RESTORE WITH STANDBY but RESTORE WITH NORECOVERY mode.
0
 

Author Comment

by:SiemensSEN
ID: 40312368
Ok, I tried it both .

The issue the setup seem OK but the logs are not applied. It is always skip. The first log is 4 GB and all other logs are also 4 GB. Not sure why it some large?.

I don't see any error other than the alert which indicate that the logs are  not restore
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40312374
But those are transaction logs backups after the full backup that you ran during the log shipping creation?
0
 

Author Comment

by:SiemensSEN
ID: 40312388
Yes, but it is very large, The Db is 6 GB. Each logs that is generated is 4 GB. They are been transfer to  secondary server but not applied.

So, I am trying this option

1: Take a full backup and applied it to the secondary  DB ( norecovery)
2: take a transaction log backup of the primary db
4: applied it to the secondary DB ( I hope this possible)
5: redo the log shipping

Do you see any issue with the steps
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 40313332
please clarify your sql server  version\edition and service pack # (make sure it is latest)
how often you run trans log backup? try every 15 mins
and make sure your restore location is set with UNC path visible to second sql server  : e.g. \\server\share
0
 

Author Comment

by:SiemensSEN
ID: 40313360
I got it work

my steps
1:backup db
2:restore db with standby
3:backup the transaction logs
4:apply the transaction to secondary db
5: configure log shipping

The only new step is 3 and 4. So it's working now. I have to do some more research.

Thanks for your help
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40313788
It was I said:
But those are transaction logs backups after the full backup that you ran during the log shipping creation?
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question