| Author |
Topic |
|
hey001us
Posting Yak Master
185 Posts |
 Posted - 2009-02-23 : 22:41:49
|
| I have setup the database maintenance plan. You can add up if I missed out any step(s).Here is the list of plans. 1. dmp_Backup transaction logs - hourly2. dmp_Backup user databases – daily3. dmp_Backup system databases – weekly4. dmp_Cycle Error Log – weekly5. dmp_Defragment indexes – weekly6. dmp_Delete history – weekly7. dmp_Integrity checks – weekly8. dmp_Clear Windows Log file - weekly9. dmp_UPDATE_STATISTICS - weeklyItem 1 to 3 is two steps:• Step 1 will do backup• Step 2 will delete the old file if step 1 successItem 6 Delete history includes: • EXEC msdb.dbo.sp_delete_backuphistory @dt• EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @dt• EXEC msdb.dbo.sp_maintplan_delete_log null,null, @dt• EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @dt• EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @dtThankshey |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hey001us
Posting Yak Master
185 Posts |
Posted - 2009-02-23 : 22:49:39
|
| Sorry i missed out to add item 9. noted your comments. Thanks muchhey |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-24 : 00:30:41
|
| You're welcome. You may want to add in differential backups also for faster recovery times. In one of my environments, I do a differential once a day, 12 hours after the full backup. In other environments that we have, we perform differentials 3-5 times per week, and then on the days where no differential runs, we perform a full backup. How you structure your backups is dependent upon your environment and business requirements.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-02-24 : 00:56:59
|
quote:
1. dmp_Backup transaction logs - hourly2. dmp_Backup user databases – daily3. dmp_Backup system databases – weekly4. dmp_Cycle Error Log – weekly5. dmp_Defragment indexes – weekly6. dmp_Delete history – weekly7. dmp_Integrity checks – weekly8. dmp_Clear Windows Log file - weekly9. dmp_UPDATE_STATISTICS - weekly
I think you should backup the transaction log after index defrag .....coz defrag will increase the transaction log size....Regards,Ahmad Osama |
|
|
|
hey001us
Posting Yak Master
185 Posts |
Posted - 2009-02-24 : 17:02:53
|
| Tara,Thanks once again :) I am not much familiar about deferential backup. Can you explain more detail so I can implement. Ahmad,Thanks for your comments.The log backup is running on hourly and the indexes defragment running on weekly. How to make it? hey |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-24 : 17:06:30
|
| A differential backup includes all of the changes since the last full backup. A transaction log backup is just the changes since the last transaction log backup or a full backup if the log chain was broken. If it still isn't clear, I would suggest reading about the different types of backups in BOL as well as on the web. Differentials are beneficial as they might save disk space and might save on recovery time.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
hey001us
Posting Yak Master
185 Posts |
Posted - 2009-02-24 : 18:22:45
|
| Cool, I will go through. Cheershey |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-02-25 : 07:06:53
|
quote:
Originally posted by hey001us
Tara,Thanks once again :) I am not much familiar about deferential backup. Can you explain more detail so I can implement. Ahmad,Thanks for your comments.The log backup is running on hourly and the indexes defragment running on weekly. How to make it? hey
You can schedule the index defrag in a way that it falls between 2 transaction log backups .... pls do chk the time it takes to defrag the index....Regards,Ahmad Osama |
|
|
|
|
Database Maintenance Plan