Microsoft provides us with a free SQL Server (for example, the 2005 or 2008 editions). However, this server, of course, has some limitations. Among them is the absence, in the Standard edition and above, of a built-in agent (SQL Server Agent), which runs scheduled jobs on a schedule.
So how do we create automated tasks now?
Still, all is not lost. Here I'll show you how to work around this obstacle.
1) Create an SQL file with a set of T-SQL commands to run.
Here's an example file for backing up 2 databases to the hard drive (this is just an example):
mybackup.sql
BACKUP DATABASE [mybase1] TO DISK = N'c:\sqlbackup\mybase1.bak' WITH INIT, NOUNLOAD, NAME = N'MyBase1 Backup', NOSKIP, STATS = 10, NOFORMAT;
BACKUP DATABASE [mybase2] TO DISK = N'c:\sqlbackup\mybase2.bak' WITH INIT, NOUNLOAD, NAME = N'MyBase2 Backup', NOSKIP, STATS = 10, NOFORMAT;
Naturally, besides backups you can use any other Transact-SQL commands.
2) Now let's create a task in the Windows Task Scheduler.
That is, yes, we open the regular Windows scheduler (which you can also use to create ordinary scheduled tasks, for example, launching some programs).
Windows 2003:
Start -> Control Panel -> Scheduled TasksWindows 2008:
Start -> Administrative Tools -> Task SchedulerAnd we add a new task. Name the task whatever you like. For the run parameters (triggers), specify the ones you would specify for SQL Agent. For example, run every day at hour X, or however you need it.
In the execution line (i.e., which program to launch or which command to run), enter it in this format:
sqlcmd -S 192.168.0.1\SQLEXPRESS -U sa -P mysuperpassword -i c:\path_to_script\mybackup.sql -o c:\path_to_script\mybackup.log
Here:
- -S 192.168.0.1\SQLEXPRESS : here specify the IP address of the server running SQL Express, and the instance name of that server. For a default installation, the instance name for an SQL Express server is SQLEXPRESS (unlike a Standard installation, where the default instance name is empty).
- -U sa : the username under which to run the script. Instead of "sa" you can (and should) specify the name of a user who has rights to perform the operations listed in mybackup.sql or another SQL script file. So, for example, for backups you can create a separate user on MS SQL Server who only has rights to do backups and nothing else.
- -P mysuperpassword : this user's password
- -i c:\... : the path and name of the file containing the set of T-SQL commands (mybackup.sql in our case)
- -o c:\... : the path and name of the file where the messages (logs) for this operation should be written
And that's it. Now save the Windows Scheduler task and test it. It will run just like any other ordinary task, while executing the set of T-SQL commands instead of SQL Agent.
Less convenient, of course, than writing it all directly in the SQL Server snap-in, but free.Applicable to: MS SQL Express 2005, 2008
Comments