question

cornpoppy avatar image
cornpoppy asked

Could you help me on creating a better maintenance plan

Could you help me on creating a better maintenance plan when using An AlwaysOn Database Availability Group. i use Ola Hallengren's Maintenance Scripts. Is it correct? **FULL BACKUP** EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES', @Directory = 'E:\BKUP', @BackupType = 'FULL', @Verify = 'Y', @Compress = 'Y', @CheckSum = 'Y', @CleanupTime = 168, @LogToTable = 'Y', @CopyOnly='Y', @OverrideBackupPreference ='Y' **LOG BACKUP** EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES', @Directory = 'F:\BKUP', @BackupType = 'LOG', @Verify = 'Y', @ChangeBackupType = 'Y', @CleanupTime = 48 **DIFFERENTIAL BACKUP** EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES', @Directory = 'E:\BKUP', @BackupType = 'DIFF', @Verify = 'Y', @Compress = 'Y', @CheckSum = 'Y', @CleanupTime = 25, @LogToTable = 'Y' **REBUILD** EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 0, @LogToTable = 'Y', @UpdateStatistics = 'ALL', @Indexes='ALL_INDEXES', @SortInTempdb = 'Y' Thank you
dbamaintenance-plansdba-developerola-hallengren
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you clarify? What outcome are you looking for? Ola's stuff is pretty rock solid (IMHO) so I'm not sure what you are looking for here.
2 Likes 2 ·
@cornpoppy I noticed that you pass 'Y' for @CopyOnly. Is this intended? Passing 'Y' will cause the copy only backups which might not be what you want for the maintenance plan. This option is absolutely great when you need to take a backup of production for the sake of restoring it, say, to QA instance, and you would like to do so without disturbing the existing backup sequences which will most probably aggravate the DBAs (or you if you are a DBA). The option, however, might not be suitable for maintenance plans, it all depends on what you want to accomplish. @Ola Hallengren's script is rock solid indeed, but it is best to spend some time to investigate the consequences of every parameter value in order to understand their purpose. [Ola's database backup page][1] provides a clear explanation of all parameters. [1]: https://ola.hallengren.com/sql-server-backup.html
2 Likes 2 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.