SQL server 2005- Backup Maintenance Plan Steps


Set Up For Database Maintenance Plan – Backup SQL Server 2005 by Neeraj Kathuria

Log onto the server via SQL Server Management Studio:

Create data backup plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter <dbname> Data Backup for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter <dbname> Data Backup Schedule for name, recurring, daily at 6PM. Click ok.

Drag History Cleanup Task and Backup Database Task and Maintenance Cleanup Task to right pane. Connect each box to the next.

Double click on History Cleanup Task box; Select all historical data to delete after 6 months.

Double click on Backup Database Task box, from drop down select database and click on ok, full backup. Select Create a backup for every database and Create a sub-directory for each database. Select the appropriate folder such as

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup

And bak file extension. Verify backup integrity.

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup<dbname>, enter bak for extension; delete files based on age, older than 7 days. Click ok.

Close right pane, YES to save.

Create transaction backup plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter <dbname> Tran Backup for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible.

Click Ok.

Click on Schedule; enter <dbname> Tran Backup Schedule for name, recurring, daily at 12PM. Click ok.

Drag History Cleanup Task and Backup Database Task and Maintenance Cleanup Task to right pane. Connect each box to the next.

Double click on History Cleanup Task box; Select all historical data to delete after 6 months.

Double click on Backup Database Task box, from drop down select database and click on ok, select transaction backup from dropdown. Select Create a backup for every database and Create a sub-directory for each database. Select the appropriate folder such as

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup

And trn file extension. Verify backup integrity.

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup<dbname>, enter trn for extension; delete files based on age, older than 7 days. Click ok.

Close right pane, YES to save.

Create reorg plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter <dbname> Reorg for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter <dbname> Reorg Schedule for name, recurring, weekly on Sunday at 7PM. Click ok.

Drag Shrink Database Task, Rebuild Index Task and Update Statistics Task to right pane. Connect each box to the next.

Double click on Shrink Database Task box, from drop down select database and click on ok, shrink when beyond 50MB, 10% free space, return space to operating system. Click ok.

Double click on Rebuild Index Task box, from drop down select database and click on ok, object Tables and Views, change free space 10%. Click ok.

Double click on Update Statistics Task box, from drop down select database and click on ok, object Tables and Views, update all existing statistics. Click ok

Close right pane, YES to save.

Create integrity check plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter <dbname> Integrity Check for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter <dbname> Integrity Check Schedule for name, recurring, monthly on first Sunday at 8PM. Click ok.

Drag Check Database Integrity Task to right pane.

Double click on Check Database Integrity box, from drop down select database and click on ok, include indexes. Click ok.

Close right pane, YES to save.

Create system backup plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter System Backup for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter System Backup Schedule for name, recurring, daily at 5PM. Click ok.

Drag History Cleanup Task, (2) Backup Database Tasks, and (4) Maintenance Cleanup Tasks to right pane. Connect each box to the next.

Double click on History Cleanup Task box; Select all historical data to delete after 6 months.

Double click on Backup Database Task box, from drop down select all system databases and click on ok, full backup.

Select Create a backup for every database and Create a sub-directory for each database. Select the appropriate folder such as

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSystem and bak file extension.

Double click on the next Backup Database Task box, from drop down select model and click on ok, select transaction backup from dropdown. Select Create a backup for every database and Create a sub-directory for each database.

Select the appropriate folder such as

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSystem and trn file extension

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSystemMaster enter bak for extension; delete files based on age, older than 7 days. Click ok.

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSystemmsdb enter bak for extension; delete files based on age, older than 7 days.

Click ok.

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSystemmodel enter bak for extension; delete files based on age, older than 7 days. Click ok.

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSystemmodel enter trn for extension; delete files based on age, older than 7 days. Click ok.

Close right pane, YES to save.

Create Report Server backup plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter ReportServer Backup for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter ReportServer Backup Schedule for name, recurring, daily at 6PM. Click ok.

Drag History Cleanup Task and Backup Database Task and Maintenance Cleanup Task to right pane. Connect each box to the next.

Double click on History Cleanup Task box; Select all historical data to delete after 6 months.

Double click on Backup Database Task box, from drop down select database and click on ok, full backup. Select Create a backup for every database and Create a sub-directory for each database. Select the appropriate folder such as

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup

And bak file extension.

Double click on Maintenance Cleanup Task box. Local server connection, search folder and delete files based on an extension, enter folder

E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup ReportServer, enter bak for extension, delete files based on age, older than 7 days. Click ok.

Close right pane, YES to save.

Create ReportServer reorg plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter ReportServer Reorg for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter ReportServer Reorg Schedule for name, recurring, weekly on Sunday at 7PM. Click ok.

Drag Shrink Database Task, Rebuild Index Task and Update Statistics Task to right pane. Connect each box to the next.

Double click on Shrink Database Task box, from drop down select database and click on ok, shrink when beyond 50MB, 10% free space, return space to operating system. Click ok.

Double click on Rebuild Index Task box, from drop down select database and click on ok, object Tables and Views reorg with default free space. Click ok.

Double click on Update Statistics Task box, from drop down select database and click on ok, object Tables and Views, update all existing statistics. Click ok

Close right pane, YES to save.

Create ReportServer integrity check plan Under Management | Maintenance Plan, right click and choose New Maintenance Plan. Enter ReportServer Integrity Check for name and then description.Click on connections, server should be local server, use SQL Server authentication with sa login/password if possible. Click ok.

Click on Schedule; enter ReportServer Integrity Check Schedule for name, recurring, monthly on first Sunday at 8PM. Click ok.

Drag Check Database Integrity Task to right pane.

Double click on Check Database Integrity box, from drop down select database and click on ok, include indexes. Click ok.

Close right pane, YES to save.

ReportServerTempDB No maintenance plan required.

If you need further assistance do mail me at

neerajkathuria24 ,

neeraj_kathuria24@yahoo.co.in

Advertisements

3 Responses

  1. I trust you would not have reservations if I placed a part of this site on my univeristy blog?

  2. Me English no good, but had to say me like what you say. Thank you from me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: