One drawback with SQL Server 2005 Express Edition is that it does not offer a way to schedule jobs. A database backup in SQL2005 Express Edition is scheduled in both the operating system and SQL Server using the tools.
To schedule a backup of a database:
Steps :
1. Create a folder ‘BackUp’ in D drive, “D:\Backup“. ( This can be changed to any folder you like.)
2. Go to Start>>Programs >>Microsoft Server 2005>>SQL Server Management Studio, Click SQL Server Management Studio. Microsoft Server Management Studio window with Connect to Server dialog box appears (see Figure 1).
3. Enter a password of the server in Password field.
4. Click Connect. It connects to the database server and Microsoft Server Management Studio window refreshes (see Figure 2).
5. Expand Databases sub folder (see Figure 3).
6. Expand ‘master’ sub folder.
7. Click New Query. A new query file opens ( Figure 4 )
USE [master] GO /****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 26/11/2010 11:40:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
|
8. Click to execute the query. It saves the query in the name highlighted in red (see Figure 5) in Stored Procedures sub folder.
9. Click New Query. A new query file opens.
10. Type the following query in the new query file.
| sp_BackupDatabase ‘readydesk’, ‘F’ GO sp_BackupDatabase ‘abacus’, ‘F’ GO QUIT |
11. Place the cursor at the top of the query.
12. Right-click the mouse. A shortcut menu appears (see Figure 6).
13. Save the file as backup.sql and for our purposes this is created in the “D:\Backup” folder, but again this could be put anywhere.
14. Goto Start>>Programs >>Accessories>>System Tools>>Scheduled Tasks, click Scheduled Tasks. Scheduled Tasks window appears (see Figure 7).
15. Click ‘Add Scheduled Task’. Scheduled Task Wizard screen appears (see Figure 8).
16. Click Next. Scheduled Task Wizard screen refreshes (see Figure 9).
17. Click ‘Browse’ to SQLCMD.EXE. Select Program to Schedule screen appears (see Figure 10).
18. Browse to select ‘SQLCMD.EXE’ in the programs.
“C:\Program Files\Microsoft SQL Server\90\Tools\Binn”
19. Click Open. It adds the selected program to the Programs list.
20. Click Next. Scheduled Task Wizard screen refreshes (see Figure 11).
21. Type the name of the task in Task field.
22. Click ‘Daily’.
23. Click Next. Scheduled Task Wizard screen refreshes (see Figure 12).
24. Specify the time to run the task.
25. Click Next. Scheduled Task Wizard screen refreshes (see Figure 13).
26. Type the credentials of the account such as user name, password and confirm password in respective fields.
27. Click Next. Scheduled Task Wizard screen refreshes (see Figure 14).
28. Check ‘Open advanced properties for this task when U click Finish’
29. Click. Finish. Database Backups window appears (see Figure 15).
30. Type the command ‘sqlcmd -S serverName -E -i D:\Backup\Backup.sql’ in Run field.
Note: SQL query is denoted as follows:
o Sqlcmd
o –S: It defines the server\instance name for SQL Server.
o serverName: It specifies the server\instance name for SQL Server. For instance, WS076\SQLEXPRESS\
o –E: It allows you to make a trusted connection.
o –i: It states this specifies the input command file.
o D:\Backup\Backup.sql: It specifies the path of the backup.sql file.
31. Enter the user name in Run as field.
32. Click Apply.
33. Click OK. The database backup is scheduled.
34. Select ‘Database Backups’ scheduled task in Scheduled Tasks window.
35. Right-click the mouse. A short-cut menu appears (see Figure 16).
36. Click Run. It runs the task at the scheduled time.
lakkireddymadhu 
Good day I was luck to search your theme in yahoo
your subject is fine
I learn a lot in your blog really thanks very much
btw the theme of you blog is really excellent
where can find it
Thank you very much. Sorry for the late reply, your comments blocked as a spam, now only I checked it.
Once again Thank you. Albeo theme
Great post
Thank you very much sir.