SQL Server Performance Forum – Threads Archive

Backup and Restoration Planning

I am taking full backup every saturday,diff backups daily and tranlog bkup every one hour for our oltp db.Now If I have to make a proper restoration plan I am getting problem
My present restoration plan is:
Restoring the latest full bkup with no recovery
Restoring the latest diff backup with no recovery
Restore all Tran log bkup one by one from the last diff bakup with no recovery and the last tranlog bkup with recovery. Now if my db is lost on 3.15pm and I have the latest tlog backup till 3pm.I am wondering how to get that 15 min data. More over if I set a log shipping on a stand by server with the same schedule,will that be helpful to get all db.As per my know ledge I will loose that 15 min data
pls guide

---

On the DR server you will have data only upto 3pm in this case.
Review thishttp://www.windowsitlibrary.com/Content/77/13/1.html#1 link for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Is there is any way by which I can get all the data,without loosing a single record.

---

To the best of my knowledge you can only recover up to your last backup. If that 15 minutes of data is too much to lose, do the transaction log backups more regularly! Ben ‘I reject your reality and substitute my own’ – Adam Savage

---

You don’t loose that 15 min data, if your tran. log doesn’t corrupted. In that case you can use BACKUP LOG WITH NO_TRUNCATE and then this backup will be the last backup to restore, and you’ll get all of your data. WBR, Vlad A. Scherbinin

---

Yes that can be done but if the hard disk crashes then it cant be done.
I am wondering whether there is any option in log shipping to accompolish this kind of task.

---

Is there is any technique ..third party tools ..methods available (apart from clusturing and replication as they are costly) which can be employed to get that 15 min lost data.

---

for log shipping you would need the backup of the log…dont know about 3rd party tools, but replication sounds like it would fit the bill (but all my expreiences setting up replication were bad ones, so thats all i have to say on that!) ‘I reject your reality and substitute my own’ – Adam Savage

---

As per my experience, I can say without using SQL Transactional replication it is hard to believe that data is safe all the time, and at every bad situation you can recover till last transaction happened. If you want to avoid being caught in bad shape, I will suggest having RAID 1 for Log file storage. This will reduce 50% chances of problem by disk. Another, try to have frequent transaction log backup.. Say after every 10 or 15 minutes depending upon work done by your database (log space used) and criticality level. (to another RAID/ or disk, other then sql data/ log) At situation, when your data files are corrupt and you need to restore using backups.. Don#%92t forget to take tail of transaction log backup using BACKUP LOG WITH NO_TRUNCATE and apply it in the last.
Deepak Kumar –An eye for an eye and everyone shall be blind

---

]]>