Automatically Backup An SQL Server 2005 Database

There are certain ways of creating a backup routine for SQL Server 2005 Management Studio (SSMS) and one of them is by making a Maintenance Plan.

The beauty of building a task for backup purposes under MP is that it can automate the job for you via the SQL Server Agent Service.

Using the SSMS, you don't need even a single code to achieve that. Things can be as easy as drag and drop literally. To begin with, let's open the SQL Server Management Studio and log in to an instance.


In my case, I have an SQL Server instance named GMERSS running under the user 'sa.' If you have log-in successfully with your own user designation, click on the Management. Right-click on Maintenance Plans and select New Maintenance Plan.

Change the name of our plan to "Back Up." You can also change it to whatever you like. Hit OK after.

At the lower left of the SSMS screen, you will see the Toolbox for Maintenance Plan Tasks. Select Back Up Database Task and drag it to the empty space at the right side of the screen.

After dragging it up to the right, you will see this object below.

Right-click over this object and select Edit. This will bring you to the "Back Up Database Task" window. On the top right, click on the "New" button. This will open up the "New Connection" window; click on the button right next to "Select or enter a server name."

Choose a server and click OK. Your server name will appear on the textbox. Choose "Use a specific user name and password:" and type in the user and password below that. Type the name of the connection in the "Connection Name" textbox. In my case, I will type "MyConnection." Click OK button when finished.

At the "Databases:" part, click on the listbox to select the database that you want to back up. In My case, I will choose "Test2" as that is the database I created for this tutorial. To select, just tick the checkbox alongside the database name then click OK.

The image below (Back Up Database Task) is pretty much done. If you have followed the instruction correctly, you may have a screen similar to this one. Notice that in the "Folder" section (bottom part), you can see that I changed the destination folder of the back up file to drive D:\. You can select your own destination folder by clicking on the button on the right side of it. You may want to tick the "Verify backup integrity" to make sure you will have a working backup. Click on the OK button if to finish this up.

We're not done here yet. We will need to specify a schedule to which this plan will run upon. On the top-right part of the SSMS, click on the small button to open the "Job Schedule Properties - Back Up - Schedule" window. (See the button in the image below)

In the "Job Schedule Properties" window, leave the default Name as is. Leave Schedule type as "Recurring." Under the "Frequency" section, choose "Daily" for the occurrence. Under the "Daily Frequency" choose "Occurs once at:" and choose your preferred time of the plan execution. For this example, I set it to 12:01 PM. Click on the OK button when done. Yours should be similar to this (see the image below) when followed correctly.

Next, click on the "File" menu and select Close. It will prompt you to save your Maintenance Plan, click "Yes."

There you go, this is how easy it is to make an automated daily back up routine for SQL Server 2005. If you know another routine for making back up file for database, please share it here. Have fun.