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?

Improve company productivity with a Business Account.Sign Up

x
 
Forefront_Data_SolutionsConnect With a Mentor Commented:
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_SolutionsConnect With a Mentor Commented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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_SolutionsConnect With a Mentor Commented:
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 ShiloConnect With a Mentor Chief 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.