Steps to configure Maintenance Plans in SQL server
DB Backup Maintenance Plan
FULL:
=====
a)Click Maintenance Plan = > New maintenance Plan and rename the Plan as------------------------------------------------>DB Backup Plan ( as per company standards)
b)Double click the Sub plan and name it as ------------------------>Full.
In the description mentio--------------->All databases Full back up Plan
c)Drag the Backup database task from the Right Pane (Maintenance Plan Tasks) and Double click the Backup Database task.
d)Select the backup type as FULL and Databases as ------->“ All databases”.
e)Select the option ------------------------------------->“ Create a subdirectory for each database”
f)Select the correct folder (Normally F;\ MSSQL\Backup) and Backup file extension as-----> “BAK”.
g)Drag maintenance Clean task from the maintenance Plan Tasks and place it below Backup Database task and make connection between these two.
h)Enter the folder details then enter the file extension as ------->“:BAK”.
i)Select the option (“Delete the files based on age”) and select the option files older than----> ("3 days").
j)Drag the History cleanup task and place it below maintenance clean task and set the time period as-----> ("10 weeks")<----- to remove the historical data.
k)Schedule the Full backup at------> ("12.30 AM")<------- Everyday.
Translog:
==========
a)Click add subplan and name it as------------------------------------>Translog
In the description mention as------->All databases Translog back up Plan.
b)Drag the Backup database task from the Right Pane (Maintenance Plan Tasks) and Double click the Backup Database task
c)Select the backup type as Transactional Log and Databases as -------------->“ All User databases”.
Select the option ---------------------------------------------------------->“ Create a subdirectory for each database”
d)Select the correct folder (Normally F;\ MSSQL\Backup) and Backup file extension as---------> “TRN”.
e)Drag maintenance Clean task from the maintenance Plan Tasks and place it below Backup.
f)Database task and make connection between these two.
g)Enter the folder details then enter the file extension as------------------> “:TRN”.
h)Select the option “Delete the files based on age” and select the option files older than-------------> 3 days.
i)Schedule transaction log backup at every 4 hours starting from---------------------------------------> 5 AM to 11.59 PM.
DB Maintenance Plans: (Rebuid Index , DB Integrity Check and Updatestats)
===========================================
a)Create another maintenance plan and name it as ----------------->DB Maintenance Plan
b)double click on Sub plan and name it as ---------------->Rebuild index
and description as--------------------------------->All databases rebuild index
Schedule it Every week----------------------------> Sunday at 3 AM (Note - as per company requirements we can update the TIME )
c)Drag the rebuild index task and select all databases and the option------> Sort results in tempdb
d)Add Sub plan and name it as------------>Integrity checks
and description as------------------------------------->All databases integrity checks
‘and schedule Every week-------------------------------------------->Saturday at 3 AM.
e)Configure updatestats job separately.
Add Sub plan and name it as------------>UpdateStats and description as------------------------------------->All databases Update Statistics ‘and schedule Daily Basis --------> Daily at 3 AM
DB Backup Maintenance Plan
FULL:
=====
a)Click Maintenance Plan = > New maintenance Plan and rename the Plan as------------------------------------------------>DB Backup Plan ( as per company standards)
b)Double click the Sub plan and name it as ------------------------>Full.
In the description mentio--------------->All databases Full back up Plan
c)Drag the Backup database task from the Right Pane (Maintenance Plan Tasks) and Double click the Backup Database task.
d)Select the backup type as FULL and Databases as ------->“ All databases”.
e)Select the option ------------------------------------->“ Create a subdirectory for each database”
f)Select the correct folder (Normally F;\ MSSQL\Backup) and Backup file extension as-----> “BAK”.
g)Drag maintenance Clean task from the maintenance Plan Tasks and place it below Backup Database task and make connection between these two.
h)Enter the folder details then enter the file extension as ------->“:BAK”.
i)Select the option (“Delete the files based on age”) and select the option files older than----> ("3 days").
j)Drag the History cleanup task and place it below maintenance clean task and set the time period as-----> ("10 weeks")<----- to remove the historical data.
k)Schedule the Full backup at------> ("12.30 AM")<------- Everyday.
Translog:
==========
a)Click add subplan and name it as------------------------------------>Translog
In the description mention as------->All databases Translog back up Plan.
b)Drag the Backup database task from the Right Pane (Maintenance Plan Tasks) and Double click the Backup Database task
c)Select the backup type as Transactional Log and Databases as -------------->“ All User databases”.
Select the option ---------------------------------------------------------->“ Create a subdirectory for each database”
d)Select the correct folder (Normally F;\ MSSQL\Backup) and Backup file extension as---------> “TRN”.
e)Drag maintenance Clean task from the maintenance Plan Tasks and place it below Backup.
f)Database task and make connection between these two.
g)Enter the folder details then enter the file extension as------------------> “:TRN”.
h)Select the option “Delete the files based on age” and select the option files older than-------------> 3 days.
i)Schedule transaction log backup at every 4 hours starting from---------------------------------------> 5 AM to 11.59 PM.
DB Maintenance Plans: (Rebuid Index , DB Integrity Check and Updatestats)
===========================================
a)Create another maintenance plan and name it as ----------------->DB Maintenance Plan
b)double click on Sub plan and name it as ---------------->Rebuild index
and description as--------------------------------->All databases rebuild index
Schedule it Every week----------------------------> Sunday at 3 AM (Note - as per company requirements we can update the TIME )
c)Drag the rebuild index task and select all databases and the option------> Sort results in tempdb
d)Add Sub plan and name it as------------>Integrity checks
and description as------------------------------------->All databases integrity checks
‘and schedule Every week-------------------------------------------->Saturday at 3 AM.
e)Configure updatestats job separately.
Add Sub plan and name it as------------>UpdateStats and description as------------------------------------->All databases Update Statistics ‘and schedule Daily Basis --------> Daily at 3 AM
