Solved

Transaction log(ldf) - apply it to db in any way?

Posted on 2004-09-20
10
230 Views
Last Modified: 2012-05-05
I have full backups at 23:00 and transaction log backups every second hour during the day. All the backups are automatically restored on a second server. If the main server breaks down(the motherboard for example) I will be able to get out the ldf files but so far I can´t use them.

I cannot accept the answers I have found so far on this subject(that its is impossible to use the ldf files). Since the ldf contains all the transactions yet not written to the mdf WHY is it not possible to use that information, isn´t there any tools for this??
0
Comment
Question by:bouleIT
[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
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12109177
>>I will be able to get out the ldf files but so far I can´t use them.<<
Can you translate what this means?  Is there an error, if so what is it?

What is your Recovery model?
0
 

Author Comment

by:bouleIT
ID: 12110102
I use the full recovery model. On the mainserver I have a RAID 5 set for the mdf files and a mirror set with two separate disks for the ldf files.
For example, at 15:00 there is a backup of the transaction log. The log is dumped to another server and restored at that server. The next backup is at 18:00. If the main server breakes down at 17:55(in my example the motherboard or the raidset)  I will not be able to get access to the mdf files but I can remove the disk containing the ldf files, put it in another server and get access to those files. The problem is that you can´t attach a ldf file to a database on the backup server. You need a backup of the same file which I find stupid.  
0
 
LVL 1

Expert Comment

by:ccsenet
ID: 12110263
Think of using transactional replication to your backup server.

Without mdf files how do you plan to attach the database. Can you please elaborate
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

 

Author Comment

by:bouleIT
ID: 12110372
Transactional replication in sql 2000 makes the mainserver very slow so that is not an option.

The whole point of this solution is to both have a high security level by having the data both on backuptapes and all the "backupfiles" restored on another server, and win time if the mainserver completely brakes down. I have a backup of the mdf files once every night and backups ot the transaction logs every second hour. The same goes for the restore process meaning that the databases on the "standby" or backup server are never more than two hours old.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12112991
If you need up-to-the-minute recovery, you need to use some form of replication (or perhaps clustering).

Naturally you can reduce the amount of lost data by doing more frequent log backups, so perhaps you can do them every hour or even every half hour.
0
 
LVL 1

Expert Comment

by:ccsenet
ID: 12153868
Think of using Log Shipping. Once your primary server goes down, the standby server can be made as a primary server. You may need to have a third  server working as a monitor.

For more details on log shipping see SQL Books Online
0
 

Author Comment

by:bouleIT
ID: 12367346
I am at the moment evaluation "Log Explorer" and so far it works. I have been able to apply the logs to the DB at the second server without having access to the primary server.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12407193
Question answered by asker or dialog valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

687 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