1)文件及文件组备份与还原示例
--以下代码简单地演示了如何进行文件组的备份及还原（在还原时，模拟了丢失第二次文件组备份文件的情况）。
--创建测试数据库
CREATE DATABASE db
ON PRIMARY(
NAME='db_data',
FILENAME= 'c:\db_data.mdf'),
FILEGROUP db_fg1(
NAME = 'db_fg1_data',
FILENAME = 'c:\db_fg1_data.ndf'),
FILEGROUP db_fg2(
NAME = 'db_fg2_data',
FILENAME = 'c:\db_fg2_data.ndf')
LOG ON(
NAME='db_log',
FILENAME ='c:\db.ldf')
GO
--在文件组db_fg1上创建表，并单独创建该文件组的备份
CREATE TABLE db.dbo.tb(id int) ON db_fg1
BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1.bak' WITH FORMAT
GO
在其他文件组上创建表
CREATE TABLE db.dbo.ta(id int) ON [PRIMARY]
CREATE TABLE db.dbo.tc(id int) ON db_fg2
INSERT db.dbo.tb SELECT id FROM sysobjects
--备份每个文件组，并且备份事务日志
BACKUP DATABASE db FILEGROUP='PRIMARY' TO DISK='c:\db_primary.bak' WITH FORMAT
BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1_new.bak' WITH FORMAT
BACKUP DATABASE db FILEGROUP='db_fg2' TO DISK='c:\db_fg2.bak' WITH FORMAT
BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT
GO
--删除数据库
DROP DATABASE db
GO
--从文件组备份中恢复数据
RESTORE DATABASE db FILEGROUP='PRIMARY' FROM DISK='c:\db_primary.bak' WITH NORECOVERY,REPLACE
RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH NORECOVERY
RESTORE DATABASE db FILEGROUP='db_fg2' FROM DISK='c:\db_fg2.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
SELECT COUNT(*) FROM db.dbo.tb
GO
--删除测试数据库
DROP DATABASE db
2)数据库部分还原示例
--下面的示例创建名为db的示例数据库，并在该数据库中演示部分还原操作。db使用的是完全恢复模型。示例中演示了把文件组db_fg1还原为数据库db_a。
USE master
--创建测试数据库
CREATE DATABASE db
ON PRIMARY(
NAME='db_data',
FILENAME= 'c:\db_data.mdf'),
FILEGROUP db_fg1(
NAME = 'db_fg1_data',
FILENAME = 'c:\db_fg1_data.ndf'),
FILEGROUP db_fg2(
NAME = 'db_fg2_data',
FILENAME = 'c:\db_fg2_data.ndf')
LOG ON(
NAME='db_log',
FILENAME ='c:\db.ldf')
GO
--备份数据库
BACKUP DATABASE db TO DISK='c:\db.bak' WITH FORMAT
GO
--在文件组 db_fg1 上创建表 ta
CREATE TABLE db.dbo.ta(id int) ON db_fg1
--在文件组 db_fg2 上创建表 tb
CREATE TABLE db.dbo.tb(id int) ON db_fg2
GO
--备份事务日志
BACKUP LOG db TO DISK ='c:\db_log.bak' WITH FORMAT
GO
--如果因为某些要求只还原ta表,则使用RESTORE DATABASE语句配合WITH PARTIAL和NORECOVERY选项以另一个名称还原数据库和文件组db_fg1,另外，还将主文件和文件组primary、事务日志和已还原的文件组中的所有文件移到新位置。日志即得到恢复
RESTORE DATABASE db_a
FILEGROUP='db_fg1'
FROM DISK='c:\db.bak'
WITH NORECOVERY,PARTIAL,
MOVE 'db_data' TO 'c:\db_data_a.mdf',
MOVE 'db_log' TO 'c:\db_log_a.ldf',
MOVE 'db_fg1_data' TO 'c:\db_fg1_data_a.ndf'
--恢复日志
RESTORE LOG db_a FROM DISK='c:\db_log.bak' WITH RECOVERY
--显示恢复结果
SELECT COUNT(*) FROM db_a.dbo.ta
/*--结果
-----------
0
（所影响的行数为 1 行）
--*/
GO
--因为只恢复了文件组db_fg1,位于文件组db_fg2上的表tb无法被访问
SELECT COUNT(*) FROM db_a.dbo.tb
/*--将收到错误信息
警告: 查询处理器无法生成计划，因为表 'db_a.dbo.tb' 已标记为 OFFLINE。
--*/
GO
--删除测试
DROP DATABASE db,db_a
3)仅有日志文件的数据修复处理示例
USE master
--创建数据库
CREATE DATABASE db
ON PRIMARY(
NAME='db_data',
FILENAME= 'c:\db_data.mdf')
LOG ON(
NAME='db_log',
FILENAME ='c:\db.ldf')
GO
--创建表
CREATE TABLE db.dbo.ta(id int)
INSERT db.dbo.ta SELECT id FROM sysobjects
GO
--做文件组备份
BACKUP DATABASE db TO DISK='c:\db.bak' WITH FORMAT
--备份后,再做数据处理
CREATE TABLE db.dbo.tb(id int)
INSERT db.dbo.tb SELECT id FROM sysobjects
GO
/*--下面演示了破坏数据文件的处理，这些操作在操作系统中进行
1. 停止SQL Server服务(msqlserver服务)
2. 删除文件 c:\db_data.ndf (模拟破坏)
3. 重新SQL Server服务,此时数据库DB置疑
--*/
GO
--下面演示了如何恢复数据
--首先要备份当前日志
BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT,NO_TRUNCATE
--利用文件组备份恢复破坏的文件
RESTORE DATABASE db FROM DISK='c:\db.bak' WITH NORECOVERY
--还原到日志点
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
--显示已经恢复的数据
SELECT COUNT(*) FROM db.dbo.ta
SELECT COUNT(*) FROM db.dbo.tb
GO
--删除测试
DROP DATABASE db
4)部分数据文件损坏的修复处理示例
USE master
--创建数据库
CREATE DATABASE db
ON PRIMARY(
NAME='db_data',
FILENAME= 'c:\db_data.mdf'),
FILEGROUP db_fg1(
NAME = 'db_fg1_data',
FILENAME = 'c:\db_fg1_data.ndf'),
FILEGROUP db_fg2(
NAME = 'db_fg2_data',
FILENAME = 'c:\db_fg2_data.ndf')
LOG ON(
NAME='db_log',
FILENAME ='c:\db.ldf')
GO
--创建表
CREATE TABLE db.dbo.ta(id int) ON [PRIMARY]
CREATE TABLE db.dbo.tb(id int) ON db_fg1
CREATE TABLE db.dbo.tc(id int) ON db_fg2
INSERT db.dbo.tb SELECT id FROM sysobjects
GO
--做文件组备份
BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1.bak' WITH FORMAT
--备份后,再做数据处理
INSERT db.dbo.ta SELECT id FROM sysobjects
GO
/*--下面演示了破坏数据文件的处理，这些操作在操作系统中进行
1. 停止SQL Server服务(msqlserver服务)
2. 删除文件 c:\db_fg1_data.ndf (模拟破坏)
3. 重新SQL Server服务,此时数据库DB置疑
--*/
GO
--下面演示了如何恢复数据
--首先要备份当前日志
BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT,NO_TRUNCATE
--利用文件组备份恢复破坏的文件
RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:\db_fg1.bak' WITH NORECOVERY
--还原到日志点
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
--显示已经恢复的数据
SELECT COUNT(*) FROM db.dbo.tb
SELECT COUNT(*) FROM db.dbo.ta
GO
--删除测试
DROP DATABASE db
5)数据还原到指定时间点的处理示例
--创建测试数据库
CREATE DATABASE Db
GO
--对数据库进行备份
BACKUP DATABASE Db TO DISK='c:\db.bak' WITH FORMAT
GO
--创建测试表
CREATE TABLE Db.dbo.TB_test(ID int)
--延时1秒钟,再进行后面的操作(这是由于SQL Server的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败)
WAITFOR DELAY '00:00:01'
GO
--假设我们现在误操作删除了 Db.dbo.TB_test 这个表
DROP TABLE Db.dbo.TB_test
--保存删除表的时间
SELECT dt=GETDATE() INTO #
GO
--在删除操作后,发现不应该删除表 Db.dbo.TB_test
--下面演示了如何恢复这个误删除的表 Db.dbo.TB_test
--首先,备份事务日志(使用事务日志才能还原到指定的时间点)
BACKUP LOG Db TO DISK='c:\db_log.bak' WITH FORMAT
GO
--接下来,我们要先还原完全备份(还原日志必须在还原完全备份的基础上进行)
RESTORE DATABASE Db FROM DISK='c:\db.bak' WITH REPLACE,NORECOVERY
GO
--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早
DECLARE @dt datetime
SELECT @dt=DATEADD(ms,-20,dt) FROM # --获取比表被删除的时间略早的时间
RESTORE LOG Db FROM DISK='c:\db_log.bak' WITH RECOVERY,STOPAT=@dt
GO
--查询一下,看表是否恢复
SELECT * FROM Db.dbo.TB_test
/*--结果:
ID
-----------
（所影响的行数为 0 行）
--*/
--测试成功
GO
--最后删除我们做的测试环境
DROP DATABASE Db
DROP TABLE #
6)数据还原到指定标记点的处理示例
USE master
--创建并完全备份数据库
CREATE DATABASE db
BACKUP DATABASE db TO DISK='c:\db.bak' WITH FORMAT
GO
--标记事务处理
BEGIN TRANSACTION Tran1 WITH MARK
CREATE TABLE db.dbo.tb(id int)
COMMIT TRAN Tran1
--事务完成后插入数据
INSERT db.dbo.tb SELECT id FROM sysobjects
GO
--还原数据到事务标记 Tran1 前
BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT
DROP DATABASE db
RESTORE DATABASE db FROM DISK='c:\db.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH STOPBEFOREMARK='Tran1'
SELECT COUNT(*) FROM db.dbo.tb
/*--事务标记 Tran1 前没有创建表,所以会收到错误信息
对象名 'db.dbo.tb' 无效。
--*/
GO
--还原数据库到事务标记 Tran1 后
DROP DATABASE db
RESTORE DATABASE db FROM DISK='c:\db.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH STOPATMARK='Tran1'
SELECT COUNT(*) FROM db.dbo.tb
/*--结果
-----------
0
（所影响的行数为 1 行）
--*/
GO
--删除测试
DROP DATABASE db
7)利用日志备份实现双服务器方案的处理示例
/*--说明：
下面的代码演示了如何利用日志还原功能，将主数据库中的数据变化及时反馈到备用数据库中
备用数据库的数据可以随时用于查询，但不能被更新（备用数据库只读）。
--*/
--首先,创建一个演示用的数据库(主数据库)
CREATE DATABASE Db_test
ON
( NAME = Db_test_DATA,
FILENAME = 'c:\Db_test.mdf' )
LOG ON
( NAME = Db_test_LOG,
FILENAME = 'c:\Db_test.ldf')
GO
--对数据库进行备份
BACKUP DATABASE Db_test TO DISK='c:\test_data.bak' WITH FORMAT
GO
--把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步)
RESTORE DATABASE Db_test_bak FROM DISK='c:\test_data.bak'
WITH REPLACE,STANDBY='c:\db_test_bak.ldf'
,MOVE 'Db_test_DATA' TO 'c:\Db_test_data.mdf'
,MOVE 'Db_test_LOG' TO 'c:\Db_test_log.ldf'
GO
--启动 SQL Agent 服务
EXEC master..xp_cmdshell 'net start sqlserveragent',no_output
GO
--创建主服务器数据训与备用服务器数据库之间同步的作业
DECLARE @jogid uniqueidentifier
EXEC msdb..sp_add_job
@job_id = @jogid OUTPUT,
@job_name = N'数据同步处理'
--创建同步处理步骤
EXEC msdb..sp_add_jobstep
@job_id = @jogid,
@step_name = N'数据同步',
@subsystem = 'TSQL',
@command = N'
--主数据库中进行日志备份
BACKUP LOG Db_test TO DISK=''c:\test_log.bak'' WITH FORMAT
--备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化
--实际应该时主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立，并且备份文件应该放在主服务器和备用都能访问的共享目录中
RESTORE LOG Db_test_bak FROM DISK=''c:\test_log.bak'' WITH STANDBY=''c:\test_log.ldf''',
@retry_attempts = 5,
@retry_interval = 5
--创建调度(每分钟执行一次)
EXEC msdb..sp_add_jobschedule
@job_id = @jogid,
@name = N'时间安排',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0x4,
@freq_subday_interval=1,
@freq_recurrence_factor=1
-- 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_id = @jogid,
@server_name = N'(local)'
GO
--通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成
--下面开始测试是否能实现同步
--在主数据库中创建一个测试用的表
CREATE TABLE Db_test.dbo.TB_test(ID int)
GO
--等待1分钟30秒（由于同步的时间间隔设置为1分钟,所以要延时才能看到效果）
WAITFOR DELAY '00:01:30'
GO
--查询一下备用数据库,看看同步是否成功
SELECT * FROM Db_test_bak.dbo.TB_test
/*--结果:
ID
-----------
（所影响的行数为 0 行）
--*/
--测试成功
GO
--最后删除所有的测试
DROP DATABASE Db_test,Db_test_bak
EXEC msdb..sp_delete_job @job_name=N'数据同步处理'
GO
/*===========================================================*/
/*--服务器档机处理说明
使用这种方式建立的数据库同步，当主数据库不可用时（例如，主数据库损坏或者停机检修）
可以使用以下两种方法使备用数据库可用。
--*/
--1. 如果主数据库损坏，无法备份出最新的日志，可以直接使用下面的语句使备用数据库可读写（丢失最近一次日志还原后的所有数据）。
RESTORE LOG Db_test_bak WITH RECOVERY
--2. 如果主数据库可以备份出最新日志，则可以使用下面的语句。
--先备份主数据库的最新的事务日志
BACKUP LOG Db_test TO DISK=''c:\test_log.bak'' WITH FORMAT
--再在备用数据库中恢复最新的事务日志，并且使备用数据库可读写（升级为主数据库）
RESTORE LOG Db_test_bak FROM DISK='c:\test_log.bak'
8)在现有数据库上还原时的数据文件处理示例
--创建一个测试数据库
CREATE DATABASE db
ON(
NAME=db,
FILENAME='c:\db.mdf')
LOG ON(
NAME=db_log,
FILENAME='c:\db.ldf')
--备份并删除测试数据库
BACKUP DATABASE db TO DISK='c:\a.bak' WITH FORMAT
DROP DATABASE db
--创建一个文件结构相同,但物理文件位置不同的数据库
CREATE DATABASE db
ON(
NAME=db,
FILENAME='d:\db.mdf')
LOG ON(
NAME=db_log,
FILENAME='d:\db.ldf')
--在新建的数据库上强制还原备份
RESTORE DATABASE db FROM DISK='c:\a.bak' WITH REPLACE
--查看还原后的文件位置
SELECT name,filename FROM db.dbo.sysfiles
/*--结果(还原后文件被移动到新建数据库时指定的对应文件)
name filename
------------ -----------
db d:\db.mdf
db_log d:\db.ldf
--*/
--删除测试
