SQL Server 2000, 2005 2008 Point & Time Recovery?

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.
Omega002Asked:
Who is Participating?
 
Forefront_Data_SolutionsCommented:
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
 
Omega002Author Commented:
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
 
Forefront_Data_SolutionsCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Omega002Author Commented:
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
 
Omega002Author Commented:
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
 
Forefront_Data_SolutionsCommented:
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
 
Omega002Author Commented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.