This article will show you how to create a SQL maintenance plan to backup all the MSSQL database(s) on your dedicated server.
With the creation of a SQL maintenance plan, your databases will be backed up daily.
Follow these steps to create a SQL backup plan for MSSQL 2000:
Enterprise Manager1. From your dedicated server, open up SQL Server Enterprise Manager (Start -> All Programs -> Microsoft SQL Server -> Enterprise Manager).
2. Login with Windows authentication.
Management3. Expand the Local Windows database server and double click on 'Management.'
4. Verify that the SQL Server Agent is turned on (right click on SQL Server Agent and click on Start).
5. Right click on Database Maintenance Plans and click 'New Maintenance Plan...'
Wizard6. Choose the All databases radio button
7. Click next on 'Update Data Optimization Information' and 'Database Integrity Check.'
Specify the Database Backup Plan
8. Make sure the 'Back up the database as part of the maintenance plan' and 'Verify the integrity…' box are checked. Leave the radio button marked on Disk.
9. Click on the 'Change…' that is at the bottom right.
Job Schedule10. Under occurs, choose Daily, under Daily leave it at 1 day(s), under Daily Frequency choose Occurs once at 10PM (can be changed to what you want), leave the Duration at No end date. Click OK and click Next.
Specify Backup Disk Directory11. Choose the location of where you want your databases backed up to (make sure to create a folder) or you can 'Use the default backup directory'. Mark the 'Remove files older than:' and set it to 1 Week(s). Leave the backup file extension as BAK.
Finish12. Click next on 'Specify the Transaction Log Backup Plan', 'Reports to Generate', 'Maintenance Plan History', change the Plan name to DB backups and click on Finish.
This will create a SQL Maintenance plan to backup your MSSQL 2000 database(s).