Author |
Topic |
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-03-10 : 12:07:31
|
Hello All,I run a full nightly back up every day of the database. But lately the transactions log backups are as big as the full backups now I’m running out of space. Can anyone tell me which backup plan would be better suited for my situation? Thanks. |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-10 : 12:21:34
|
Full backup: Every dayDifferential: Every 5 hoursTransaction: Every 15 min |
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-03-10 : 14:34:46
|
Thanks sodeep, for your response. In my recovery plan I did not have auto shrink selected does this matter? Should I have it selected? Please advice. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-10 : 14:51:09
|
You should not have autoshrink selected in a production environment, nor should you have a scheduled shrink job. My backup plan is the same as sodeep's but without the differential backups. I don't see the need for the differentials when you perform daily full backups. But you need to understand that the sizes will be the same if you ran one tlog backup per day or every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-10 : 15:04:42
|
'But you need to understand that the sizes will be the same if you ran one tlog backup per day or every 15 minutes.'Why is that? I think it depends on transaction rate and we take deifferential because It will be headache to apply transaction log for whole day while restoring |
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-03-10 : 15:07:06
|
tkizer thanks for your response... I'm just thinking of other ways to solve this problem. Should I purge all the old data and shrink the databases or I should request for more space to be allocated to the server? Please advice. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-10 : 15:22:27
|
Add more space.sodeep, it doesn't matter how many files you have. Add them all up and they'll equal about the same size if you only did one tlog backup in a day.And as for the restore, you just script what needs to be restored so you don't have to worry if you have 1000 files to restore or just 2 files. Differentials are typically used so you don't have to perform full backups daily. Some people will perform differentials 6 days a week and a full backup on the other day.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-10 : 15:25:34
|
I understand that Size will be same but it will be faster to restore from one differential rather than restoring each tran log file. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-10 : 15:29:13
|
Not really if you know how to script it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-10 : 15:32:57
|
Thats right Tara. I like doing by GUI . |
|
|
|