Solved

SQLserver , log shipping

Posted on 2014-09-08
16
454 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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

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 48

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 48

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 48

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 48

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 48

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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