you're reading...
Database, IT-Center, SQL Server

Automatic Backup SQL Server 2005

Note : don’t login using ‘sa’, please login using windows authentification for creating schedule/maintenance plan

Creating a SQL Server 2005 Maintenance Plan

If you are going to use new Maintenance Plan features, I strongly recommend installing SQL Server 2005 Service Pack 1 first. Among the new features and improvements that are included in SQL Server 2005 Service Pack 1, there is a fix for the previously existing discrepancy between Back up database task, which allowed storing database backups in separate folders, and Maintenance Cleanup Task, which could not delete backup files from those subfolders.

In order to create a new maintenance plan in Management Studio, first connect to the target server using Windows Authentication, then right-click on the Maintenance Plan folder in Object Explorer, select New Maintenance Plan, and enter the plan name. As a result, a Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left. Click on the Connection button to verify that the current connection uses Windows Authentication (recommended by Microsoft). Currently connected Windows user will become the owner of the job created by this maintenance plan.

The first step in creating a database or transaction log backup is to drag and drop Back up database task from the toolbox to the design panel. Then double-click on that item to set the following necessary properties:

Databases: Click on the dropdown field to bring up the database selection window. For this example, I chose Northwind and Pubs as Figure 1 shows:

  • Figure 1: Database selection window.
  • Backup type: choose Full.
  • Destinationparameters:
    • Back up to ā€” choose Disk.Make sure that the Create a backup file for every database option is selected and the Create a sub-directory for each database box is checked.
    • You can use the default destination folder or specify your own. For this example, the network folder \server02dbbackupsql2005 has been selected.
    • Backup file extension ā€” make sure that its value is bak without a leading dot.
    • Check the Verify backup integrity box.

When you are done, the Back up database task properties window should look like the one shown on Figure 2 :

Figure 2: Back up database task properties window.

Create Schedule
Click Schedule […] to set interval backup. Starting your SQL Agent to enable job schedule.


About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 10 other followers

December 2011
« Nov   Jan »


Web Statistic

Blog Stats

  • 160,987 hits
%d bloggers like this: