如何在 SQL Server Express 版本中创建作业

谁能解释一下如何在 SQL Server Express 版本中创建作业?

Could anyone please explain to me how to create jobs in SQL Server Express edition?

推荐答案

SQL Server Express 没有包括 SQL Server 代理,因此不能只创建 SQL 代理作业.

SQL Server Express doesn't include SQL Server Agent, so it's not possible to just create SQL Agent jobs.

你可以做的是:
您可以手动"创建工作;通过创建批处理文件和 SQL 脚本文件,并通过 Windows 任务计划程序运行它们.
例如,您可以使用以下两个文件备份数据库:

What you can do is:
You can create jobs "manually" by creating batch files and SQL script files, and running them via Windows Task Scheduler.
For example, you can backup your database with two files like this:

backup.bat:

sqlcmd -i backup.sql

backup.sql:

backup database TeamCity to disk = 'c:ackupsMyBackup.bak'

只需将两个文件放在同一个文件夹中,然后通过 Windows 任务计划程序执行批处理文件.

Just put both files into the same folder and exeute the batch file via Windows Task Scheduler.

第一个文件只是一个 Windows 批处理文件,它调用 sqlcmd 实用程序 并传递一个 SQL 脚本文件.
SQL 脚本文件包含 T-SQL.在我的示例中,备份数据库只需一行,但您可以将任何 T-SQL 放入其中.例如,您可以改为执行一些 UPDATE 查询.

The first file is just a Windows batch file which calls the sqlcmd utility and passes a SQL script file.
The SQL script file contains T-SQL. In my example, it's just one line to backup a database, but you can put any T-SQL inside. For example, you could do some UPDATE queries instead.

如果您要创建的作业用于备份、索引维护或完整性检查,您还可以使用出色的 维护解决方案 由 Ola Hallengren 撰写.

If the jobs you want to create are for backups, index maintenance or integrity checks, you could also use the excellent Maintenance Solution by Ola Hallengren.

它由一堆存储过程(以及 SQL Server 的非 Express 版本的 SQL 代理作业)组成,并且位于 FAQ 有一节介绍如何在 SQL Server Express 上运行作业:

It consists of a bunch of stored procedures (and SQL Agent jobs for non-Express editions of SQL Server), and in the FAQ there’s a section about how to run the jobs on SQL Server Express:

SQL Server Express 没有 SQL Server 代理.因此,必须使用 cmd 文件和 Windows 计划任务来计划存储过程的执行.请按照以下步骤操作.

How do I get started with the SQL Server Maintenance Solution on SQL Server Express?

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

SQL Server Express 没有 SQL Server 代理.因此,执行必须使用 cmd 文件和Windows 计划任务.请按照以下步骤操作.

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

  1. 下载 MaintenanceSolution.sql.

  1. Download MaintenanceSolution.sql.

执行 MaintenanceSolution.sql.此脚本创建您需要的存储过程.

Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.

创建cmd文件来执行存储过程;例如:
sqlcmd -E -S .SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory =N'C:Backup', @BackupType = 'FULL'"-b -o C:LogDatabaseBackup.txt

Create cmd files to execute the stored procedures; for example:
sqlcmd -E -S .SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:Backup', @BackupType = 'FULL'" -b -o C:LogDatabaseBackup.txt

在 Windows 计划任务中,创建任务以调用 cmd 文件.

In Windows Scheduled Tasks, create tasks to call the cmd files.

安排任务.

启动任务并验证它们是否成功完成.

Start the tasks and verify that they are completing successfully.

相关文章