Solved

SQL Server 2000, 2005 2008 Point & Time Recovery?

Posted on 2011-03-01
8
275 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 42
Refresh Dev server with Production database 8 27
sql server insert 12 30
Deal with apostrophe in stored procedures 8 41
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

785 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