Schedule daily backup for sql server 2005 database using sql agent

Many of us come across a need to schedule periodic database backups for sql server. But many of us don't realize that this feature is readily available in sql server management studio and we purchase some 3rd party tools to create such scheduled tasks. I recommend to use Sql Server Management Studio to do such jobs rather than spending on unnecessary tools. But remember, this feature is not available in express releases of sql server. If you are using sql server express edition please ignore this post. And this post also assumes that you have SSIS (Sql Server Integration Services) installed and running on your machine. SSIS is the prerequisite to follow the below instructions to schedule the database periodic backups.
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.

1. Open sql server 2005 Management Studio and connect a server
2. Expand the tree (left navigation) ”Management->Maintenance Plans”
3. Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.

4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following

6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.

7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.
8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.

9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.

10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish step.
12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.
13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).

The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

Every time I install sql server, in the initial days I used to face problems in logging on by sql server authentication mode.

These were the steps I used to follow to create new login in order to logon using sql server authentication mode.

1. Log on to the server using windows authentication mode and create some login in the section of Sqlserver->Security->Logins->(Right click)NewLogin.

2. Restart the sql server and disconnect it.

3. This time connect to the server using the sql server authentication mode by entering new login credentials.

But it never used to work. The error I receive was “The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)”.

After doing a long research on this I found the solution to this. To make sql server authentication work we need to configure the sql server to use Sql server and windows authentication mode. The following steps will solve the problem.

1. Right click on the root node of the sql server and select properties in the context menu.

2. That will open a popup window which will have server level settings.

3. Click on security tab it will show the view as in the following figure. There you have to make sure you have “Sql server and Windows authentication mode” is selected.

4. Press OK and restart the sql server again.

5. This time the sql server authentication mode using the new login credential will work.

Note: You have to make sure your login has server role as “SysAdmin” in order to logon.