数据库的备份与还原

 备份就是对SQL Server数据库或事务日志进行备份，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复。

为什么要进行数据备份？ 

备份数据库的主要目的是为了防止数据的丢失。

   造成数据的丢失包括如下几种情况：
由于不准确的更新而造成的数据的不正确；
由于病毒的侵害而造成的数据的丢失或损坏；
存放数据的物理磁盘或机器的损害；
由于自然灾害而造成的损坏。

备份的四种方式：

（一）完全数据库备份（Dadabase-complete） 
（二）差异备份或称增量备份（Dadabase-differential）
（三）事务日志备份（Transaction log） 
（四）数据库文件和文件组备份（File and filegroup）

如果是将数据库备份到远程计算机的磁盘上，则使用通用命名规则名称，以\\服务器名\共享名\路径\文件名格式指定文件的位置。

 磁带备份设备与磁盘备份设备的用法大致相同，但应注意如下问题：
 必须将磁带设备物理地连接到运行SQL Server实例的计算机上。
 不能备份到远程磁带上。

创建备份设备的格式：

sp_addumpdevice [ @devtype = ] 'device_type' 
        , [ @logicalname = ] 'logical_name' 
        , [ @physicalname = ] 'physical_name'
      [ , { [ @cntrltype = ] controller_type |
          [ @devstatus = ] 'device_status' }
      ]

[ @devtype = ] 'device_type'

备份设备的类型。device_type 的数据类型为 varchar(20)，无默认值，可以是下列值之一

值说明

disk

硬盘文件作为备份设备。

tape

Microsoft Windows 支持的任何磁带设备。

注意：在 SQL Server 的未来版本中将不再支持磁带备份设备。请避免在新的开发工作中使用该功能，并计划修改当前还在使用该功能的应用程序。

[ @logicalname = ] 'logical_name'

在 BACKUP 和 RESTORE 语句中使用的备份设备的逻辑名称。logical_name 的数据类型为 sysname，无默认值，且不能为 NULL。

[ @physicalname = ] 'physical_name'

备份设备的物理名称。物理名称必须遵从操作系统文件名规则或网络设备的通用命名约定，并且必须包含完整路径。physical_name 的数据类型为nvarchar(260)，无默认值，且不能为 NULL。

在远程网络位置上创建备份设备时，请确保启动数据库引擎时所用的名称对远程计算机有相应的写权限。

如果要添加磁带设备，则该参数必须是 Windows 分配给本地磁带设备的物理名称，例如，使用 \\.\TAPE0 作为计算机上的第一个磁带设备的名称。磁带设备必须连接到服务器计算机上，不能远程使用。如果名称包含非字母数字的字符，请用引号将其引起来。

注意：此过程会在目录中输入指定的物理名称。此过程不会尝试访问或创建设备。

[ @cntrltype = ] 'controller_type'

已过时。如果指定该选项，则忽略此参数。支持它完全是为了向后兼容。新的 sp_addumpdevice 使用应省略此参数。

[ @devstatus = ] 'device_status'

已过时。如果指定该选项，则忽略此参数。支持它完全是为了向后兼容。新的 sp_addumpdevice 使用应省略此参数。

下面是几个例子:

1．通过查询分析器，为STUDENT数据库创建一个全数据库备份，要求立即执行，备份设备叫STUDENT_Bak_Full。

使用企业管理器备份：选择数据库STUDENT ->右键 任务->备份

 

 

使用SQL语句完全备份：

Backing Up a Whole Database BACKUP DATABASE { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ][;]

WITH 选项 指定要用于备份操作的选项。

DIFFERENTIAL

只能与 BACKUP DATABASE 一起使用，指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。差异备份一般会比完整备份占用更少的空间。对于上一次完整备份后执行的所有单个日志备份，使用该选项可以不必再进行备份。

USE master

/*创建备份设备STUDENT_Bak_Full.bak*/

GO

EXEC sp_addumpdevice 'disk','STUDENT_Bak_Full' ,'D:\EXSQL\EX9\STUDENT_Bak_Full.bak'

GO

EXEC sp_helpdevice STUDENT_Bak_Full/*查看备份设备*/

/*完全备份STUDENT数据库*/

BACKUP DATABASE STUDENT TO STUDENT_Bak_Full WITH INIT/*WITH INIT表示完全备份*/

 

2．使用企业管理器，为STUDENT数据库创建一个增量备份，要求在11:00执行备份，备份设备叫STUDENT_Bak_Differential

--创建备份设备叫STUDENT_Bak_Differential

EXEC sp_addumpdevice 'disk','STUDENT_Bak_Differential','D:\EXSQL\EX9\STUDENT_Bak_Differential.bak'

--差异备份

BACKUP DATABASE STUDENT TO DISK='D:\EXSQL\EX9\STUDENT_Bak_Differential.bak'

WITH NOINIT,DIFFERENTIAL/*非完全、差异的*/

 

/*

执行结果：

 

已为数据库 'STUDENT'，文件 'STUDENT_data' (位于文件 2 上)处理了 120 页。

已为数据库 'STUDENT'，文件 'STUDENT_log' (位于文件 2 上)处理了 3 页。

BACKUP DATABASE WITH DIFFERENTIAL 成功处理了 123 页，花费 0.423 秒(2.257 MB/秒)。

 

*/

 假如要求在某一时刻备份：

要求在每天11:00对Student 进行执行差异备份

使用企业管理器，管理->维护计划->维护计划向导

 

 

3．使用企业管理器，在STUDENT数据库上创建一个日志备份，备份设备叫STUDENT_Bak_Log。

 

--创建备份设备叫STUDENT_Bak_Log

EXEC sp_addumpdevice 'disk','STUDENT_Bak_Log','D:\EXSQL\EX9\STUDENT_Bak_Log.bak'

 

--日志备份

BACKUP LOG STUDENT TO DISK='D:\EXSQL\EX9\STUDENT_Bak_Log.bak'

WITH NOINIT,DIFFERENTIAL

/*

执行结果：

 

已为数据库 'STUDENT'，文件 'STUDENT_data' (位于文件 2 上)处理了 120 页。

已为数据库 'STUDENT'，文件 'STUDENT_log' (位于文件 2 上)处理了 3 页。

BACKUP LOG WITH DIFFERENTIAL 成功处理了 123 页，花费 0.423 秒(2.257 MB/秒)。

 

*/

为数据库在某一时间段内实现多频率的日志备份

要求从上午11:00到11:30之间每隔10分钟做一次备份，备份设备叫STUDENT_Bak_Log。

使用企业管理器-管理-维护计划向导

 

 

4．分别使用企业管理器和查询分析器，从上面所做的备份中恢复STUDENT数据库以及它的事务日志。 

/*还原完全备份数据库*/

RESTORE  STUDENT FROM STUDENT_Bak_Full WITH REPLACE

 

 

/*还原差异备份*/

USE master

RESTORE DATABASE STUDENT FROM DISK='D:\EXSQL\EX9\STUDENT_Bak_Differential.bak' 

 

WITH NORECOVERY

 

/*还原日志*/

RESTORE LOG STUDENT FROM DISK='D:\EXSQL\EX9\STUDENT_Bak_Differential.bak' 

WITH NORECOVERY

 

还原选项

指定要用于备份操作的选项。

 WITH REPALCE 覆盖现有数据库

 WITH KEEP_REPLACETION 保留复制设置

恢复状态

WITH RECOVERY 回滚未使用的事物，使数据库处于可以使用的状态

WITH NORECOVERY  不对数据库执行任何操作 ，不回滚未提交的事物。

详见下图

有时进行数据库还原时，可能出现以下错误:

无法还原日志备份或差异备份，因为没有文件可用于前滚---还原SQLSERVER数据库差异备份

解决方案详见:http://blog.csdn.net/kimifdw/article/details/12744309