Backup and Restore

Question

Backup and Restore

saghbash

SSC-Addicted

Points: 493
More actions
April 1, 2010 at 1:04 am

#220743

We have and SQL server 2005 running two instances. I want to implement the following backup plan:
First Plan:
1. Full backup - Weekly
2. Differential - Daily at 7:00 PM
3. No transaction log backups required
Second Plan:
1. Full backup - Weekly
2. Differential - Daily at 7:00 PM
3. Log - Every 2 hours
My questions are:
for the first plan if I want to restore do I need to worry about the log file and do I need to backup the log to prevent it from becoming too big or is restricting it's size will take care of everything.
For the second plan if I need to restore do I need to apply all the backed up logs to restore successfully or can I just do the following:
Restore:
The last full backup
The last differential backup
The last log backup
Which is a better plan considering the number of daily transactions is small? I want to keep it as simple as possible.
You guidance is much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr James Howard Ten Centuries Points: 1158 More actions · Answer 1

1) It depends on the point in time you want to restore to. You can restore to the last full/diff backup, or to a later point in time depending how often the log is backed up. Restricting the size is ok if you know roughly how big the log file will be, you should aim to allow for approx 10% on top of the max size.

2) Yes, although, there will probably be a list of log backups rather than one. eg. if you back them up every 2 hours there will be one for each 2 hour period since the last full/diff backup (7pm) was taken.

3) Really depends on how much recoverability you need. If you must recover to within 2hours, you need to choose option two. Option one is far simpler and uses less resources, but you could only restore to 7pm the previous evening.

Hope this helps

James Howard

saghbash SSC-Addicted Points: 493 More actions · Answer 2

Option one is far simpler and uses less resources, but you could only restore to 7pm the previous evening.

Thanks james for the prompt response. I am for the first option, but what I am not sure about is the transaction log. do i need to back it up at all? in terms of growth and restore. what happens if I dont. restoring the database to 7pm the previous evening should be ok. otherwise maybe two diffirential backups a day.

I am worried if i implement the second option, then in case of restoring I will need to restore every single log backup.

Mr James Howard Ten Centuries Points: 1158 More actions · Answer 3

You don't NEED to back the transaction log. Its an option thats available should you need high recoverability. Basically, if you have a failure, whats the earliest time that your company would find it acceptable to be restored to? If the answer is about 8 hours, then your full + 2 diff will suffice. A diff takes all the changes since the last full so isnt as resource intensive as a full backup.

Transaction logs are straight forward when configured correctly though. You could store them in one file and then restore using SSMS, and when you select that file to restore, you are able to type in the actual time you want to restore to and SQL handles the rest for you. Its a real pleasure compared to some older versions of SQL.

Have a play with the options locally or in a test environment, set up the routines on 2 db's locally and run some tests/comparisons...

And remember to clean up old backup files to avoid disk congestion!

James Howard

ps. One Orange Chip Points: 29255 More actions · Answer 4

saghbash (4/1/2010)
Option one is far simpler and uses less resources, but you could only restore to 7pm the previous evening.
Thanks james for the prompt response. I am for the first option, but what I am not sure about is the transaction log. do i need to back it up at all? in terms of growth and restore. what happens if I dont. restoring the database to 7pm the previous evening should be ok. otherwise maybe two diffirential backups a day.
I am worried if i implement the second option, then in case of restoring I will need to restore every single log backup.

If u go for 1st option, make sure u keep the recovery model to simple, or else ur log file will eventually grow and may eat up all disk space.

If u go for 2nd option, u'll need to restore all logs which were taken after ur most recent diff backup (with an option of taking tail-log backup) to recover the database.

Pradeep Singh