| Author |
Topic |
|
maksuda
Starting Member
15 Posts |
 Posted - 2011-03-11 : 16:48:34
|
| Hi:I'm backing up a production database where 10/12 databases are running. I'm backing up the database as the following sequences :-> Full back up - once in a day-> Differential backup - every 4 hours -> Transaction Log back up - every 2 hoursBack up process is working fine and backing up as they are cheduled. I'm wonder about the restore or recovery process.In case I need to restore the database what should be the process/sequence to resotr it? Thanks in advance for the help.Maksuda... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-11 : 16:57:34
|
| - Restore (most recent) full database backup- Restore (most recent) differential backup made AFTER the full backup you restored *- Restore (most recent) log backup made AFTER the differential backup you restoredRestoring differential backups is optional as long as you have the complete log backup chain since the last full backup. However it can save a lot of time if you have a lot of log backups, or large log backups. It probably won't help you though since the interval between differentials and log backups is pretty small (2x log backups).If you need to restore to a point in time between the full backup and next differential, just restore the log(s). You can only restore to a point in time with the log. Books Online describes the restore process in more detail. |
 |
|
|
maksuda
Starting Member
15 Posts |
Posted - 2011-03-11 : 17:34:16
|
| Thanks robvolk very much for the post. The database is very large and it imports data every hour from different databases. Backing up Transaction log every two hours is not enough in that case? What you meant by 'a lot of log backup' ? Backup log more frequent like every hour?Thanks again fro the reply.Maksuda... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-11 : 17:43:49
|
| Yes, more frequently. I would even suggest every 15 minutes.When backing up, you need to consider how much work is "acceptable" to lose in case you have a hard crash and need to restore elsewhere. If 2 hours is acceptable, then your current backup schedule is fine. This is known as Recovery Point Objective (RPO).You also need to consider how long it takes to restore your system to a functioning status, this is Recovery Time Objective (RTO). You don't want to have a lot of backup files to restore, so you have to balance how often you back up with how much work is acceptable to lose.You can read more on RPO/RTO here: http://blog.bakbone.com/2009/09/09/rto-versus-rpo/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
maksuda
Starting Member
15 Posts |
Posted - 2011-03-14 : 11:36:09
|
| Thank you very much for your mail. I got the idea about the resoting database and TLog backup.In case of hard crash I would preafure as much data decover as possible. Every 15 minutes TLog backup is good idea. robvolk - your last comment confused me again-'You don't want to have a lot of backup files to restore' - I'm sorry I didn't get it- 'lot of backup files'Whatever the backup schedule is (even I change my TLog backup schedule every 15 minutes), I think I need three backup files always to resotore(according to your first post). If I got the wrong understanding, really sorry. Can you please help me in that case to understand the restore of db.Thanks again for the reply.Maksuda... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-14 : 14:35:11
|
| No, you need at least 3 files, usually more.Restore the fullRestore the latest diffRestore ALL the log backups since the diff in order.Differential backups are cumulative so you only need the latest one, log backups are not so you need all of them since the full or diff that you're using.--Gail ShawSQL Server MVP |
|
|
|
maksuda
Starting Member
15 Posts |
Posted - 2011-03-14 : 15:02:29
|
| Thank you Gila Shaw, thank you very much, it's very much clear now........Maksuda... |
|
|
|
|
How to restore from backup files