Add a SQL Server Database Backup Job

Previous  Top  Next

Note: SQL Server 2005 Express Edition does not include the SQL Server Agent, therefore you will not be able to schedule a backup this way.

For SQL Server 2005

1.	Open the SQL Server Management Studio and connect to the SQL Server that houses the RMS database.

2.	Right click on the SQL Server Agent icon and click New Job.

3.	On the General page, type in the Name of the job.

4.	Select the Steps page and click the New button.

5.	Enter a name for the step in the Step name text box; the Type is Transact-SQL script (T-SQL); select the master database in the Database text box; paste the following command into the Command text box:

BACKUP DATABASE RMSDBSQL TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DatabaseBackup.bak'  WITH INIT

Note: Your RMS database may be named something other than RMSDBSQL; if so, use it in the command instead. If you wish to place the backup in a different folder, you must ensure that SQL Server has permissions to write files to that folder.

6.

Click OK.

7.	Select the Schedules page and click New.

8.

Type in a name for the schedule; the Schedule type is Recurring; make sure the Enabled check box is checked; under Frequency select Occurs to be Daily and Recurs every 1 day(s); under Daily frequency select Occurs once at and select the time you wish the backup to occur (it is recommended to choose a time when few users are connected to the database and before the network tape backup occurs); under Duration, select the current date as the Start date and select No end date; click OK.

9.	You may wish to add an email Alert notifying you via email when the job fails and/or completes. Do this on the Alert page.

This is a very basic backup procedure where the there is only a single backup file that is overwritten each day. It is best that you couple this with a daily tape backup of this file and keep the last 5 to 7 days of daily backup tapes.

For SQL Server 2000

1.	Open the Enterprise Manager and connect to the SQL Server that houses the RMS database.

2.	Expand the Management folder and right click on the SQL Server Agent icon and click New Job.

3.	On the General tab, type in the Name of the job.

4.	Select the Steps tab and click the New button.

5.	Enter a name for the step in the Step name text box; the Type is Transact-SQL script (T-SQL); select the master database in the Database text box; paste the following command into the Command text box:

BACKUP DATABASE RMSDBSQL TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\DatabaseBackup.bak'  WITH INIT

Note: Your RMS database may be named something other than RMSDBSQL; if so, use it in the command instead. The folder path may vary, but it is recommended to place the backup in the Backup folder. If you wish to place the backup in a different folder, you must ensure that SQL Server has permissions to write files to that folder.

6.

Click OK.

7.	Select the Schedules tab and click New Schedule.

8.

Type in a name for the schedule; the Schedule type is Recurring; make sure the Enabled check box is checked; click the Change button, select Occurs to be Daily and every 1 day(s); under Daily frequency select Occurs once at and select the time you wish the backup to occur (it is recommended to choose a time when few users are connected to the database and before the network tape backup occurs); under Duration, select the current date as the Start date and select No end date; click OK.

9.	You may wish to add a Notification telling you via email when the job fails and/or completes. Do this on the Notification tab.