Scheduling automated backup 2

Sage Pastel Evolution is an Accounting / Stock / Point of Sale program for medium to large sized companies. The program can support a large number of concurrent users and an unlimited number of companies. It uses Microsoft SQL Server as its underlying database. It is very important to take backups for the database files on regular basis.

Backups are security copies of your data. There are a number of strategies to ensure data security. It is vital that you understand all these issues. Making security copies of your data is essential. The database is stored on your hard disk, and the hard disk is subject to hardware failure, physical damage from natural events, and theft. The information you store in your database is worth thousands of times as much as the physical hardware. In fact, your business may fail if you lose this data!

If you are using MS SQL Express edition see automatic backup script for MS SQL Express Edition

In this article, I am going through step by step that will allow

  • Users to schedule the backup to be taken on a particular interval 

Pre-requisite

  • SQL Sever
  • SQL Studio Management
  • SQL Server Agent must be running
  • “SYSADMIN” database role

Using Maintenance plan to schedule database backup

Limitations and Restrictions

  • To create a multiserver maintenance plan, a multiserver environment containing one master server and one or more target servers must be configured. Multiserver maintenance plans must be created and maintained on the master server. These plans can be viewed, but not maintained, on target servers.
  • Members of the db_ssisadmin and dc_admin roles may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages; these packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

Security

Permissions

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.

Login to Sql Management studio and connect to the required database. Now from the object explorer, make sure SQL server agent is running, if not start SQL server agent(Right click and press start).


Expand the Management Node from the object explorer, and then select the maintenance plan node.


Right click the maintenance plan and then select “Maintenance Plan Wizard”.


[nextpage title=”Maintenance Plan Wizard 1″ ]Maintenance Plan Wizard[/nextpage]

Enter the maintenance plan name in the popup box (This can be any name that identifies your task for ). This will identify your backup plan and you should choose a relevant name that suits your plan.



To Schedule, click “Change” button.This will bring the job schedule properties popup window that configure the execution time/frequency of the tasks. Configure the data carefully so that it suits your requirement. Usually database backups are taken daily basis. Make sure you are selecting proper time so that your database is least used. Click ok once you finish.

 


 

Select the Maintenance Task


Define Backup Tasks

  1. On the Define Backup Database (Full) Task page, select the database or databases on which to run a full backup. This task uses the BACKUP DATABASE statement

 

Define backup database task


Backup component

Select Database to back up the entire database. Select File and filegroups to back up only a portion of the database. If selected, provide the file or filegroup name. When multiple databases are selected in the Databases box, only specify Databases for the Backup components. To perform file or filegroup backups, create a task for each database. These options are only available if a single database is chosen from the Databases list above.

Backup set will expire check box

Specifies when the backup set for this backup can be overwritten. Select After and enter a number of days to expiration, or select On and enter a date of expiration.

Back up to

Specifies the medium on which to back up the database. Select either Disk or Tape. Only tape devices attached to the computer containing the database are available.

Back up database(s) across one or more files

Click Add to open the Select Backup Destination dialog box.

Click Remove to remove a file from the box.

Click Contents to read the file header and display the current backup contents of the file.

Select Backup Destination dialog box

Select the file, tape drive, or backup device for the backup destination.

If backup files exist list

Specify how to handle existing backups. Select Append to add the new backups after any existing backups in the file or on the tape. Select Overwrite to remove the old content of a file or tape, and replace it with this new backup.

Create a backup file for every database

Create a backup file in the location specified in the folder box. One file is created for each database selected.

Create a sub-directory for each database check box

Create a sub-directory under the specified disk directory that contains the database backup for each database being backed up as part of the maintenance plan.

 Important
The sub-directory will inherit permissions from the parent directory. Restrict permissions to avoid unauthorized access.

Folder box

Specify the folder to contain the automatically created database files.

Backup file extension box

Specify the extension to use for the backup files. The default is .bak.

Verify backup integrity check box

Verify that the backup set is complete and that all volumes are readable.

[nextpage title=”Select Database” ]Select Database[/nextpage]

Select the database


Databases

Specify the databases affected by this task. When selected, the drop down list provides the following options: All databasesAll system databasesAll user databases,These specific databases.

All databases

Generate a maintenance plan that runs maintenance tasks against all SQL Server databases.

All system databases (master, msdb, model)

Generate a maintenance plan that runs maintenance tasks against each of the SQL Server system databases. No maintenance tasks are run against user-created databases.

All user databases (excluding master, model, msdb, tempdb)

Generate a maintenance plan that runs maintenance tasks against all user-created databases. No maintenance tasks are run against the SQL Server system databases.

These databases

Generate a maintenance plan that runs maintenance tasks against only those databases that are selected. At least one database in the list must be selected if this option is chosen.

 

Click Next

 

Select Report Options


Click Next

Complete the Wizard


Click Finish


You have successfully created a daily automated database backup operation.

One thought on “Scheduling automated backup 2

Leave a Reply