?
Solved

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

Posted on 2004-09-20
10
Medium Priority
?
231 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

762 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