掌握SQL 2014备份与恢复

2016-11-26

网络安全和信息化 2016年3期

SQL Server提供了方便的图形化管理，只需要采用图形化的方式，一步步进行操作，便能完成数据库的备份。

在SQL Server Manage ment Studio管理界面中，右键单击需要备份的数据库，在弹出的快捷菜单中，选择“任务”→“备份”，弹出备份窗口，在备份窗口界面中，采用默认方式是备份到本地，“备份类型”下拉列表有三个选项，即“完整、“差异”和“事务”，它们之间的差别是：

完整：指完全备份选定的数据库。

差异：差异备份是针对完整备份，即只备份上一次完整备份后变化的数据。

事务日志：指包含创建最后一个备份（可以是完整备份也可以是差异备份）之后对数据库进行的更改。因此，在进行事务日志备份前，需要对数据库进行一次完整的数据库备份。

在首次备份时，采用“完整”备份的方式。单击“添加”按钮，在弹出的“选择备份目标”窗口中，输入备份的文件名后，在备份窗口中，单击“确定”按钮，完成数据库的完整备份。

实际上，在备份窗口中，可以更加详细指定备份的方式，读者可以单击“介质选项”和“备份选项”查看。

一般来讲，完整备份的方式适合首次备份数据库，其它备份方式多数都是依赖在完整备份的基础上进行备份。如果对于存在大数据集的数据库来讲，一次完整的备份是比较耗时的，如果每次都完全备份，则这种方式不适合于实时性要求强的网站或应用。所以，一般在首次采用完整备份后，以后的备份会采用其他方式进行，比如差异备份或事务日志等，这样只是备份了变化的数据或文件，所以备份速度更快。

采用图形界面还原数据库

在SQL Server Manage ment Studio管理界面中，右键单击“数据库”节点，在弹出的快捷菜单中，选择“还原数据库”，弹出还原窗口；或右键单击需要还原的数据库，在弹出的快捷菜单中，选择“任务”→“还原”→“数据库”。

在“源”中，选择需要还原的备份数据，两种方式：一种是“数据库”方式，即在本地备份的数据库，都会列在“数据库”下拉列表中，这种方式适合快速还原本地备份的数据库；另一种方式“设备”方式，这种方式可以直接指定需要还原的备份文件，比如从其他数据库移植到本地的备份文件。当选择需要还原的“源”后，将在“要还原的备份集”中列出还原列表。选择具体还原的备份集后，同时，输入目标“数据库”名称，单击“确定”，完成数据库的还原。

需要注意的是，当还原的数据库存在时，需要确定覆盖已存在的数据库，否则恢复将不成功。更高级的用法，单击“文件”和“选项”查看。

T-SQL备份数据库

T-SQL(Transact-SQL)，是SQL在Microsoft SQL Server上的增强版，其提供了标准SQL的DDL和DML功能，还有延伸的函数、系统预存程序以及程式设计结构。

使用图形化界面备份数据库，可以直观完成整个数据库的备份过程。但作为程序员和高级的网络管理员，还是喜欢使用T-SQL备份语句完成数据库的备份，其一，可以使用任何编程语言采用远程连接的方式快速完成数据库的备份，其二，不用每次都点击繁琐的鼠标操作，其三，灵活性更强。

完整备份数据库的T-SQL语句如下：

backup database DBName to disk='d:db.bak' WITH FORMAT;

以上备份语句将数据库DBName备份到本地文件“d:db.bak”中。而下面语句使用了压缩方式备份数据库：

BACKUP DATABASE DBName TO DISK=' d:db.bak ' WITH FORMAT,COMPRESSION;

差异备份数据库的T-SQL语句如下：

backup database DBName to disk='d:db.bak' WITH Differential;

参数FORMAT表示格式化介质，COMPRESSION表示采用压缩的方式。

采用backup database备份数据库的参数较多，但基础的和常用的是上面的备份数据库语句。其他的参数将在后面使用到时，会逐步进行介绍。

T-SQL还原数据库

还原数据库的T-SQL命令如下所示：

restore database DBName from disk= ' d:db.bak '

以上T-SQL语句将从文件“d:db.bak”进行恢复，还原为数据库DBName；但以上语句需要保证数据库DBName不存在。如果要强制进行还原，则使用如下T-SQL语句：

restore database DBName from disk= ' d:db.bak 'with replace

以上还原语句适合于不改变原备份数据库的物理存储位置。如果将备份的数据文件拷贝到其他服务器中进行还原时，不能保证两台服务器的SQL Server 2014安装位置完全一样，或数据文件的存放位置就无法保证一致。此时，采用如上方法还原时，会提示“系统找不到指定的文件”的错误。解决方法是在：还原时，先查看备份文件中的逻辑文件名，然后将逻辑文件名移动到实际的目录位置。

下面T-SQL命令用于查看备份文件中的逻辑文件名及位置：

restore filelistonly from disk= 'd: db.bak'

运行结果将列出备份文件中的逻辑文件名和其物理位置。

然后采用如下T-SQL命令进行恢复数据库，同时会将恢复后的数据文件移动到指定位置：

在上面语句，更改还原后的数据库名为cc，同时将备份文件中的逻辑文件移动到新的位置，并且更改了文件名称。

由此可以看出，采用如上方法，不但可以完成数据库的备份和还原，同时可以实现数据库的复制。

注意：默认无法在早期版本的 SQL Server中还原较新版本的 SQL Server 创建的备份。

设置自动备份

SQL Server 2014提供了配置自动备份数据库的方法，可以指定系统自动备份的数据库的时间、频率和方法等。这可以进一步减少系统管理员维护数据库的工作量。

在SQL Server Manage ment Studio管理界面中，展开左边“对象资源管理器”树型节点，右键单击“维护计划”，选择“维护计划向导”，将弹出建立维护计划的向导窗口，以引导用户逐步完成创建自动维护任务。

在“选择计划属性”页面，点击“更改”按钮，弹出“新建作业计划”窗口，在该窗口中，设置作业运行的频率以及持续时间。比如，可以设置每天晚上3点运行指定作业，即选择在数据库最少被使用时，运行备份任务。设置完任务作业计划后，单击“确定”按钮，以进行其他设置。

在“选择维护任务”页面，勾选需要维护的任务，可以同时指定多项，比如勾选“备份数据库(完全)”和“”清除维护”任务”两项，前一项用于设置在指定时间自动完全备份数据库，后一项用于指定清除指定文件，。在这里设置“清除”任务很重要，如果只是无限制备份数据，服务器硬盘迟早会被占满。

选择完成后，点击“确定”完成备份，在打开“定义备份数据库任务”页面中，指定备份的数据库，同时需要注意指定备份文件的扩展名，默认是“bak”后缀。指定完成后，进入“定义清除维护任务”页面，

设置指定删除备份文件的条件，比如指定了删除文件的所在文件夹，同时需要指定文件扩展名，比如“bak”，同时，需要指定文件保留时间，这样在同一个文件夹中会有多个指定扩展名的文件存在，维护任务运行时，会查找并保留指定时间内的文件。一般备份文件保留最近一个月或三个月的时间即可，这样，维护任务运行是，系统会自动删除指定时间之前的文件，以节省服务器空间。

设置完成后，根据提示逐步完成维护任务的设置。

这样，系统会在指定时间完成事先设置的任务，以帮助管理员完成繁琐的维护任务。

注意：要正确运行维护任务，需要在系统服务中启动“SQL Server代理”，否则，维护任务将不会执行。

原生加密备份数据库

对于用户来讲，数据的安全性始终是第一的。不但对于数据库中的数据如此，对于备份的数据，同样如此。SQL Server 2014加强了对备份数据的加密管理，其一个新特性就是支持原生备份加密，其使得如果备份文件被盗，在没有加密证书的情况下也无法使用。在SQL Server 2014中，用户可以采用多种加密算法对备份数据文件进行加密，即AES128、AES196、AES256 和三重DES算法。

直接使用SQL Server 2014的加密算法对备份数据文件加密的前提是，存在证书或非对称密钥。那么，首先应该是在SQL Server 2014中创建证书，使用如下简单T-SQL语句：

CertificateName：证书名称，可改成任意名称，只要不重复。

1221adcd：即密钥，可以设置成强密码。

SUBJECT Name：设置主题名称。

以上语句创建了一个名为“CertificateName”的证书，在加密备份中将会用到。接下来，就可以创建加密备份数据文件，点击“介质选项”节点，设置新介质名称，点击“备份选项”节点，勾选“加密备份”复选框，选择一种加密“算法”，选择刚创建的证书，点击“确定”按钮，完成加密备份数据库。

除了图形化界面备份数据库，还可以直接使用如下T-SQL语句完成数据库的备份：

以上T-SQL语句用于备份数据库“abc”，备份位置“d:abc.bak”，加密算法采用AES_256，使用了CertificateName证书。

还原加密的备份数据库

在本地加密了备份数据库，如果本地SQL Server 2014数据库没有重新安装，或者证书没有被删除，都可以正常还原加密后的备份数据库。但如果涉及数据库的移植和重新安装，就需要考虑证书的备份和使用。

在本地SQL Server 2014创建了证书，就需要备份证书，采用如下T-SQL语句：

以上T-SQL语句用于备份证书“CertificateName”至文件“D:CertificateName.cer”，运行完成后，将文件“D:CertificateName.cer”拷贝到其他SQL Server 2014数据库服务器中，运行如下T-SQL语句：

以上T-SQL语句用于从备份证书文件“D:CertificateName.cer”创建一个名为CertificateName的证书。

如果正常执行以上T-SQL语句，将可以在该数据库中按照正常方法恢复加密后的备份数据了。由此，可以发现，恢复加密备份数据库的关键是备份证书，只要有正确的证书，才能还原数据库。

分离和附加数据库

分离和附加数据库的方式也是一种重要的数据库备份和还原的方法，即把数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘上作备份，然后把这两个文件再拷贝到任何需要这个数据库的系统之中。

如果在SQL Server运行中对数据文件进行复制操作，会提示“文件只在使用，不能复制”的错误。所以，数据库的复制有如下两种方法：

一是在系统服务中，将SQL Server数据库的服务停止，然后再进入数据库文件位置复制数据库文件，包括数据库的文件（.MDF）和对应的日志文件（.LDF），以保证数据库文件的完整性。

其次，如果系统中运行了多个数据库，那么SQL Server数据库的服务则无法停止。此时，就需要使用SQL Server数据库提供的数据库分离方法，现将数据库进行分离，再进行复制。

在SQL Server Manage ment Studio管理界面中，右键单击需要分离的数据库，在弹出的快捷菜单中，选择“任务”→“分离”，弹出分离窗口，如果有其他连接正在连接数据库，则在页面中，“状态”列显示为“未就绪”，为了能正常分离数据库，一定要勾选“删除连接”复选框。单击“确定”按钮，完成数据库的分离。

附加数据库时，将需要附加的数据库文件和日志文件同时拷贝到指定文件夹中。在SQL Server Management Studio管理界面中，右键单击“数据库”节点，在弹出的快捷菜单中，选择“附加”，弹出“附加数据库”窗口，在界面中，单击“添加”按钮，找到需要附加的“.MDF”文件，如果该文件正确，将在窗口中列出所有的文件，包括“.MDF”和“.LDF”文件，单击“确定”按钮，完成数据库的附加。

提示：如果服务器操作系统宕机了，但数据库忘记备份了。那么可以直接将数据库文件复制到其他服务器中，在SQL Server直接进行附加操作，就可以完成数据库的还原操作了。

综合应用

实际上，数据库在还原时，有很多工作需要做。比如，最重要的是查看当前待还原的数据库是否有其他用户的连接，如果有，则需要先断开这些连接，然后才能进行数据的还原。那么，可以将这一系列动作合在一起，创建一个过程，在还原数据库时，只需要调用该过程即可。下面是创建过程的T-SQL语句：

以上创建的过程需要放在master数据库中，传入的两个参数分别是：

@dbname：要恢复的数据库；

@dbpath：数据库备份文件的物理路径。

该过程内部定义的@sql用于查找系统中正在运行的指定数据库的连接，接着使用循环语句断开所有连接，最后使用恢复语句，恢复数据库。提示：在该过程中，使用的是完全还原语句，在实际开发中，可以采用任何还原语句；同时，用户在还原过程中，要连接到master数据库，否则，会造成无法断开自己本身的连接，恢复不成功的错误。