Problem with a log shipped database

Question

Hi,

I have a log shipped database.The problem is that I need the logs to be backed up,and use the log backups to restore the database to a particular time.

How is it possible to use them and not need to have another log backup maintenance plan?

Answer 1

What is the problem? You can use the logs that are backed up by the Log Shipping job to restore your DB as a new DB name on the same server or a different server as long as you have the corresponding full backup (and/or diff backup). See usage of STOPAT: https://msdn.microsoft.com/en-us/library/ms179451.aspx

---

Satish Kartan www.sqlfood.com

Answer 2

Here is what I do:

1.Restoring the full backup and leave the database is STANDBY mode

2.Restoring a log backup and here is the resulting ERROR:

Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 37000000020300001, which is too recent to apply to the database. An earlier log backup that includes LSN 34000000038800001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Answer 3

If so, why do you need a log shipping at all, Have a manually log file backups and write a script to restore the database at point of time.... Otherwise Satish has provided you with  some thoughts ....

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

I would like to tell you the whole story.

In our DBA department we have two different sections.Section one (us) is responsible to take care of the services,and Section two is responsible for taking backups.

A week ago my boss asked me to implement a HA solution to have an online mirror of some important DBs.

What would you do if you were on my shoes??

Yes.I suggested Log Shipping.

But after that I realized that the transactions are not taking affect on the secondary DB,And the problem was the transaction log backup plan deployed by our Section two. Because changing that plan is out of my hands,I have to solve the problem by myself.

The solution provided by Satish Kartan is perfectly fine. Now I just need to be able to restore Full and Log backups to a specific point in time.

Just help me to realize how to do it.

Answer 5

Are you sure that the transaction log backup that you are trying to restore is indeed the first transaction log backup after the full backup?

You cannot just take any transaction log backup and then restore, you have to restore all the transaction log backups in the order it was taken.

To see all backups run this query and check if you missed any transaction log backups

select * from msdb.dbo.backupset where database_name ='Name of database' order by backup_finish_date desc

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 6

But after that I realized that the transactions are not taking affect on the secondary DB,And the problem was the transaction log backup plan deployed by our Section two. Because changing that plan is out of my hands,I have to solve the problem by myself.

The reason why log restore failed is what you mentioned. Since other guys are manually taking Transaction log backup using MP(maint plan) this is breaking  Log chain. Because there are two Trn log backups happening one by Log shipping and other by MP. You have to ask them to stop MP you can say that since HA(log shipping) is configured they should not worry about taking additional log backup LS is already doing it for you

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

Answer 7

Please read this article 

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/06/15/an-overview-of-ha-dr-solutions-available-for-sql-server.aspx

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence