Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

SQLserver , log shipping

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
SiemensSEN
Asked:
SiemensSEN
  • 8
  • 7
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
SiemensSENAuthor Commented:
Can I redo the log shipping?
0
 
SiemensSENAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
SiemensSENAuthor Commented:
I did . Please see my last comment
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have to set a restore job in the secondary server to apply the transaction log backups.
0
 
SiemensSENAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
SiemensSENAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I usually don't use the RESTORE WITH STANDBY but RESTORE WITH NORECOVERY mode.
0
 
SiemensSENAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
But those are transaction logs backups after the full backup that you ran during the log shipping creation?
0
 
SiemensSENAuthor Commented:
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
 
Eugene ZCommented:
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
 
SiemensSENAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now