Daily Backup Configuration in SQL Server 2008

As we are aware of how backup is more important in day to day life, please consider following methodologies to backup SQL server 2008 databases through Microsoft SQL Server management studio.

We can backup database with 2 methods as below:

Method 1: Individual backup:
We can take individual database backup by following steps:

Step 1: connect to Microsoft SQL Server management studio.

Step 2: Expand databases & Select database to be backup.

Step 3: Right click -> Takes -> Backup Up

Step 4: Provide relevant information like which type of database backup you want to take, compression backup, etc…

Step 5: After providing all information, click ok to take backup of that database.

Please find screen shot below:

SQL Server Manual Backup

SQL Server Manual Backup

 

By above method we can take backup of individual databases.

 

Method 2: Backup through Maintenance plan:

What if you have 100 databases and you want to backup all in one go, then SQL Server Maintenance plan is very easy and comfortable option to do so:

Step 1: Connect to Microsoft SQL Server management studio.

Step 2: Expand Management.

Step 3: Select Maintenance plan -> Right click -> New Maintenance plan. Please find screen shot below:

SQL Server New Maintenance Plan

SQL Server New Maintenance Plan

 

Step 4: Provide name of the maintenance plan ( Database backup ) and click next.

Step 5: Drag “Backup Up Database Task” from toolbox situated on left panel of management studio. Please find screen shot below:

SQL Server Backup Database Maintenance Plan

SQL Server Backup Database Maintenance Plan

 

Step 6: Double click on “Backup Up Database Task” to set database backup parameters.

Step 7: “On Backup Up Database Task” window, provide backup relevant information, like:

  • Backup types: You can select Full, Differential & Transaction log according to your convenience.
  • Select Database(s): You can select “All databases”, “System databases”, “All user databases” & select according to choice.
  • On database selection window, don’t forget to select check box “Ignore databases where the state is not online” because backup won’t failed if one of the selected databases is offline.
  • Click Ok.
  • Please find screen shot below:
SQL Server Backup Database Task Wizard

SQL Server Backup Database Task Wizard

 

Step 8: Provide database backup location & backup extension as .bak ( default )

Step 9: Select backup compression option in case of you want backup in compressed mode, this will save your disk space.

Step 10: Click Ok & save maintenance configuration.

 

Step 11: We can run backup as manual by executing “Database backup” job. ( SQL Server Agent -> Jobs -> Database backup.Full -> Right click -> Start Job at Step )

SQL Server - Start Job at Step

SQL Server – Start Job at Step

 

OR

We can schedule it by Maintenance “Job Scheduler”. Please find screen shot below:

SQL Server - New Job Schedule

SQL Server – New Job Schedule

 

Click Ok.

In above screen shot, we are scheduling database backup task every day at 10pm.

By above steps mentioned in both the methods, we can backup SQL server 2008 databases.

************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply