Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jazzcatone
Starting Member
12 Posts |
 Posted - 2010-06-14 : 19:28:23
|
| I am in the middle of devising a sql server 2008 backup and restore strategy. Our business requirements in terms of the data being available are pretty strenuous so I am pretty certain we will go with a full recovery model. Am looking to make certain I can do a point in time restore if need be. Am thinking our backup strategy will need to be something silmilar to the one shown here. (http://msdn.microsoft.com/en-us/library/ms190217(v=SQL.105).aspx)Was reading this other article (http://msdn.microsoft.com/en-us/library/ms190982.aspx) about how to restore to a point in time. Question is How do I know whether I need to restore a transaction log or the database? (See step 4 in the article) It is my understanding that normally a transaction log is restored after a full or differential backup in order to roll the db forward. In what scenario would I restore just a transaction log?Any help would be greatly appreciated. Jason |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-14 : 20:11:42
|
" Our business requirements in terms of the data being available are pretty strenuous so I am pretty certain we will go with a full recovery model."let me clarify that for you  you are definitely going with Full Recovery model unless you have a read-only / enquiry only database.Here's a couple of scenarios:1) Someone deletes some records accidentally. They don't notice for a little while, meantime other people have been doing data entry. It is undesirable to restore back and lose the work the other people have done.Solution: Restore last full backup, followed by all TLog backups chronologically, up to the one AFTER the accident to a new, temporary, database. Use STOP AT command to only restore up to a point in time shortly before the accident. Use SQL command to copy the "lost" rows from Temporary database back into Live database.2) You have disk failure, or some other cause, that corrupts your database.Lock all users out of the database. Take a "tail" TLog backup. (Because the Log is written to separately from the database it is usually readable even when the database file has become corrupted. It is also good practice to put the Log on a different drive sub-system, which makes it more immune to failure in the disk controller sub-system that the data itself is on).Restore the last full backup, and all TLog backups since (including the TAIL TLog backup). Perform a Data Consistency check to ensure that there is no corruption in the restored database. Assuming all is well you will have lost NO data.3) You restore a Full Backup and discover it is corrupted. Restore an earlier Full Backup, and all TLog backups since. For the reasons above this is likely to be "clean", but at the very least it gives you a "second chance".4) You suspect fraud. You need to examine what happened / when. Restore a full backup (from months ago, maybe). Play forward the Tlog backups using STANDBY (which allows you to have Read-only access to the database, but to continue restoring more TLog backups later). You can choose the restore granularity you want, run various queries, and keep restoring forwards in time accumulating evidence of the fraudulent acts.Compare this to using Simple Recovery Model and thus only having Full Backups:1) Restore back to the last full backup, repeat the work since.2) If that full backup is corrupted restore from the next-earlier full backup, and repeat the work since that backup was taken. |
 |
|
|
jazzcatone
Starting Member
12 Posts |
Posted - 2010-06-15 : 19:00:15
|
| Thank you. Replies were very helpful.Jason |
|
|
|
lbseong
Starting Member
5 Posts |
Posted - 2011-02-28 : 23:45:01
|
| Hi expert... I would like to ask if I have perform a Full Backup on Sunday and follow by the Tlog backup every 4 hours...from Monday to Sat.Now, DB corrupted...and I am performing the restoration of the DB from Full backup (Sunday)...follow by tlog...but what happen is The Thursday Tlog backup was corrupted/missing, Can i still continue to restore the tlog backup to point in time? How? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-01 : 00:48:21
|
| No you cannot. you can restore to the point of the missing log, no further.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
SQL Backup and Restore Strategy