How do I schedule automated backups in SQL 2008 (not Express version)?

Netwrix Auditor SQL Databases
6.5 and older
Copy Article URL Copied

The steps below will allow you:

to schedule backup to be created at a particular interval
to delete backed up copies after a certain period of time
To schedule a database backup:
1. Log in to the SQL Server Management Studio and connect to the appropriate database engine.
2. From the Object Explorer , verify the SQL Server Agent is running. If it isn’t started, then right-click it and select  Start .

3. Expand the Management node from the Object Explorer , and then select the Maintenance Plans node. To schedule a maintenance plan, you need to granted “SYSADMIN” database role. If you do not see the Maintenance Plans node, make sure you have the necessary permission.

4. Right click the Maintenance Plans and then select New Maintenance Plan .

5. Enter the maintenance plan name in the popup box. This created object will identify your backup plan and you should choose a relevant name that suits your plan.

6. Specify maintenance plan options in the configuration page. Make sure to set maintenance plan options in both areas marked on the image (encircled with red).

The settings in the top right area in the top right corner are used to configure the plan’s execution schedule. It is recommentded to specify the time when the databases is least used.
The tasks  in the bottom left pane can be utilized to create an SQL maintenance plan.

7. Click on the Calendar shown in the right  top side. It invokes the Job Schedule Properties popup window that allows configuring the execution time/frequency of the tasks. Specify settings carefully, make sure they your requirements. Usually database backups are created on daily basis. Make sure you are selecting proper time so that your database is least used.

8. Select Back Up DatabaseTask  from the Maintenance Plan Tasks pane. Drag and drop a backup database task to the pane on the right (as shown in the image).

9. Double-click on the Back Up Database Task to configure backup properties. Here you specify the databases that you want to backup, backup location and extension for the backup files, etc.

10. Click OK once finished. Now the backup plan configuration is done. The backup files will be created on the scheduled time and save to the specified folder. The backup file’s name is created by appending the backup date to the database name so that you can easily identify the backup for a particular date.

To delete backup files after a certain period of time
You need to execute clean up task  along with the maintenance plan. You can configure the clean up task as follows:

1. Log in to the  SQL Server Management Studio  and connect to the appropriate database engine.
2. Navigate to  Maintenance Plans node in the Object Explorer , select   Maintenance Clean Up task from the Maintenance Plans Tasks toolbox and drag and drop it to the pane on the right.

3. Double-click on the Maintenance Cleanup Task t o configure cleanup properties. Specify backup location, file extension for the back up files and set the age of the file. It is a good practice to keep backups for a month and to remove backups captured before.

4. Click OK  to save the maintenance plan. You can either wait till the next execution time or execute it manually in order to check whether everything is working fine

Go Up