SQL Server 高级技术（一） —— 数据备份与恢复

SQL Server 高级技术（一） —— 数据备份与恢复

一、SQL Server 备份和恢复简介

SQL Server 支持的备份方式有四种，分别是完整备份、差异备份、事务日志备份以及文件和文件组备份。SQL Server 支持的恢复方式有三种，分别是完整恢复模式、大容量日志恢复模式和简单恢复模式。

二、SQL Server 数据库备份的 T-SQL 语句

EXEC sp_addumpdevice 'disk','bk_device','D:\db\bk\bk_device.bak'
-- 创建一个名为 bk_device 的备份设备
BACKUP DATABASE demo 
	TO bk_device -- 备份到设备
	WITH DIFFERENTIAL -- 使用差异备份
BACKUP DATABASE demo 
	TO DISK = 'D:\db\bk\backup.bak'
BACKUP DATABASE demo 
	FILE = 'demo' -- 备份文件
	TO bk_device -- 完整备份
BACKUP DATABASE demo
	FILEGROUP = 'static_filegroup' -- 备份文件组
	TO bk_device
BACKUP LOG demo -- 备份事务日志
	TO bk_device
RESTORE HEADERONLY FROM bk_device -- 查看设备内容
RESTORE VERIFYONLY FROM bk_device -- 查看备份设备是否有误，默认只校验第一个备份集
RESTORE VERIFYONLY 
	FROM bk_device
	WITH FILE = 2
RESTORE VERIFYONLY
	FROM DISK = 'D:\db\bk\backup.bak'

二、SQL Server 数据库还原的 T-SQL 语句

还原数据库前的注意事项：
　　1、检查备份设备或文件，可使用 RESTORE VERIFYONLY 语句。
　　2、查看数据库是否有其他人正在使用，如果有人正在使用将无法还原数据库。

USE master
RESTORE DATABASE demo
	FROM bk_device -- 从设备还原数据库
	WITH FILE = 6 -- 使用第 6 个备份集来还原 demo 数据库
RESTORE DATABASE demo
	FROM DISK = 'D:\db\bk\backup.bak' -- 从磁盘文件还原数据库
RESTORE DATABASE demo
	FROM bk_device
	WITH FILE = 1,NORECOVERY -- 还原差异备份
RESTORE LOG demo
	FROM bk_device -- 还原日志备份
RESTORE DATABASE demo
	FILEGROUP = 'PRIMARY'
	FROM bk_device -- 从设备还原文件组
	
DECLARE @bk_date datetime2
RESTORE DATABASE demo
	FROM bk_device,
	WITH FILE = 17,NORECOVERY
	MOVE 'demo' TO 'D:\new\demo_Data.mdf' -- 将文件还原到新位置
	MOVE 'demo_log' TO 'D:\new\demo_log.mdf' 	
GO
RESTORE LOG demo
	FROM bk_device
	WITH FILE = 18, STOPAT= SELECT CONVERT(char(11),@bk_date,120)+CONVERT(char(12),@bk_date,114)

测试

-- 创建测试数据库和表 
USE master;
CREATE DATABASE test;
GO
USE test;
GO
CREATE TABLE [user](id INT NOT NULL IDENTITY PRIMARY KEY,name NVARCHAR(20));
GO

第一轮，覆盖上次的备份，将丢失事务日志和还原节点

---------------- 插入1条数据待测试 -------------------------
INSERT INTO [user](name) VALUES('张三'),('李四');
SELECT * FROM dbo.[user]
---------------- A1 覆盖式备份数据库 ----------------------------
USE master;
BACKUP DATABASE test TO DISK='D:\test.bak';
---------------- 追加一条记录 -----------------
USE test;
INSERT INTO [user] VALUES('王五');
SELECT * FROM dbo.[user]   -- 应为3条数据
---------------- A2 覆盖式备份数据库 ----------------------------
BACKUP DATABASE test TO DISK='D:\test.bak' 
---------------- 插入一条数据，用于还原数据库  ------------------------
INSERT INTO [user] VALUES('赵六');
SELECT * FROM dbo.[user]   -- 应为4条数据
---------------- 直接还原-----------
USE master;
RESTORE DATABASE test FROM DISK='D:\test.bak';  -- 只返回3条，因为 A2 覆盖掉了 A1 的事务日志
USE test;
SELECT * FROM dbo.[user]  -- 只有3条

下一章 《SQL Server 高级技术（二）—— 安全》