| A SQL Server 2000 Backup Plan |
| Backup Preparation |
The backup processes defined here can only work against hard drives local to the server. From there, a tape backup
process should pick up and store these files on a regular basis.
Because of differences and limitations in backing up system versus user databases and tables, two separate jobs
should be defined to handle the full backup need. First, prepare the system to define the scheduled jobs:
- Start up Enterprise Manager.
- Expand the target server.
- Expand the Management folder.
- Right-click on SQL Server Agent and choose Start.
|
| Backing Up The System Tables |
Now define the scheduled job to handle system databases and tables:
- Right-click on Database Maintenance Plans and choose New Maintenance Plan... to start the
wizard, then click Next>.
- Select All system databases then click Next>.
- Select Reorganize data and index pages; make sure Change free space per page percentage to: is
selected and set to 10.
- Select Removed unused space from database files; leave default settings alone.
- Click on Change... to setup the schedule for this job (daily, early in the morning), then click
OK, then click Next>.
- Select Check database integrity and make sure Include indexes is selected.
- Select Perform these checks before doing backups.
- Click on Change... to setup the schedule for this job (daily, early in the morning, before the
backup), then click OK, then click Next>.
- Select Back up the database as part of the maintenance plan and Verify the integrity of the backup
when complete.
- Select Disk and then click on Change... to setup the schedule for this job (daily, early in
the morning, after the previous jobs), then click OK, then click Next>.
- Select Use the default backup directory and Create a subdirectory for each database.
- Select Remove files older than and leave the default settings, then click Next>.
- Select Back up the transaction log as part of the maintenance plan.
- Make sure Verify the integrity of the backup when complete is checked, along with Disk.
- Click on Change... to setup the schedule for this job (daily, hourly), then click OK, then
click Next>.
- Select Use the default backup directory and Create a subdirectory for each database.
- Select Remove files older than and leave the default settings, then click Next>.
- Select Write report to a text file in directory and Delete text report files older than.
- Click Next>. Leave the defaults then click Next>.
- Change the name of the plan to Backup System Databases then click Finish.
|
| Backing Up The User Tables |
Now define the scheduled job to handle user databases and tables:
- Right-click on Database Maintenance Plans and choose New Maintenance Plan... to start the
wizard, then click Next>.
- Select All user databases then click Next>.
- Select Reorganize data and index pages; make sure Change free space per page percentage to: is
selected and set to 10.
- Select Removed unused space from database files; leave default settings alone.
- Click on Change... to setup the schedule for this job (daily, early in the morning), then click
OK, then click Next>.
- Select Check database integrity and make sure Include indexes is selected.
- Select Perform these checks before doing backups.
- Click on Change... to setup the schedule for this job (daily, early in the morning, before the
backup), then click OK, then click Next>.
- Select Back up the database as part of the maintenance plan and Verify the integrity of the backup
when complete.
- Select Disk and then click on Change... to setup the schedule for this job (daily, early in
the morning, after the previous jobs), then click OK, then click Next>.
- Select Use the default backup directory and Create a subdirectory for each database.
- Select Remove files older than and leave the default settings, then click Next>.
- Select Back up the transaction log as part of the maintenance plan.
- Make sure Verify the integrity of the backup when complete is checked, along with Disk.
- Click on Change... to setup the schedule for this job (daily, hourly), then click OK, then
click Next>.
- Select Use the default backup directory and Create a subdirectory for each database.
- Select Remove files older than and leave the default settings, then click Next>.
- Select Write report to a text file in directory and Delete text report files older than.
- Click Next>. Leave the defaults then click Next>.
- Change the name of the plan to Backup User Databases then click Finish.
|
| Ad-Hoc Backups |
| If there is a need to actually move a copy of a database to another
place, an ad-hoc backup and restore process can be performed. You can right-click on any database, choose All
Tasks then Backup Database... to create the backup file. Then reverse the process on the target
machine. |
| |
|
|
|
