Solved

SQLserver , log shipping

Posted on 2014-09-08
16
421 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
  • 8
  • 7
16 Comments
 
LVL 45

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
 
LVL 45

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 45

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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 45

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 45

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 42

Accepted Solution

by:
EugeneZ earned 500 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 45

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now