blocks|key|1640028|text|不能在SQL+Server+Express中使用SQL+Server代理。我以前的方法是创建一个SQL脚本，然后每天将其作为计划任务运行，您可以有多个计划任务来适应您的备份计划/保留。我在定时任务中使用的命令是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1640029|"C:\Program+Files\Microsoft+SQL+Server\90\Tools\Binn\SQLCMD.EXE“-i"c:\path\to\sqlbackupScript.sql”|1640030|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You cannot use the SQL Server agent in SQL Server Express.
The way I have done it before is to create a SQL Script, and then run it as a scheduled task each day, you could have multiple scheduled tasks to fit in with your backup schedule/retention. The command I use in the scheduled task is:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

blocks|key|1425097|text|只需使用this脚本动态备份服务器上的所有数据库即可。然后根据文章创建批处理文件。创建两个批处理文件非常有用，一个用于完全备份，另一个用于差异备份。然后在Task+Scheduler中创建两个任务，一个用于full，另一个用于diff。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1425098|--+//+Copyright+©+Microsoft+Corporation.++All+Rights+Reserved.
--+//+This+code+released+under+the+terms+of+the
--+//+Microsoft+Public+License+(MS-PL,+http://opensource.org/licenses/ms-pl.html.)
USE+[master]+
GO+
/******+Object:++StoredProcedure+[dbo].[sp_BackupDatabases]+******/+
SET+ANSI_NULLS+ON+
GO+
SET+QUOTED_IDENTIFIER+ON+
GO+

--+=============================================+
--+Author:+Microsoft+
--+Create+date:+2010-02-06
--+Description:+Backup+Databases+for+SQLExpress
--+Parameter1:+databaseName+
--+Parameter2:+backupType+F=full,+D=differential,+L=log
--+Parameter3:+backup+file+location
--+=============================================

CREATE+PROCEDURE+[dbo].[sp_BackupDatabases]++
++++++++++++@databaseName+sysname+=+null,
++++++++++++@backupType+CHAR(1),
++++++++++++@backupLocation+nvarchar(200)+
AS+

+++++++SET+NOCOUNT+ON;+

++++++++++++DECLARE+@DBs+TABLE
++++++++++++(
++++++++++++++++++ID+int+IDENTITY+PRIMARY+KEY,
++++++++++++++++++DBNAME+nvarchar(500)
++++++++++++)

+++++++++++++--+Pick+out+only+databases+which+are+online+in+case+ALL+databases+are+chosen+to+be+backed+up
+++++++++++++--+If+specific+database+is+chosen+to+be+backed+up+only+pick+that+out+from+@DBs
++++++++++++INSERT+INTO+@DBs+(DBNAME)
++++++++++++SELECT+Name+FROM+master.sys.databases
++++++++++++where+state=0
++++++++++++AND+name=@DatabaseName
++++++++++++OR+@DatabaseName+IS+NULL
++++++++++++ORDER+BY+Name

++++++++++++--+Filter+out+databases+which+do+not+need+to+backed+up
++++++++++++IF+@backupType='F'
++++++++++++++++++BEGIN
++++++++++++++++++DELETE+@DBs+where+DBNAME+IN+('tempdb','Northwind','pubs','AdventureWorks')
++++++++++++++++++END
++++++++++++ELSE+IF+@backupType='D'
++++++++++++++++++BEGIN
++++++++++++++++++DELETE+@DBs+where+DBNAME+IN+('tempdb','Northwind','pubs','master','AdventureWorks')
++++++++++++++++++END
++++++++++++ELSE+IF+@backupType='L'
++++++++++++++++++BEGIN
++++++++++++++++++DELETE+@DBs+where+DBNAME+IN+('tempdb','Northwind','pubs','master','AdventureWorks')
++++++++++++++++++END
++++++++++++ELSE
++++++++++++++++++BEGIN
++++++++++++++++++RETURN
++++++++++++++++++END

++++++++++++--+Declare+variables
++++++++++++DECLARE+@BackupName+varchar(100)
++++++++++++DECLARE+@BackupFile+varchar(100)
++++++++++++DECLARE+@DBNAME+varchar(300)
++++++++++++DECLARE+@sqlCommand+NVARCHAR(1000)+
++++++++DECLARE+@dateTime+NVARCHAR(20)
++++++++++++DECLARE+@Loop+int++++++++++++++++++

++++++++++++--+Loop+through+the+databases+one+by+one
++++++++++++SELECT+@Loop+=+min(ID)+FROM+@DBs

++++++WHILE+@Loop+IS+NOT+NULL
++++++BEGIN

--+Database+Names+have+to+be+in+[dbname]+format+since+some+have+-+or+_+in+their+name
++++++SET+@DBNAME+=+'['%2B(SELECT+DBNAME+FROM+@DBs+WHERE+ID+=+@Loop)%2B']'

--+Set+the+current+date+and+time+n+yyyyhhmmss+format
++++++SET+@dateTime+=+REPLACE(CONVERT(VARCHAR,+GETDATE(),101),'/','')+%2B+'_'+%2B++REPLACE(CONVERT(VARCHAR,+GETDATE(),108),':','')++

--+Create+backup+filename+in+path\filename.extension+format+for+full,diff+and+log+backups
++++++IF+@backupType+=+'F'
++++++++++++SET+@BackupFile+=+@backupLocation%2BREPLACE(REPLACE(@DBNAME,+'[',''),']','')%2B+'_FULL_'%2B+@dateTime%2B+'.BAK'
++++++ELSE+IF+@backupType+=+'D'
++++++++++++SET+@BackupFile+=+@backupLocation%2BREPLACE(REPLACE(@DBNAME,+'[',''),']','')%2B+'_DIFF_'%2B+@dateTime%2B+'.BAK'
++++++ELSE+IF+@backupType+=+'L'
++++++++++++SET+@BackupFile+=+@backupLocation%2BREPLACE(REPLACE(@DBNAME,+'[',''),']','')%2B+'_LOG_'%2B+@dateTime%2B+'.TRN'

--+Provide+the+backup+a+name+for+storing+in+the+media
++++++IF+@backupType+=+'F'
++++++++++++SET+@BackupName+=+REPLACE(REPLACE(@DBNAME,'[',''),']','')+%2B'+full+backup+for+'%2B+@dateTime
++++++IF+@backupType+=+'D'
++++++++++++SET+@BackupName+=+REPLACE(REPLACE(@DBNAME,'[',''),']','')+%2B'+differential+backup+for+'%2B+@dateTime
++++++IF+@backupType+=+'L'
++++++++++++SET+@BackupName+=+REPLACE(REPLACE(@DBNAME,'[',''),']','')+%2B'+log+backup+for+'%2B+@dateTime

--+Generate+the+dynamic+SQL+command+to+be+executed

+++++++IF+@backupType+=+'F'+
++++++++++++++++++BEGIN
+++++++++++++++SET+@sqlCommand+=+'BACKUP+DATABASE+'+%2B@DBNAME%2B++'+TO+DISK+=+'''%2B@BackupFile%2B+'''+WITH+INIT,+NAME=+'''+%2B@BackupName%2B''',+NOSKIP,+NOFORMAT'
++++++++++++++++++END
+++++++IF+@backupType+=+'D'
++++++++++++++++++BEGIN
+++++++++++++++SET+@sqlCommand+=+'BACKUP+DATABASE+'+%2B@DBNAME%2B++'+TO+DISK+=+'''%2B@BackupFile%2B+'''+WITH+DIFFERENTIAL,+INIT,+NAME=+'''+%2B@BackupName%2B''',+NOSKIP,+NOFORMAT'++++++++
++++++++++++++++++END
+++++++IF+@backupType+=+'L'+
++++++++++++++++++BEGIN
+++++++++++++++SET+@sqlCommand+=+'BACKUP+LOG+'+%2B@DBNAME%2B++'+TO+DISK+=+'''%2B@BackupFile%2B+'''+WITH+INIT,+NAME=+'''+%2B@BackupName%2B''',+NOSKIP,+NOFORMAT'++++++++
++++++++++++++++++END

--+Execute+the+generated+SQL+command
+++++++EXEC(@sqlCommand)

--+Goto+the+next+database
SELECT+@Loop+=+min(ID)+FROM+@DBs+where+ID>@Loop

END|code-block|syntax|javascript|1425099|批处理文件可能如下所示：|1425100|sqlcmd+-S+localhost\myDB+-Q+"EXEC+sp_BackupDatabases+@backupLocation='c:\Dropbox\backup\DB\',+@backupType='F'"++>>+c:\Dropbox\backup\DB\full.log+2>&1|1425101|和|1425102|sqlcmd+-S+localhost\myDB+-Q+"EXEC+sp_BackupDatabases+@backupLocation='c:\Dropbox\backup\DB\',+@backupType='D'"++>>+c:\Dropbox\backup\DB\diff.log+2>&1|1425103|这种方法的优点是，如果您添加新数据库或删除数据库，您不需要更改任何内容，甚至不需要在脚本中列出数据库。来自JohnB的答案对于只有一个数据库的服务器更好/更简单，这种方法更适合于多个数据库服务器。|1425104|entityMap|0|LINK|mutability|MUTABLE|url|http://support.microsoft.com/kb/2019698^0|4|4|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|16|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|C|$]]|$1|O|3|P|5|F|7|18|8|@]|9|@]|C|$G|H]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|C|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

Just use <a href="http://support.microsoft.com/kb/2019698" rel="noreferrer">this</a> script to dynamically backup all databases on the server. Then create batch file according to the article. It is usefull to create two batch files, one for full backup a and one for diff backup. Then Create two tasks in Task Scheduler, one for full and one for diff.

<pre><code>-- // Copyright © Microsoft Corporation. All Rights Reserved.
-- // This code released under the terms of the
-- // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
USE [master] 
GO 
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
 @databaseName sysname = null,
 @backupType CHAR(1),
 @backupLocation nvarchar(200) 
AS 

 SET NOCOUNT ON; 

 DECLARE @DBs TABLE
 (
 ID int IDENTITY PRIMARY KEY,
 DBNAME nvarchar(500)
 )

 -- Pick out only databases which are online in case ALL databases are chosen to be backed up
 -- If specific database is chosen to be backed up only pick that out from @DBs
 INSERT INTO @DBs (DBNAME)
 SELECT Name FROM master.sys.databases
 where state=0
 AND name=@DatabaseName
 OR @DatabaseName IS NULL
 ORDER BY Name

 -- Filter out databases which do not need to backed up
 IF @backupType='F'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
 END
 ELSE IF @backupType='D'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE IF @backupType='L'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE
 BEGIN
 RETURN
 END

 -- Declare variables
 DECLARE @BackupName varchar(100)
 DECLARE @BackupFile varchar(100)
 DECLARE @DBNAME varchar(300)
 DECLARE @sqlCommand NVARCHAR(1000) 
 DECLARE @dateTime NVARCHAR(20)
 DECLARE @Loop int 

 -- Loop through the databases one by one
 SELECT @Loop = min(ID) FROM @DBs

 WHILE @Loop IS NOT NULL
 BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name
 SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
 SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 

-- Create backup filename in path\filename.extension format for full,diff and log backups
 IF @backupType = 'F'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
 ELSE IF @backupType = 'D'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
 ELSE IF @backupType = 'L'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media
 IF @backupType = 'F'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
 IF @backupType = 'D'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
 IF @backupType = 'L'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

 IF @backupType = 'F' 
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
 END
 IF @backupType = 'D'
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 IF @backupType = 'L' 
 BEGIN
 SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END

-- Execute the generated SQL command
 EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID&gt;@Loop

END
</code></pre>

And batch file can look like this:

<pre><code>sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='F'" &gt;&gt; c:\Dropbox\backup\DB\full.log 2&gt;&amp;1
</code></pre>

and

<pre><code>sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='D'" &gt;&gt; c:\Dropbox\backup\DB\diff.log 2&gt;&amp;1
</code></pre>

The advantage of this method is that you don't need to change anything if you add new database or delete a database, you don't even need to list the databases in the script. Answer from JohnB is better/simpler for server with one database, this approach is more suitable for multi database servers.

I'm running a small web application with SQL server express (2005) as backend. I can create a backup with a SQL script, however, I'd like to schedule this on a daily basis. As extra option (should-have) I'd like to keep only the last X backups (for space-saving reasons obviously) Any pointers?

[edit] SQL server agent is unavailable in SQL server express...

How can I schedule a daily backup with SQL Server Express?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在运行一个小型的web应用程序，使用SQL server express (2005)作为后端。我可以使用SQL脚本创建备份，但是，我想将其安排在每天。作为额外的选项(应该-有)，我想只保留最后X个备份(显然是出于节省空间的原因)，有什么建议吗？编辑SQL server代理在SQL server express中不...

问如何使用SQL Server Express安排每日备份？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SQL Server Express安排每日备份？EN