September 30, 2009 at 6:21 am
My environment (coming in with it set) for SQL Server 2005 dictates that I have the multiple back-end SharePoint databases set to FULL recovery mode (not Simple).
This being the case: Should I implement a mixed backup strategy, aside from my daily FULL Database backup, of differential and transaction log backups?
I will need to do frequent Trans Log Truncate and Shrinkfile operations to keep these files from getting too big, so this has to be kept in mind.
Newbie accidental DBA needs help and counsel here. Thanks.
Zee - Atlanta GA
General Dynamics I.T.
SS 2005 DBA (Novice)
September 30, 2009 at 6:30 am
Fred Zimmerman (9/30/2009)
My environment (coming in with it set) for SQL Server 2005 dictates that I have the multiple back-end SharePoint databases set to FULL recovery mode (not Simple).This being the case: Should I implement a mixed backup strategy, aside from my daily FULL Database backup, of differential and transaction log backups?
I will need to do frequent Trans Log Truncate and Shrinkfile operations to keep these files from getting too big, so this has to be kept in mind.
Newbie accidental DBA needs help and counsel here. Thanks.
Zee - Atlanta GA
General Dynamics I.T.
SS 2005 DBA (Novice)
I wouldnt be so keen to jump straight in and schedule log truncate or shrinkfile as it has performance hit and increases fragmentation.
if your log backup are frequent enough, you will keep the database size down. so dont worry about having to use shrinkfile. you can use differential to help keep the number of log backups down, if you backup log every 5 minutes or so for example. as when you do a restore, you would use the last full backup, then the latest differential then the latest log files if you wanted to do a point in time restore.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 30, 2009 at 6:31 am
If you are doing regular transaction log back ups, there is actually no need to shrink or truncate the transaction log. When you do a transaction log back up, it will release the space used and free it up. So the chances are that the transaction log will not grow. It will stay in almost the same size.
But make sure to take regular transaction log back up depending on the business needs. If you truncate the transaction log, you will break the chain thus making point of recovery very difficult.
-Roy
September 30, 2009 at 6:50 am
Start here :
- Gails very nice summary: http://www.sqlservercentral.com/articles/64582/
- Books online topic "Implementing Restore Scenarios for SQL Server Databases"
You really need :
- Full backup (= snapshot of your database)
- log backup ( = activity report of your database. A log backup marks all completed transactions to be overwritable ) If you don't make log backups, you log files will keep on growing !
- Diff backup ? That depends on the modification volume within the time frames.
You should handle this having a recovery scenario in mind !
How long will your db need to get restored to a certain point in time?
If you end up losing to much time handling the restore of your log-backups, and the overhead (= 100% I/O of your db pages !) of taking a diff backup is affordable, differentail backups will help out.
Also to be kept in mind: a diff backup will always record all modified pages since the last Full backup.
-edited-
You should avoid shrink operations (at least on production environments). The only acceptable reason for a shrink operation is data has been cleaned up and you don't expect your data size to grow again within a reasonable timeframe. A file extend is an expensive operation for your db engine.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 30, 2009 at 8:19 am
Fred Zimmerman (9/30/2009)
databases set to FULL recovery mode
That means your recovery strategy calls for point-in-time recovery.
- DO Full and T-Log backups.
How about a daily full backup and hourly T-Log backups?
- Never ever truncate logs
- Never ever shirnk files
How big are your databases?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2009 at 8:34 am
I'll add in, what is the amount of data you can lose? Or can deal with a loss? how quickly do you need to recover?
I use diffs to keep daily, or hourly, backup sizes manageable. Especially if lots of data doesn't change. Then use logs every 5 minutes-1 hour to determine how much recovery you have to do. Some people run logs every minute and copy them off the box to ensure they can recover easily. Some go every hour.
While you might be able to recover beyond the last log backup, if you can get to the tail of the log, you might not. That's why you schedule log backups to prevent data loss.
Great advice above, especially Gail's article.
October 1, 2009 at 12:40 pm
Seemingly great answer but leaves me ?s.
1) What do you set your Recovery to on your databases backed up.
2) Aside from stated Diff backups you do, do you also do Trans Log backups in addition?
what is your interleave, eg.?
Tran Log Backup 1:00 a.m.
Tran Log Backup 6:00 a.m.
Tran Log Backup 12:00 p.m.
Tran Log Backup 6:00 p.m.
DB Differential Bkup 9:00 pm.
DB Full Backup 12:00 a.m.
Thanks,
Zee.
October 2, 2009 at 2:15 am
Fred Zimmerman (10/1/2009)
Seemingly great answer but leaves me ?s.1) What do you set your Recovery to on your databases backed up.
2) Aside from stated Diff backups you do, do you also do Trans Log backups in addition?
what is your interleave, eg.?
Tran Log Backup 1:00 a.m.
Tran Log Backup 6:00 a.m.
Tran Log Backup 12:00 p.m.
Tran Log Backup 6:00 p.m.
DB Differential Bkup 9:00 pm.
DB Full Backup 12:00 a.m.
Thanks,
Zee.
You need to plan your backup types according to the DRP/SLA you need with the choosen database recovery type (simple/full).
A database recovery type should never change, unless DRP/SLA changes !
You must read the refered BOL to get the picture concerning time lines, recovery interval, allowed data loss, ... !!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 2, 2009 at 2:35 am
Fred Zimmerman (10/1/2009)
Seemingly great answer but leaves me ?s.1) What do you set your Recovery to on your databases backed up.
2) Aside from stated Diff backups you do, do you also do Trans Log backups in addition?
what is your interleave, eg.?
Tran Log Backup 1:00 a.m.
Tran Log Backup 6:00 a.m.
Tran Log Backup 12:00 p.m.
Tran Log Backup 6:00 p.m.
DB Differential Bkup 9:00 pm.
DB Full Backup 12:00 a.m.
Thanks,
Zee.
ok, if you need the ability to restore to a point of time, like yesterday at 4am for example. you will need the recovery model to be full, otherwise you can have it as simple. changing the recovery model between different models can cause issues, so you dont do it, unless you have a specific need.
If you have the recovery model as full, you need to do tran log backups to manage/control the size of the log file, as a tran log backup frees up space in the log file. an example schedule might be as follows.
full backup after working hours
tran log backups during the working day
It is all down to the SLA (Service level agreement), what it basically comes down to, is how much data can you afford to lose in the event of failure. if you can afford to lose a days data, then you could do a full backup every day after hours and in the event of failure, you restore last nights backup and you lose all work done after that backup was taken. if the business can only afford to lose 15 minutes worth of work, then you schedule tran log backups every 15 minutes. then in the event of failure. you restore from the last full backup and the additional log files up to the point in time that you need.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply