SQL Server 2000, 2005 2008 Point & Time Recovery?

Posted on 2011-03-01
Last Modified: 2012-05-11
If you have your SQL Server database in FULL or Bulk-Insert recovery mode and you perform transaction log backups every hour, can we recovery a database to a specific time with no database loss or your recovery is based on the time of your last transaction log backup only? We are looking for a backup solution that will guarantee no loss of data and the ability to restore a database down to a second? Is this even possible in SQL Server 2000, 2005 or 2008? Please advise.
Question by:Omega002
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
  • 4
  • 3

Accepted Solution

Forefront_Data_Solutions earned 375 total points
ID: 35012414
I know that in SQL Server 2008 you can recover all data to a point in time if you use the Full or Bulk-Logged recovery modes.  Here is the MSDN article:

Author Comment

ID: 35012551
So if my transaction log backup completed at 1pm and the next one starts at 2pm and i had some new data added at 1:27pm and my database crashed at 1:28pm will I be able to recover those transactions that occurred at 1:27pm?

Assisted Solution

Forefront_Data_Solutions earned 375 total points
ID: 35012618
Yes, as long as your log backups are complete at that time.  You would need to back up the log tail (for the transactions between 1pm and 1:27pm and then you could restore the database (using the full backup plus all log backups plus the log tail backup).  Info on the log tail backup is at:
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 35013659
That's my concern so unless I have a transaction log back every minute or 5 minutes those transactions created after the 1pm transaction log backup are loss correct?

Author Comment

ID: 35013670
So the only way to truly have point and time recovery is to setup a standby database the replicates from the primary database to the standby database correct?

Assisted Solution

Forefront_Data_Solutions earned 375 total points
ID: 35014031
No, that's not necessarily true.  How long does it take to make your transaction log backup?  Unless you have millions of transactions per hour, it probably only takes a couple of minutes.  Even if your server dies during the backup, you should be able to still recover those transactions and do your restore.  If you are looking for a High Availability situation, then you should consider having 2 servers in an active/passive failover configuration.  The database needs to be stored on a SAN so that if one server fails, the other server takes over automatically.  There would only be a few seconds/minutes of downtime, but all transactions would be saved.  I would only use replication if you need a copy of your data for something like running reports.

Author Comment

ID: 35014261
So to achieve true point and time recovery with minimal data loss is to have frequent transaction log backups every minute to five minutes correct? Realistically even with a database with millions of transaction per second SLA would be 5 minutes minimal using transaction log backups ever 4 to 5 minutes?
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 125 total points
ID: 35027823
no not correct

to have point in time recovery you can backup your logfile eaven once a day.

the number and frequency of log backups have nothing to do with your ability to recover.

you need to make a risk assesment.

1. is media failure an issue ?
2. do i store all my files in the same device ?
3. how complex should my restore process be?
4. how long should by restore process take ?
5. where and how much space do i have for saving my backups ?

on production system you should backup your database nightly full/differential
and backup your log file every hour or by calculating the percent used on the log files and automating a responce to the event when your pct used is higher than for example 65%.

you will allways lose data when you have media failure on your system (where logfdiles a kept)

you have many means to minimize this the way microsoft sql server helps you to achive minimum data loss is by using DATABASE MIRRORING.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

734 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