Solved

SQL Server 2000, 2005 2008 Point & Time Recovery?

Posted on 2011-03-01
8
284 Views
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.
0
Comment
Question by:Omega002
  • 4
  • 3
8 Comments
 
LVL 8

Accepted Solution

by:
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:  http://msdn.microsoft.com/en-us/library/ms189275%28v=SQL.100%29.aspx
0
 

Author Comment

by:Omega002
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?
0
 
LVL 8

Assisted Solution

by:Forefront_Data_Solutions
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:  http://msdn.microsoft.com/en-us/library/ms179314%28v=SQL.100%29.aspx
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Omega002
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?
0
 

Author Comment

by:Omega002
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?
0
 
LVL 8

Assisted Solution

by:Forefront_Data_Solutions
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.
0
 

Author Comment

by:Omega002
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?
0
 
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.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 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