Scheduling backup and maintenance for SQL Server Standard and Enterprise

The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, run database integrity checks, or update database statistics at specified intervals.

To start the Maintenance Plan Wizard in SQL Server Management Studio:

Step 1 Start SQL Management Studio.

Step 2 Make sure that the SQL Server Agent is running. In Management Studio connect to a component of SQL Server.In Object Explorer, right-click on SQL Server Agent and select Start..

Step 3 Expand the server.

Step 4 Expand the Management folder.

Step 5 Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.

Configuring a maintenance plan including daily full database backup for the Verba database

To configure a maintenance plan including daily full database backup for the Verba database follow these steps:

Step 1 Enter a name for the maintenance plan in the Name text box.

Step 2 Optionally, in the Description text box, define a description for the maintenance plan.

Step 3 Select Separate schedule for each task option and click the Next button.

Step 4 In the Select Maintenance Task window, check in the Back Up Database (Full) option and click Next.

Step 5 In the Select Maintenance Task Order window, click Next.

Step 6 In the Define Back Up Database (Full) Task window, select Verba database in the Database(s) selection list.

Step 7 Check in the Backup set will expire option and set it to After 4 days. This setting will allow to have the last 4 full database backup while older backup sets will be automatically deleted after 4 days.

Step 8 Set the Back up to option according to your needs.

• If you choose to back up the database to Disk then select the Create a backup file for every database option and set the Folder to a directory, where you would like to store the backup sets (it is highly recommended to move the backup sets to another server/network drive).

• If you choose to back up the database to Tape then select the Back up databases across one or more files option. Click on the Add button and select the backup file location on your tape then click OK. Set the If backup file exist option to Append.

Step 9 Check in the Verify backup integrity checkbox

Step 10 At the Set backup compression option, select Compress backup.

Step 11 Press the Change button at the Schedule section to open the Job Scheduler Properties window.

Step 12 In the Job Schedule Properties window set the Schedule type to Recurring.

Step 13 In the Frequency pane, set the Occurs attribute to Daily and select the Occurs once at option and set the time of the execution to an appropriate time, when the load in the Verba system is very low.

Step 14 Set the Start date optionally and select the No end date option in the Duration pane.

Step 15 Click the OK button.

Step 16 Back in the Maintenance Plan Wizard window, click Next.

Step 17 In the Select Report Options window select the Write a report to a text file option and define the Folder location, where the log files will be created. Click the Next button.

Step 18 In the Complete the Wizard window review your settings and click Finish or press the < Back button to modify something.

Step 19 In the Maintenance Plan Wizard Progress window wait until the SQL server finishes the listed actions.

Configuring a maintenance plan including daily full database backup and hourly transactional log backup for Verba database

In order to set transaction log backup the database recovery mode has to be set to full. To set the recovery model to full, refer to Setting the database recovery model to full by SQL Server Management Studio or SQL Server Management Studio Express.

The first transactional log backup can only be created, if at least one full database backup exists. To create a full database backup, refer to Creating a one-off full database backup. Alternatively, according to your full database backup scheduling settings, you can wait for the first full database backup creation (until that, the transactional log backups will not be created).

To configure a maintenance plan including daily full database backup and hourly transactional log backup for Verba database:

Step 1 Enter a name for the maintenance plan in the Name text box.

Step 2 Optionally, in the Description text box, define a description for the maintenance plan.

Step 3 Select Separate schedule for each task option and click the Next button.

Step 4 In the Select Maintenance Task window, check in the Back Up Database (Full) and the Back Up Database (Transactional Log) options and click Next.

Step 5 In the Select Maintenance Task Order window, click Next.

Step 6 In the Define Back Up Database (Full) Task window, select verba database in the Database(s) selection list.

Step 7 Check in the Backup set will expire option and set it to After 4 days. This setting will allow to have the last 4 full database backup while older backup sets will be automatically deleted after 4 days.

Step 8 Set the Back up to option according to your needs.

• If you choose to back up the database to Disk then select the Create a backup file for every database option and set the Folder to a directory, where you would like to store the backup sets (it is highly recommended to move the backup sets to another server/network drive).
• If you choose to back up the database to Tape then select the Back up databases across one or more files option. Click on the Add button and select the backup file location on your tape then click OK. Set the If backup file exist option to Append.

Step 9 Check in the Verify backup integrity checkbox

Step 10 At the Set backup compression option, select Compress backup.

Step 11 Press the Change button at the Schedule section to open the Job Scheduler Properties window.

Step 12 In the Job Schedule Properties window set the Schedule type to Recurring.

Step 13 In the Frequency pane, set the Occurs attribute to Daily and select the Occurs once at option and set the time of the execution to an appropriate time, when the load on the Verba system is very low.

Step 14 Set the Start date optionally and select the No end date option in the Duration pane.

Step 15 Click the OK button.

Step 16 Back in the Maintenance Plan Wizard window, click Next.

Step 17 In the Define Back Up Database (Transactional Log) Task window, select verba database in the Database(s) selection list.

Step 18 Check in the Backup set will expire option and set it to After 1 day. This setting will allow to have the last transactional log backups for the last day while older backup sets will be automatically deleted after 1 day. Having the daily full backups, there is no need to store the transactional log backups longer.

Step 19 Set the Back up to option according to your needs.

• If you choose to back up the database to Disk then select the Create a backup file for every database option and set the Folder to a directory, where you would like to store the backup sets (it is highly recommended to move the backup sets to another server/network drive).
• If you choose to back up the database to Tape then select the Back up databases across one or more files option. Click on the Add button and select the backup file location on your tape then click OK. Set the If backup file exist option to Append.

Step 20 Check in the Verify backup integrity checkbox

Step 21 At the Set backup compression option, select Compress backup.

Step 22 Press the Change button at the Schedule section to open the Job Scheduler Properties window.

Step 23 In the Job Schedule Properties window set the Schedule type to Recurring.

Step 24 In the Frequency pane, set the Occurs attribute to Daily and select the Occurs every option and set it to 1 hour. This setting will allow the system to create a transaction log backup at every hour.

Step 25 Set the Start date optionally and select the No end date option in the Duration pane.

Step 26 Click the OK button.

Step 27 Back in the Maintenance Plan Wizard window, click Next.

Step 28 In the Select Report Options window select the Write a report to a text file option and define the Folder location, where the log files will be created. Click the Next button.

Step 29 In the Complete the Wizard window review your settings and click Finish or press the < Back button to modify something.

Step 30 In the Maintenance Plan Wizard Progress window wait until the SQL server finishes the listed actions.