Solved

Log shipping failed MSSQL 2008 R2

Posted on 2013-12-16
7
657 Views
Last Modified: 2014-01-01
We have a remote disaster recovery site for our databases which is kept up to date through log shipping. Our former DBA set this up and it worked great, but he has since left and when the passwords for the logins were changed after his departure they forgot to update the passwords for the SQL services on the disaster recovery site. This happened in July and I only found out about this today. We have updated the passwords and restarted the services, but it's been more than 5 months since the last successful log restore. The log backup and restore frequency was set to 15 minutes, with a threshhold of 1440 minutes. Because the latest successful restore has well and truly surpassed this threshhold, the logs are no longer being restored.
We currently do not have a DBA, and we lack the necessary knowledge on how to correct this. Can anyone please tell me how we can get the log shipping started again? Do we have to do a full backup/restore first? We're rather concerned that if we would get things going again the transfer of 5 months accumulated data will incapacitate our network.
Any help will be appreciated.
Thanks.
0
Comment
Question by:Koen Van Wielink
  • 4
  • 2
7 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 39725705
You need to get the Logchipping target up to date. This is done by restoring the latest fullbackup and then subsequent log backups.

This site: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc9a3630-09c7-4a29-9bec-e61b5c13c75c/reinitialize-log-shipping?forum=sqldisasterrecovery
Gives you a script for the logfile portion.

When you restore the fullbackup (i e Before the logs). Make sure you use the With norecovery option. Otherwise you cant apply the logbackups.

The link fits your scenario also, that is IF your log files gets shipped still.

Since youre without a DBA, try the example in the link, do the subsequent log restores. Look in the errorlog file for the target SQL server (i e under maintenance, logs). See that you can read: Backup LOG bla bla restored. Note the date and time. Wait a while, and see in the same log that new restores have been performed.

Case solved. If this does not work. I'd set it up from scratch. It's really not that hard.

Regards Marten
PS: You really should monitor the logs on all of your SQL servers, then this wont hit you after X months. It makes you wonder, what else you might miss in your Environment. Id prioritize a SCOM or equivilliant soulution in your case.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39725735
Hi,

Speaking to Marten's PS - make sure you have Database Mail (or the previous SQL Mail), SQL Agent connected to said mail profile, a SQL Agent Operator, and alerts with notifications.

Test to make sure said operator can get these emails. If the emails are heading for another domain etc, then they may get stopped as email system may prevent relaying. Should be easy enough to add your server to the exceptions list.

This is free and takes only a small amount of effort to setup.

And the second point is:
Is this it, or just the tip of the iceberg? What else have you missed? I use Brent Ozar's sp_Blitz to a) audit new installations, and b) audit machines I take over.
http://www.brentozar.com/blitz/ (I use the script, not the app.)

HTH
  David
0
 
LVL 12

Author Comment

by:Koen Van Wielink
ID: 39734186
I do apologize for not replying sooner, it's been a very hectic week. I'll try your suggestions when i'm back in the office on Monday or Tuesday. I really appreciate your help.
Thanks!
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

 
LVL 12

Author Comment

by:Koen Van Wielink
ID: 39735376
Ok, I've had a look at our setup and checked the link, and it appears doable. I've confirmed that the logs are still shipped to the recovery server, but obviously none were shipped while the services were down so a full database restore will be required. Which is where I need a bit more assistance.
Our DBA split the main databases over several filegroups (I see 14 on the primary server, all labeled with some descriptive file names). I have never done a database restore where the database was split over multiple secondary data files. On the recovery server I count 15 data files for the same database, but only labeled as "Databasename_1" to "Databasename_15". So if I do a full restore of our latest nightly backup, how should I go about doing this? I assume I have to restore it to the same filegroups? Also, the databases are currenly in "restoring" mode, so I assume that before I can even drop the databases and restore them from scratch I'd have to disable the current restore command. How should I proceed with that?
Thanks again for the help.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 39736015
Run in a querywindow
restore filelistonly from disk='pathtofile.bak'
compare output with the databas on the logshipserver.

regards Mårten
0
 
LVL 12

Author Comment

by:Koen Van Wielink
ID: 39737535
So far no luck. I've tried re-initializing the restores following the instructions in the link, and initially it seems to work. I managed to restore a database and restore subsequent log files as well. But after that subsequent restores did not happen. In the event viewer logs you can see the restore failed, and in the history of the restore job on the secondary database server the following message is shown (Real database name replaced with MyDB):

Skipped log backup file. Secondary DB: MyDB', File: 'D:\Log_Shipping\MyDB\MyDB_20131224090001.trn'
2013-12-24 16:15:08.73      Could not find a log backup file that could be applied to secondary database 'MyDB'.


So I tried starting from scratch. Removed the log shipping settings, the secondary databases, and log backups that were already generated. Then reset everything using this MSDN document:


I used a restore from an existing DB backup (from last night) to initialize the secondary database.
The same notification is shown in the restore job as before, and although log backups are created and copied, no restore is taking place.
What am I missing here?
0
 
LVL 12

Author Closing Comment

by:Koen Van Wielink
ID: 39750669
Had to try a couple of times, but eventually managed to get things going using your solution. The final trick was to first remove the secondary database from the primary. After that I restored the last backup, ran the log restore commands created using the script in the link (for those reviewing this for a potential solution, run the script in that link on the primary server, not the secondary) and re-configured the secondary database for the restore commands. Initally it seemed to work for only a few databases, with some still reporting errors, but after deciding to sleep on it and try again the next day to my surprise everything was running smooth the following morning.
Regarding the comments about notifications, emails, and monitoring, we are acutely aware of these concerns. Our previous DBA had everything set up correctly, with all the monitoring tools in place, but unfortunately this was not properly handed over. We do have email notifications going out for most things by now, but the disaster recovery replication was unfortunately overlooked. Hopefully this was the last of the hidden problems...
Thanks again, great help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query to summarize items per month 5 28
backups - Strategies 1 13
Caste datetime 2 25
affinity mask in sql server 1 8
First I will try to share a design of a Veeam Backup Infrastructure without Direct NFS Access backup. Note: Direct NFS Access backup transport mechanism is only available in Veeam v9 In above I try to design the Veeam Backup flow between i…
VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…

743 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

15 Experts available now in Live!

Get 1:1 Help Now