Solved

SQLserver , log shipping

Posted on 2014-09-08
16
482 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 50

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 50

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 50

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 50

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 50

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 50

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 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 50

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
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: …

734 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