备份是指对SQL Server数据库或事务日志进行的复制，数据库备份记录了在进行备份操作时数据库中所有数据的状态，如果数据库因意外而损坏，这些备份文件将在数据库恢复时被用来恢复数据库。

一.数据库备份概述

1.备份内容

 数据库中数据的重要程度决定了数据恢复的必要性与重要性，即决定了数据如何备份，数据库需备份的内容可分为系统数据库、用户数据库和事务日志3部分。

系统数据库主要包括master、msdb和model数据看，它们记录了重要的系统信息，是确保SQL Server2005系统正常运行的重要依据，必须完全备份。

用户数据库是存储用户数据的存储空间集，通常用户数据库中的数据依其重要性可分为关键数据和非关键数据。对于关键数据则是用户的重要数据，不易甚至不能重新创建，必须进行完全备份。

事务日志记录了用户对数据的各种操作，平时系统会自动管理和维护所有的数据库事务日志。相对于数据库备份，事务日志备份所需要的时间较少，但恢复需要的时间比较长。

 在SQL Server2005中固定服务器角色sysadmin和固定数据库角色db_owner、db_backupoperator可以做备份操作。但通过授权其他角色也允许数据库备份。

2.备份设备

备份设备是指数据库备份到的目标载体，即备份到何处。在SQL Server2005中允许使用两种类型的备份设备，分别为硬盘和磁带。硬盘是最常用的备份设备，用于备份本地文件和网络文件。磁带是大容量备份设备，仅用于备份本地文件。

在进行数据库备份时，可以首先创建用于存储备份的备份设备，然后再将备份存放到指定的设备上，一般情况下，命名备份设备实际就是对应某一物理文件的逻辑名称。

3.备份频率

数据库备份频率一般取决于修改数据库的频繁程度以及一旦出现意外，丢失的工作量的大小，还有发生意外丢失数据的可能性大小。

在正常使用阶段，对系统数据库的修改不会十分频繁，所以对系统数据库的备份也不需要十分频繁，只要在执行某些语句或存储过程导致SQL Server对系统数据库进行了修改的时候备份。

如果在用户数据库中执行了添加数据、创建索引等操作，则应该对用户数据库进行备份。如果清楚了事务日志，也应该备份数据库。

4.数据库备份的类型

SQL Server2005支持4种基本类型的备份：完全备份、事务日志备份、差异备份、文件和文件组备份。

（1）完全备份。完全备份将备份整个数据库，包括用户表、系统表、索引、视图和存储过程等所有数据库对象。适用于数据更新缓慢的数据库。

（2）事务日志备份。事务日志是一个单独文件，它记录数据库的改变，备份的时候只复制自上次备份事务日志后对数据库执行的所有事务的一系列记录。

（3）差异备份。差异备份只记录自上次数据库备份后发生更改的数据，差异备份一般会比完全备份占用更少的空间。

（4）文件和文件组备份。当数据库非常庞大时，可执行数据库文件或文件组备份。这种备份策略使用户只恢复已损坏的文件或文件组。而不用恢复数据库的其余部分，所以，文件和文件组的备份及恢复是一种相对较完善的备份和恢复过程。

二.创建和删除备份设备

进行数据库备份时，必须创建用来存储备份的备份设备。创建和删除备份设备可以使用SQL Server管理平台和系统存储过程sp_addumpdevice、sp_dropdevice实现。

1.使用SQL Server管理平台创建备份设备

其操作步骤如下：

（1）在SQL Server管理平台的”对象资源管理器“中，展开服务器树，选择”服务器对象“结点并展开，在其下的”备份设备“结点上单击鼠标右键，从弹出的快捷菜单上选择”新建备份设备“，打开”备份设备“窗口。

（2）在出现的”备份设备“窗口中的”设备名称“文件框中输入新设备的逻辑名称，如Sales_d。在下面的”文件“文本框中显示的是一个默认的文件名及其路径，用户可以对它进行修改。

（3）设置好后，单击”确定“按钮，即可创建备份设备。所创建的备份设备可在”备份设备“结点下看到。

2.使用系统存储过程sp_addumpdevice创建备份设备

sp_addumpdevice的语法格式如下：

sp_addumpdevice [@devtype]'device_type'

[@logincalname=]'logincal_name',

[@physicalname=]'physical_name',

[,{[@cntrltype=]controller_type|[@devstatus=]'device_status'}]

各选项含义如下：

（1）[@devtype=]‘device_type’:备份设备的类型，取值为：DISK表示硬盘，PIPE表示命名管道，TAPE表示磁带设备。

（2) [@logincalname=]'logincal_name':备份设备的逻辑名称，该逻辑名称用于BACKUP和 RESTORE语句中。

（3）[@physicalname=]'physical_name'：备份设备的物理名称。物理名称应遵守操作系统文件名的规则或者网络设备的通用命名规则，并且必须使用完整的路径。

（4）[@cntrltype=]controlleer_type:在创建备份设备时不是必须的，主要用于脚本。设备类型2为DISK时使用，设备类型5为TAPE时使用，设备类型6为PIPE时使用。

（5）[@devstatus=]'device_status'：指明是读取（NOSKIP）ANSI磁带标签，还是忽略(SKIP)。默认值为NOSKIP。

举例：使用系统存储过程创建备份设备test_backup。

USE Sales

GO

EXEC sp_addumpdevice 'DISK','test_backup','d:\test_backup.bak'

本例添加一个逻辑名称为test_backup的磁盘备份设备，物理名称为d:\test_backup.bak。

举例：添加网络磁盘备份设备。

USE Sales

GO

EXEC sp_addumpevice 'DISK','netdevice','\servername\sharename\filename.ext'

本例添加一个远程磁盘备份设备，并命名其逻辑名称为netdevice。

3.使用SQL Server管理平台删除备份设备

使用SQL Server管理平台删除备份设备的操作步骤如下：

（1）打开SQL Server管理平台，在”对象资源管理器“中展开结点”数据库服务器“→”服务器对象“→”备份设备“。

（2）在”备份设备“结点下，选择要删除的设备，右击该设备，从弹出的快捷菜单中选择”删除“命令即完成删除操作。

4.使用系统存储过程sp_dropdevice删除备份设备

sp_dropdevice语句的语法格式如下：

sp_dropdevice [@logicalname=]'device'[,[@delfile=]'delfile']

各选项含义如下：

（1）[@logicalname=]'device':数据库设备或备份设备的逻辑名称，该名称存储在系统表中。

（2）[@delfile=]'delfile':指出是否应该删除物理备份设备文件。如果将其指定为DELFILE，则表示删除物理备份设备的磁盘文件。

举例：使用系统存储过程删除上例创建的备份设备test_backup。

USE Sales

GO

EXEC sp_dropdevice 'test_backup'

三.备份数据库

备份数据库可以使用SQL Server管理平台和Transact-SQL语句BACKUP来实现。

1.使用SQL Server管理平台备份数据库

其操作步骤如下：

（1）打开SQL Server管理平台，在对象资源管理器中，展开结点”服务器树“→”数据库“，右击需要备份的数据库名称，在弹出的快捷菜单上选择”任务“→”备份“命令，打开”备份数据库“窗口。

（2）在”备份数据库“窗口的”常规“选项卡上，”数据库“下拉列表框中可以更改待备份的数据库；选择备份的类型，如果是第一次备份，应该选择”完全“备份；在”备份集“名称文本框中可设置此备份的名称；”备份集过期时间“0表示永远过期；”目标“中可添加或删除备份设备。

（3）设置完成后，单击”确定“按钮开始备份。

2.使用Transact-SQL语句BACKUP备份数据库

使用Transact-SQL语句BACKUP可以对整个数据库、数据库文件及文件组、事务日志进行备份。

BACKUP语句的语法格式为：

BACKUP{DATABASE|LOG}

{database_name|@database_name_var}

[<file_or_filegroup>[,...n]]

TO <backup_device>[,...n]

[WITH

[BLOCKSIZE={blocksize|@blocksize_var}]

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

[[,]DEFFERENTIAL]

[[,]EXPIREDATE={date|@date_var}|RETAINDAYS={days|@days_var}]

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

[[,]{INIT|NOINIT}]

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

[[,]MEDIANAME={media_name|media_name_var}]

[[,]MEDIAPASSWORD={mediapassword|@mediapassword_var}]

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

[[,]NO_TRUNCATE]

[[,]{NORECOVERY|STANDBY=undo_file_name}]

[[,]{NOSKIP|SKIP}]

[[,]RESTART]

[[,]STATS[=percentage]]

]

各选项含义如下：

（1）{DATABASE|LOG}：指定是备份数据库还是备份事务日志，LOG指定只备份事务日志。

（2）{database_name|@database_name_var}：指定了要备份的数据库名称。

（3）<file_or_filegroup>：指定包含在数据库备份中的文件或文件组的逻辑名。可以指定多个文件或文件组。

（4）<backup_device>：指定备份操作时要使用的逻辑或物理备份设备。默认值为逻辑设备名。其格式为：

<backup_device>::={{'logical_backup_device_name'|@logical_backup_device_name_var}

|{DISK|TAPE}={'physical_backup_device_name'|@physical_backup_name_var}}

当<backup_device>取值为{DISK|TAPE}='physical_backup_device_name'|@physical_backup_device_name_var时，指定备份设备为物理设备，在执行BACKUP之前不必存在指定的物理设备。

（5）BLOCKSIZE={blocksize|@blocksize_var}：指定物理块的字节长度。

（6）DESCRIPTION={‘text’|@text_var}：备份描述文本，最长可以有255个字符。

（7）DEFFERENTIAL:说明以差异备份方式备份数据库。

（8）EXPIREDATE={date|@date_var}:指定备份的有效期。

（9）RETAINDAYS={days|@days_var}:指定必须经过多少天才可以重写该备份。

（10）PASSWORD={password|@password_var}：为备份设置密码。

（11）{INIT|NOINIT}:指定是重写还是追加介质，NOINIT为默认值，表示追加数据。

（12）MEDIADESCRIPTION={'text'|@text_var}:介质描述文本，最多为255个字符。

（13）MEDIANAME={media_name|@media_name_var}:指定备份介质名称，最多为128个字符。若指定该项参数，则它必须与以前指定的介质名称相匹配。

（14）MEDIAPASSWORD={medeapassowrd|@mediapassword_var}：为介质设置密码。

（15）NAME={backup_set_name|@backup_set_name_var}:指定备份的名称，最长为128个字符。

（16）NO_TRUNCATE:允许在数据库不可访问的情况下也备份日志，与BACKUPLOG一起使用。

（17）NORECOVERY：说明备份到日志尾部并使数据库处于正在还原的状态，只与BACKUP LOG 一起使用。

（18）STANDBY=undo_file_name:指明备份到日志尾部并使数据库处于只读或备用模式。撤销文件名指定了容纳回退更改的存储。只与BACKUP LOG一起使用。

（19）{NOSKIP|SKIP}:指示是否对备份集过期和名称进行检查，SKIP表示不检查。

举例：使用Transact-SQL语句备份数据库。

（1）数据库完全备份。

将数据库Sales备份到一个磁盘文件上，备份设备为物理设备。

BACKUP DATABASE SalesTO DISK='D:\Sales.BAK'

将数据库Sales完全备份到逻辑备份设备back1上。

BACKUP DATABASE Sales TO back1

若将Sales数据库分别备份到back2、back3上，可使用”，“将备份设备分隔。

BACKUP DATABASE Sales TO back2,back3

（2）数据库差异备份

在BACKUP DATABASE 语句中使用WITH DIFFERENTIAL项以实现数据库差异备份。

将Sales数据库差异备份到一个磁盘文件上。

BACKUP DATABSE Sales TO Disk=‘D:\Salesbk.bak’WITH DIFFERENTIAL

将Sales数据库差异备份到备份设备back4上。

BACKUP DATABASE Sales TO back4 WITH DEFFERENTIAL

注意：只有已经执行了完全数据库备份的数据库才能执行差异备份。为了使差异备份与完全备份的设备能相互区分开来，应使用不同的设备名。

（3）事务处理日志备份。

将Sales数据库的事务日志备份到备份设备back4上。

BACKUP LOG Sales TO back4

注意：当数据库被损坏时，应使用WITH NO_TRUNCATE选项备份数据库。该选项可以备份最近的所有数据库活动，SQL Server将保存整个事务日志。

（4）备份数据文件和文件组。

在BACKUP DATABASE 语句中使用”FILE=逻辑文件“或”FILEGROUP=逻辑文件组名“来备份文件和文件组。

举例：将数据库Sales的数据文件和文件组备份到备份设备back4中。

BACKUP DATABASE Sales 

FILE='Sales_data1'

FILEGROUP='fg1',

FILE='Sales_data2',

FILEGROUP='fg2'

TO back4

BACKUP LOG Sales TO back4

本例将数据库Sales的数据文件Sales_data1、Sales_data2及文件组fg1、fg2备份到备份设备back4中。

注意：必须使用BACKUP LOG提供事务日志的单独备份，才能使用文件和文件组备份来恢复数据库，且必须指定文件或文件组的逻辑名。