This maintenance plans to clean up the database, make backups (data and log), and do some performance optimization.
Weekly Database Maintenance Plans
1.Start the Microsoft SQL Server Management Studio and log in as the ‘sa’ user. Open the “Management” -> “Maintenance Plans” -> Select “Maintenance Plan Wizard.”
2.On the “Select Plan Properties” page give it the name “Weekly Maintenance Plan”. Select “Single schedule for the entire plan or no schedule” and click the “Change” button to set schedule. Click Next.
3. On the “Select Maintenance Tasks” page, choose: Check Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, Back Up Database (Full), Back Up Database (Transaction Log) and Maintenance Cleanup Task.
4. On the “Select Maintenance Task Order” page, move “Back Up Database (Full)” to after “Check Database Integrity”.
5. For “Define Database Integrity Check”,choose all databases and check including indexes.
6. For “Define Reorganize Index” choose all databases and check compact large objects.
7. For “Define Rebuild Index” choose all databases, reorganize pages with the default amount of free space and check “Keep index online while reindexing.”
8. For “Define Update Statistics” choose all databases, all existing statistics and full scan.
9. For “Define Back Up Database (Full)”,choose all databases, and ignore databases where the state is not online. Set the backup set will expire after 21 days, back up to disk, create a backup file for every database and create a sub-directory for each database. Set the backup folder to be on my data drive, G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Data, and to use a backup file extension of “bak”.
10. For “Define Back Up Database (Transaction Log)”,choose all databases, and ignore databases where the state is not online. Set the backup set will expire after 21 days, back up to disk, create a backup file for every database and create a sub-directory for each database. Set the backup folder to be on my data drive, G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Log, and to use a backup file extension of “trn”.
11. For “Define Maintenance Cleanup” I have it delete backup files, using “G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Data” as the search folder location, “bak” as the file extension and include first-level subfolders and delete files older than 22 days.
12. Pick report options you’d like. Click Next and Finish.
13. Go into the Maintenance Plans folder, double click on this job. Drag "Maintenance Cleanup Task" from toolbox, name it "Maintenance Cleanup Task Log". Set the delete backup log files, using “G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Log” as the search folder location, “trn” as the file extension and include first-level subfolders and delete files older than 22 days. Make link between both maintenance task.
14. Now right click on this job, and choose “Execute” to see if it runs. Check the logs if it doesn’t.
Daily Database Maintenance Plans
1. Go back into the Maintenance Plan Wizard and create a new plan called “Daily Differential Backup”. Set the schedule to recur Monday through Saturday at a good time. You don’t need to schedule it on Sunday if you have the full backup happening then.
2. Choose only “Back Up Database (Differential)” and configure the plan. Again, I set it to back up all databases, ignoring databases where the state is not online, and to back up to the same location as above (G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup) with the “bak” extension, expiring after 21 days.
Restore a Backup Work
1. Make sure the backups are happening. You might want to try restoring a backup.
2.Watch your disk space for backups and logs, too. If you are getting a lot of logs you can add or change a maintenance plan with a maintenance cleanup task to delete old logs.
Weekly Database Maintenance Plans
1.Start the Microsoft SQL Server Management Studio and log in as the ‘sa’ user. Open the “Management” -> “Maintenance Plans” -> Select “Maintenance Plan Wizard.”
2.On the “Select Plan Properties” page give it the name “Weekly Maintenance Plan”. Select “Single schedule for the entire plan or no schedule” and click the “Change” button to set schedule. Click Next.
3. On the “Select Maintenance Tasks” page, choose: Check Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, Back Up Database (Full), Back Up Database (Transaction Log) and Maintenance Cleanup Task.
4. On the “Select Maintenance Task Order” page, move “Back Up Database (Full)” to after “Check Database Integrity”.
5. For “Define Database Integrity Check”,choose all databases and check including indexes.
6. For “Define Reorganize Index” choose all databases and check compact large objects.
7. For “Define Rebuild Index” choose all databases, reorganize pages with the default amount of free space and check “Keep index online while reindexing.”
8. For “Define Update Statistics” choose all databases, all existing statistics and full scan.
9. For “Define Back Up Database (Full)”,choose all databases, and ignore databases where the state is not online. Set the backup set will expire after 21 days, back up to disk, create a backup file for every database and create a sub-directory for each database. Set the backup folder to be on my data drive, G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Data, and to use a backup file extension of “bak”.
10. For “Define Back Up Database (Transaction Log)”,choose all databases, and ignore databases where the state is not online. Set the backup set will expire after 21 days, back up to disk, create a backup file for every database and create a sub-directory for each database. Set the backup folder to be on my data drive, G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Log, and to use a backup file extension of “trn”.
11. For “Define Maintenance Cleanup” I have it delete backup files, using “G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Data” as the search folder location, “bak” as the file extension and include first-level subfolders and delete files older than 22 days.
13. Go into the Maintenance Plans folder, double click on this job. Drag "Maintenance Cleanup Task" from toolbox, name it "Maintenance Cleanup Task Log". Set the delete backup log files, using “G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Log” as the search folder location, “trn” as the file extension and include first-level subfolders and delete files older than 22 days. Make link between both maintenance task.
Daily Database Maintenance Plans
1. Go back into the Maintenance Plan Wizard and create a new plan called “Daily Differential Backup”. Set the schedule to recur Monday through Saturday at a good time. You don’t need to schedule it on Sunday if you have the full backup happening then.
2. Choose only “Back Up Database (Differential)” and configure the plan. Again, I set it to back up all databases, ignoring databases where the state is not online, and to back up to the same location as above (G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup) with the “bak” extension, expiring after 21 days.
Restore a Backup Work
1. Make sure the backups are happening. You might want to try restoring a backup.
2.Watch your disk space for backups and logs, too. If you are getting a lot of logs you can add or change a maintenance plan with a maintenance cleanup task to delete old logs.
MS Dynamics CRM Contributor
5 comments:
beautiful post.
Mifare readers
Mifare cards
SQL SERVER2008 R2 doest contain management PLAN
SQL SERVER2008 R2 doest contain MAINTENANCE PLAN your screen shots refer to SQL SErver 2008 Only, if you know any option in SQL Server 2008 R2 for maintenance please do let us know.
Please rename the post title as SQL Server 2008 Maintenance Plan because its misleading..
2008 R2 has maintenance plans. You can find it under Management
Thank you for sharing. The most effective maintenance planning and scheduling practices efficiently use labor; manage parts, tools, and drawings; reduce time spent waiting for jobs to be assigned; and streamline complex down-days and shutdowns.
Post a Comment