2023年7月30日发(作者：)

⾃动定时备份sqlserver数据库的⽅法下⾯是我已经证实可⽤的⾃动备份的⽅法.

1、打开企业管理器->管理->sql server代理

2、新建⼀个作业,作业名称随便取,例如：data备份,所有者选择sa,当然你也可以选择其他⽤户,前提是该⽤户有执⾏作业的权限；

3、点击步骤标签,进⼊步骤⾯板.新建步骤,步骤名可以随便填写,如步骤1,类型和数据库默认,不需要修改.命令中写⼊以下语句：BACKUP DATABASE [数据库名] TO DISK = N'F:databackfirenews备份' WITH NOINIT , NOUNLOAD , NAME =N'firenews备份', NOSKIP , STATS = 10, NOFORMAT

注意：需要修改的地⽅,数据库名,DISK＝（这⾥需要填写路径和你的数据库备份的名称）后⾯的Name＝可以随便填写.

4、点击调度标签,进⼊调度⾯板,新建调度,名称随便填写,选择反复出现,点更改可以选择你想要执⾏任务的随意调度.如每天,每2天,每星期,每⽉等.根据需要⾃⼰设置；（注意：在调度名称后⾯有个启动选项，必须勾选）

5、确定后,不要忘记⼀件事情,在你刚才建⽴的⼯作上点右键,启动⼯作,如果你的⼯作没有问题,将会提⽰执⾏成功,并有相对应的备份⽂件在你的磁盘上出现；

6、还有⼀个重要的问题就是你的sql server agent服务器已经启动.

如果我们需要根据每天的⽇期来⽣成⼀个新的备份,以便我们区别备份⽂件.这时,我们需要修改⼀下刚才的sql语句.

参考实例：

1、⾃动备份，固定备份名，每次会追加备份

复制代码 代码如下:backup database FireNews to disk=N'F:databackfirenews备份' with noinit,nounload,name=N'firenews备份',noskip,stats=10,noformat

backup database fireweb to disk=N'F:backupdatafirewebfireweb备份' with noinit,nounload,name=N'fireweb备份',noskip,stats=10,noformat

2、⾃动备份，以时间命名，完整备份

复制代码 代码如下:declare @filename nvarchar(100) set @filename='F:databackdata'+convert(char(8),getdate(),112)+'.bak' print @filenameBACKUP DATABASE [firenews] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'firenews备份', NOSKIP,STATS = 10, NOFORMAT

declare @filename nvarchar(100) set @filename='F:backupdatafirewebfireweb'+convert(char(8),getdate(),112)+'.bak'print @filename BACKUP DATABASE [fireweb] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'fireweb备份',NOSKIP, STATS = 10, NOFORMAT

注：

1、备份的数据库存放的⽂件夹必须已经存在；

2、N 必须⼤写；

3、NAME的值可以随便写；

4、FireNews,fireweb为数据库名；

5、新建作业时提⽰：未能找到存储过程_regread，表名master数据库没有这个存储过程(可能是做安全处理的时候删除了)，需⾃⼰注册。⽅法是：找到 数据库——

master——扩展存储过程，右键“新建扩展存储过程”（名称：xp_regread，路径直接填：）

以下为完整的备份数据库的⽅法：

备份整个数据库：

复制代码 代码如下:BACKUP DATABASE { database_name | @database_name_var }

TO < backup_device > [ ,...n ]

[ WITH

[ BLOCKSIZE = { blocksize | @blocksize_variable } ]

[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]

[ [ , ] DIFFERENTIAL ]

[ [ , ] EXPIREDATE = { date | @date_var }

| RETAINDAYS = { days | @days_var } ]

[ [ , ] PASSWORD = { password | @password_variable } ]

[ [ , ] FORMAT | NOFORMAT ]

[ [ , ] { INIT | NOINIT } ]

[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]

[ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]

[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]

[ [ , ] { NOSKIP | SKIP } ]

[ [ , ] { NOREWIND | REWIND } ]

[ [ , ] { NOUNLOAD | UNLOAD } ]

[ [ , ] RESTART ]

[ [ , ] STATS [ = percentage ] ]

]

参数

DATABASE

指定⼀个完整的数据库备份。假如指定了⼀个⽂件和⽂件组的列表，那么仅有这些被指定的⽂件和⽂件组被备份。

说明 在进⾏完整数据库备份或差异数据库备份时，Microsoft® SQL Server™ 备份⾜够的事务⽇志，以⽣成⼀个将在还原数据库时使⽤的⼀致的数据库。在 master 数据库上只能采⽤完整数据库备份。

{ database_name | @database_name_var }

指定了⼀个数据库，从该数据库中对事务⽇志、部分数据库或完整的数据库进⾏备份。如果作为变量 (@database_name_var)提供，则可将该名称指定为字符串常量 (@database_name_var = database name) 或字符串数据类型（ntext 或 text 数据类型除外）的变量。

< backup_device >

指定备份操作时要使⽤的逻辑或物理备份设备。可以是下列⼀种或多种形式：

{ logical_backup_device_name } | { @logical_backup_device_name_var }

是由 sp_addumpdevice 创建的备份设备的逻辑名称，数据库将备份到该设备中，其名称必须遵守标识符规则。如果将其作为变量 (@logical_backup_device_name_var) 提供，则可将该备份设备名称指定为字符串常量(@logical_backup_device_name_var = logical backup device name) 或字符串数据类型（ntext 或 text 数据类型除外）的变量。

{ DISK | TAPE } =

'physical_backup_device_name' | @physical_backup_device_name_var

允许在指定的磁盘或磁带设备上创建备份。在执⾏ BACKUP 语句之前不必存在指定的物理设备。如果存在物理设备且BACKUP 语句中没有指定 INIT 选项，则备份将追加到该设备。

当指定 TO DISK 或 TO TAPE 时，请输⼊完整路径和⽂件名。例如，DISK = 'C:Program FilesMicrosoft ' 或 TAPE = '.TAPE0'。

说明 对于备份到磁盘的情况，如果输⼊⼀个相对路径名，备份⽂件将存储到默认的备份⽬录中。该⽬录在安装时被设置并且存储在 KEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServerMSSQLServer ⽬录下的 BackupDirectory 注册表键值中。

如果使⽤的是具有统⼀命名规则 (UNC) 名称的⽹络服务器或已重新定向的驱动器号，则请指定磁盘的设备类型。

当指定多个⽂件时，可以混合逻辑⽂件名（或变量）和物理⽂件名（或变量）。但是，所有的设备都必须为同⼀类型（磁盘、磁带或管道）。

Windows 98 不⽀持备份到磁盘。

是表⽰可以指定多个备份设备的占位符。备份设备数⽬的上限为 64。

BLOCKSIZE = { blocksize | @blocksize_variable }

⽤字节数来指定物理块的⼤⼩。在 Windows NT 系统上，默认设置是设备的默认块⼤⼩。⼀般情况下，当 SQL Server 选择适合于设备的块⼤⼩时不需要此参数。在基于 Windows 2000 的计算机上，默认设置是 65,536（64 KB，是 SQL Server ⽀持的最⼤⼤⼩）。

对于磁盘，BACKUP ⾃动决定磁盘设备合适的块⼤⼩。

说明 如果要将结果备份集存储到 CD-ROM 中然后从 CD-ROM 中恢复，请将 BLOCKSIZE 设为 2048。

磁带的默认 BLOCKSIZE 为 65,536 (64 KB)。显式声明块⼤⼩将替代 SQL Server 选择的块⼤⼩。

DESCRIPTION = { 'text' | @text_variable }

指定描述备份集的⾃由格式⽂本。该字符串最长可以有 255 个字符。

DIFFERENTIAL

指定数据库备份或⽂件备份应该与上⼀次完整备份后改变的数据库或⽂件部分保持⼀致。差异备份⼀般会⽐完整备份占⽤更少的空间。对于上⼀次完整备份时备份的全部单个⽇志，使⽤该选项可以不必再进⾏备份。有关更多信息，请参见差异数据库备份和⽂件差异备份。 说明 在进⾏完整数据库备份或差异备份时，SQL Server 备份⾜够的事务⽇志，从⽽在恢复数据库时⽣成⼀个⼀致的数据库。

EXPIREDATE = { date | @date_var }

指定备份集到期和允许被重写的⽇期。如果将该⽇期作为变量 (@date_var) 提供，则可以将该⽇期指定为字符串常量(@date_var = date)、字符串数据类型变量（ntext 或 text 数据类型除外）、smalldatetime 或者 datetime 变量，并且该⽇期必须符合已配置的系统 datetime 格式。

RETAINDAYS = { days | @days_var }

指定必须经过多少天才可以重写该备份媒体集。假如⽤变量 (@days_var) 指定，该变量必须为整型。

重要 假如 EXPIREDATE 或 RETAINDAYS 没有指定，有效期将取决于 sp_configure 的 media retention 配置设置。这些选项仅仅阻⽌ SQL Server 重写⽂件。⽤其它⽅法可擦除磁带，⽽通过操作系统可以删除磁盘⽂件。有关过期验证的更多信息，请参见本主题的 SKIP 和 FORMAT。

PASSWORD = { password | @password_variable }

为备份集设置密码。 PASSWORD 是⼀个字符串。如果为备份集定义了密码，必须提供这个密码才能对该备份集执⾏任何还原操作。

重要 备份集密码防⽌未经授权即通过 SQL Server 2000 ⼯具访问备份集的内容，但是不能防⽌重写备份集。

有关使⽤密码的更多信息，请参见"权限"部分。

FORMAT

指定应将媒体头写⼊⽤于此备份操作的所有卷。任何现有的媒体头都被重写。FORMAT 选项使整个媒体内容⽆效，并且忽略任何现有的内容。

重要 使⽤ FORMAT 要谨慎。格式化⼀个备份设备或媒体将使整个媒体集不可⽤。例如，如果初始化现有条带备份集中的单个磁带，则整个备份集都将变得不可⽤。

通过指定 FORMAT，备份操作也就暗⽰了 SKIP 和 INIT；这些都不必显式说明。

NOFORMAT

指定媒体头不应写⼊所有⽤于该备份操作的卷中，并且不要重写该备份设备除⾮指定了 INIT。

INIT

指定应重写所有备份集，但是保留媒体头。如果指定了 INIT，将重写那个设备上的所有现有的备份集数据。

当遇到以下⼏种情况之⼀时不重写备份媒体：

媒体上的备份设置没有全部过期。有关更多信息，请参见 EXPIREDATE 和 RETAINDAYS 选项。

如果 BACKUP 语句给出了备份集名，该备份集名与备份媒体上的名称不匹配。有关更多信息，请参见 NAME ⼦句。

使⽤ SKIP 选项替代这些检查。有关使⽤ SKIP、NOSKIP、INIT 和 NOINIT 时的相互作⽤关系的更多信息，请参见注释部分。

说明 如果备份媒体有密码保护，SQL Server 将不写⼊媒体，除⾮提供媒体密码。SKIP 选项不替代此检查。只有通过格式化才能重写受密码保护的媒体。有关更多信息，请参见 FORMAT 选项。

NOINIT

表⽰备份集将追加到指定的磁盘或磁带设备上，以保留现有的备份集。NOINIT 是默认设置。

RESTORE 命令的 FILE 选项⽤于在还原时选择适当的备份集。有关更多信息，请参见 RESTORE。

如果为媒体集定义了媒体密码，则必须提供密码。

MEDIADESCRIPTION = { text | @text_variable }

指明媒体集的⾃由格式⽂本描述，最多为 255 个字符。

MEDIADESCRIPTION = { text | @text_variable }

为整个备份媒体集指明媒体名，最多为 128 个字符。假如指定了 MEDIANAME，则它必须与以前指定的媒体名相匹配，该媒体名已存在于备份卷中。假如没有指定 MEDIANAME，或指定了 SKIP 选项，将不会对媒体名进⾏验证检查。

MEDIAPASSWORD = { mediapassword | @mediapassword_variable }

为媒体集设置密码。MEDIAPASSWORD 是⼀个字符串。

如果为媒体集定义了密码，则在该媒体集上创建备份集时必须提供此密码。另外，从该媒体集执⾏任何还原操作时也必须提供媒体密码。只有通过格式化才能重写受密码保护的媒体。有关更多信息，请参见 FORMAT 选项。

有关使⽤密码的更多信息，请参见"权限"部分。

NAME = { backup_set_name | @backup_set_var }

指定备份集的名称。名称最长可达 128 个字符。假如没有指定 NAME，它将为空。 NORECOVERY

只与 BACKUP LOG ⼀起使⽤。备份⽇志尾部并使数据库处于正在还原的状态。当将故障转移到辅助数据库或在 RESTORE操作前保存⽇志尾部时，NORECOVERY 很有⽤。

STANDBY = undo_file_name

只与 BACKUP LOG ⼀起使⽤。备份⽇志尾部并使数据库处于只读或备⽤模式。撤消⽂件名指定了容纳回滚更改的存储，如果随后应⽤ RESTORE LOG 操作，则必须撤消这些回滚更改。

如果指定的撤消⽂件名不存在，SQL Server 将创建该⽂件。如果该⽂件已存在，则 SQL Server 将重写它。有关更多信息，请参见使⽤备⽤服务器。

NOREWIND

指定 SQL Server 在备份操作完成后使磁带保持打开。NOREWIND 意即 NOUNLOAD。SQL Server 将保留磁带驱动器的所有权，直到 BACKUP或 RESTORE 命令使⽤ REWIND 为⽌。

如果⽆意中使磁带处于打开状态，则释放磁带的最快⽅法是使⽤下⾯的 RESTORE 命令：

RESTORE LABELONLY FROM TAPE = WITH REWIND通过查询 master 数据库中的 sysopentapes 表可以查找正在打开的磁带列表。

REWIND

指定 SQL Server 将释放磁带和倒带。如果 NOREWIND 和 REWIND 均未指定，则默认设置为 REWIND。

NOSKIP

指⽰ BACKUP 语句在可以重写媒体上的所有备份集之前先检查它们的过期⽇期。

SKIP

禁⽤备份集过期和名称检查，这些检查⼀般由 BACKUP 语句执⾏以防重写备份集。有关更多信息，请参见注释部分。

NOUNLOAD

指定不在备份后从磁带驱动器中⾃动卸载磁带。设置始终为 NOUNLOAD，直到指定 UNLOAD 为⽌。该选项只⽤于磁带设备。

UNLOAD

指定在备份完成后⾃动倒带并卸载磁带。启动新⽤户会话时其默认设置为 UNLOAD。该设置⼀直保持到⽤户指定了NOUNLOAD 时为⽌。该选项只⽤于磁带设备。

RESTART

指定 SQL Server 重新启动⼀个被中断的备份操作。因为 RESTART 选项在备份操作被中断处重新启动该操作，所以它节省了时间。若要重新启动⼀个特定的备份操作，请重复整个 BACKUP 语句并且加⼊ RESTART 选项。不⼀定⾮要使⽤RESTART 选项，但是它可以节省时间。

重要 该选项只⽤于导向磁带媒体的备份和跨越了多个磁带卷的备份。在备份的第⼀卷上永远不会有重新启动操作。

STATS [= percentage]

每当另⼀个 percentage 结束时显⽰⼀条消息，它被⽤于测量进度。如果省略 percentage，SQL Server 将每完成 10 个百分点显⽰⼀条消息。

指定包含在数据库备份中的⽂件或⽂件组的逻辑名。可以指定多个⽂件或⽂件组。

FILE = { logical_file_name | @logical_file_name_var }

给⼀个或多个包含在数据库备份中的⽂件命名。

FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

给⼀个或多个包含在数据库备份中的⽂件组命名。

说明 当数据库的⼤⼩和性能要求使得进⾏完整数据库备份不切实际时，备份⼀个⽂件。若要单独备份事务⽇志，请使⽤BACKUP LOG。

重要 必须通过使⽤ BACKUP LOG 提供事务⽇志的单独备份，才能使⽤⽂件和⽂件组备份来恢复数据库。有关⽂件备份的更多信息，请参见备份使⽤⽂件备份。

如果恢复模型为 SIMPLE，则不允许⽂件和⽂件组备份。

是⼀个占位符，表⽰可以指定多个⽂件和⽂件组。对⽂件或⽂件组的最⼤个数没有限制。

LOG

指定只备份事务⽇志。该⽇志是从上⼀次成功执⾏了的 LOG 备份到当前⽇志的末尾。⼀旦备份⽇志，可能会截断复制或活动事务不再需要的空间。

说明 假如备份⽇志看来并没有截断⼤部分的⽇志，则有可能在⽇志中存在⼀个旧的开放事务。可以使⽤ DBCC SQLPERF(LOGSPACE) 观察⽇志空间。有关更多信息，请参见事务⽇志备份。

NO_LOG | TRUNCATE_ONLY

⽆须备份复制⽇志即删除不活动的⽇志部分，并且截断⽇志。该选项会释放空间。因为并不保存⽇志备份，所以没有必要指定备份设备。NO_LOG 和 TRUNCATE_ONLY 是同义的。

使⽤ NO_LOG 或 TRUNCATE_ONLY 备份⽇志后，记录在⽇志中的更改不可恢复。为了恢复，请⽴即执⾏ BACKUPDATABASE。

NO_TRUNCATE

允许在数据库损坏时备份⽇志。

注释

可以将数据库或⽇志备份追加到任何磁盘或磁带设备上，从⽽使得数据库和它的事务⽇志能存储在⼀个物理位置中。

当数据库正在使⽤时，SQL Server 使⽤⼀个联机备份过程来对数据库进⾏备份。下⾯的列表包括在数据库或事务⽇志备份时⽆法进⾏的操作：

在备份操作时允许进⾏⽂件管理操作，如带有 ADD FILE 或 REMOVE FILE 选项的 ALTER DATABASE 语句，以及INSERT、UPDATE 或 DELETE 语句。

收缩数据库或⽂件。这包括⾃动收缩操作。

假如在这些操作正在进⾏时启动备份，备份将终⽌。假如正在进⾏备份时，试图进⾏这些操作，则操作会失败。

只要操作系统⽀持数据库的排序规则，就可以在不同的平台之间执⾏备份操作，即使这些平台使⽤不同的处理器类型。