SQL SERVER 数据备份恢复 技巧

完整备份数据库
backup database 需要备份数据库名 to disk='c:\temp\advfull.bak'

查看数据库历史备份信息
select  s.backup_finish_date,s.type,y.physical_device_name
from msdb..backupset as s inner join
msdb..backupfile as f on f.backup_set_id=s.backup_set_id inner join
msdb..backupmediaset as m on s.backup_set_id = m.media_set_id inner join
msdb..backupmediafamily as y on m.media_set_id=y.media_set_id
where(s.database_name='需要查询数据库名')
order by s.backup_finish_date desc;

create table testtable
(number int,
name varchar(50)
)
go
insert into testtable values(1,'aaaa')
go
backup log adventureWorks to disk='c:\temp\advlog2.bak'
go
insert into testtable values(2,'bbbb')
go
backup log adventureWorks to disk='c:\temp\advlog3.bak'
go
insert into testtable values(3,'cccc')
go
差导备份数据库
backup database adventureworks to disk='c:\temp\advdiff4.bak' with differential
go

镜像备份数据文件
backup database 备份数据库名
to disk=‘c:\temp\备份数据文件名A.back’
mirror to disk=‘d:\temp\备份数据文件名B.back’

验证数据文件的有效性
restore verifyonly from disk=‘c:\temp\备份数据文件名A.back’
go

验证数据备份文件的头信息
restore headeronly from disk=n'c:\temp\备份数据文件名A.back

验证数据备份文件物理信息
restore filelistonly from disk=n'c:\temp\备份数据文件名A.back

验证数据备份文件物理介质
restore labelonly from disk=n'c:\temp\备份数据文件名A.back

 

压缩备份

在BACKUP DATABASE命令中没有什么新的，除了在WITH条件从句里包含了COMPRESSION关键字。这告诉SQL Server压缩这个数据库备份，因为默认情况下压缩是关闭的。

DECLARE @time  DiffDATETIME
SET @timeDiff=GETDATE()
BACKUP DATABASE AdventureWorks
　　TO DISK='C:\temp\SQLDataAdventureWorks_uncompressed.bak'
　with init
SELECT DATEDIFF(ms,@timeDiff,GETDATE())  AS  uncompressed_time_in_ms　
SET @timeDiff=GETDATE()
BACKUP DATABASE AdventureWorks
TO DISK='C:\temp\SQLDataAdventureWorks_compressed.bak'
　　WITH COMPRESSION,INIT
　SELECT DATEDIFF(ms,@timeDiff,GETDATE()) AS compressed_time_in_ms

 

还原到故障点演示
CREATE DATABASE case1 ON  PRIMARY 
( NAME = N'case1', FILENAME = N'C:\case1\case1_data.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'case1_log', FILENAME = N'C:\case1\case1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

use case1
go
create table course
(cid char(5) primary key,
 cname varchar(20) not null)
go
insert course values('1001','课程1')
insert course values('1002','课程2')
insert course values('1003','课程3')
insert course values('1004','课程4')
insert course values('1005','课程5')

create table student
(stu_id int identity(1,1) primary key,
 cid char(5) references course(cid),
 score int null
)

建立一个备份设备

use master; 
go 
exec sp_addumpdevice 'disk', 'case1backup', 'c:\case1\case1.bak'
 --可以通过网络共享磁盘文件来创建备份设备

周一晚八点
backup database case1 to case1backup with name='case1 full backup'
--也可以不用备份设备，直接备份到磁盘文件
backup database case1  to disk='c:\case1\case1.bak' 

库有变化了

insert student values('1001',95)
insert student values('1004',90)
周二晚八点
backup database case1 to case1backup with differential,name='case1 diff backup'
库有变化了
insert student values('1005',80)

周三晚八点
backup database case1 to case1backup with differential,name='case1 diff backup'
库有变化了
insert student values('1003',84)
insert student values('1002',98)

周四，16：00崩溃了，主文件丢失，我这两条记录会不会丢掉

net stop mssqlserver
删掉case1库的数据文件,主文件所在盘坏掉了

net start mssqlserver

发现case1库置疑

能还原到哪里？ 

第一种方案：
周一的full backup+周三 differential backup
如果现在这样做，会丢掉一些数据

我们
backup  log case1 to case1backup with name='case1 log backup', no_truncate

/------------------/
 

 

还原数据库备份到新的位置
    restore filelistonly from disk='D:\share\adventureworks2014.bak'

restore database adventureworks2014 from disk = 'D:\share\adventureworks2014.bak'
with move 'adventureworks2014_data'     to 'c:\database\adventureworsk2014.mdf'
    ,move 'adventureworks2014_log' to 'c:\database\adventureworks.ldf'

 

数据库损坏修复
drop database testdb10
go
CREATE DATABASE TESTDB10
on
(
name=testdb10_db,
filename='c:\database\testdb10.mdf',
size=3mb
)
log on
(name=testdb10_log,
filename='c:\database\testdb10.ldf',
size=2mb
)
GO
USE TESTDB10
GO
CREATE TABLE TESTTABLE
(ID int,
NAME nvarchar(50)
)

 --建立两个索引，其中一个是聚集索引，另外一个是非聚集索引
CREATE CLUSTERED INDEX idx1 on TESTTABLE (ID)
CREATE INDEX idx2 on TESTTABLE(NAME)

 -- 插入300行数据
DECLARE @i INT
SET @i = 1
WHILE (@i <= 300)
BEGIN
INSERT INTO TESTTABLE VALUES(@i, ('name_'+ cast(@i as varchar)))
SET @i = @i + 1
END

-------

BEGIN TRANSACTION
UPDATE TESTTABLE SET NAME = 'xxxx' where ID = 295
CHECKPOINT
WAITFOR DELAY '0:10:0'
UPDATE TESTTABLE SET NAME = 'xxxx' where ID = 296
COMMIT TRANSACTION

请运行如下的脚本操作。第一个UPDATE语句是更新ID=295的数据，并且马上做一个CHECKPOINT的动作，这会使得数据的更新，立刻写入数据文件中。第二个UPDATE语句打算要更新ID=296的数据，这两个操作是放在同一个事务内进行的。但是在第一个UPDATE语句结束后，不等WAITFOR语句结束，请马上杀掉数据库服务进程(Sqlservr.exe)。这样，在数据文件里，ID=295的记录已经被修改，ID=296的记录还没被修改。如果日志文件不损坏，下次SQL Server重新启动时，SQL会发现这个做到一半的事物，将ID=295的记录修改回滚。

在这个测试里，我们编辑该数据库的日志文件，人为对数据库的日志文件造成损坏。当该数据库服务重新启动的时候，该数据库会处于RECOVERY_PENDING状态，查看数据库错误日志，有如下的错误，表明日志文件受损。

消息
错误: 824，严重性: 24，状态: 2。
消息
SQL Server detected a logical consistency-based I/O error: 校验和不正确(应为: 0xe00db0b3，但实际为: 0x6385313b). It occurred during a 读取 of page (2:0) in database ID 36 at offset 0000000000000000 in file 'c:\database\testdb10.ldf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

消息
错误: 5105，严重性: 16，状态: 1。
消息
A file activation error occurred. The physical file name 'c:\database\testdb10.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
消息
文件激活失败。物理文件名称'c:\database\testdb10.ldf'可能不正确。
消息
无法重新生成日志，原因是数据库关闭时存在打开的事务/用户，该数据库没有检查点或者该数据库是只读的。如果事务日志文件被手动删除或者由于硬件或环境问题而丢失，则可能出现此错误。

ALTER DATABASE TESTDB10 SET EMERGENCY
ALTER DATABASE TESTDB10 Rebuild LOG on 
(name=testdb10_log, filename='c:\database\testdb10.LDF')
ALTER DATABASE TESTDB10 SET MULTI_USER 

r
---repair database
ALTER DATABASE TESTDB10 set EMGERGENCY
ALTER DATABASE TESTDB10 set single_user with rollback immediate
GO
DBCC CHECKDB(TESTDB10, 'REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE TESTDB set multi_user

---------
use TESTDB10
SELECT * FROM TESTTABLE where ID=295 or ID=296

有一个要注意的地方，对数据库日志进行重建以后，我们会发现ID为295的NAME变成了xxxx，而ID为296的NAME依旧是name_296。这从数据库的逻辑上来讲是没有问题的。但是从应用的角度，可能会大有问题。应用程序可能有逻辑需求，要求ID为295和ID为296的名字要么同时改变，要么同时不改变，在应用程序中，我们开启了事务以确保这一点。但是经过日志重建后，这点不能得到保证了。
因此，重建数据库的日志文件，是迫不得已的办法，会破坏数据的一致性。还是建议从好的数据库备份中恢复数据，这样能保证数据在业务逻辑上的一致。

-------------

模拟数据库文件损坏的各种情形。新建测试数据库后，把TESTDB10下线，编辑该数据库的数据文件，以模拟该数据文件受损的情形。然后把该数据库上线，运行DBCC CHECKDB命令，会报告发现数据库受损。

消息
SQL Server detected a logical consistency-based I/O error: 校验和不正确(应为: 0xc6024699，但实际为: 0xd300bbee). It occurred during a 读取 of page (1:49) in database ID 36 at offset 0x00000000062000 in file 'c:\database\testdb10.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

错误: 928，严重性: 20，状态: 1。
消息
错误: 824，严重性: 24，状态: 2。
消息
During upgrade, database raised exception 945, severity 14, state 2, address 0000000000A98061. Use the exception number to determine the cause.

-------
TESTTABLE的 DBCC 结果。
消息 8928，级别 16，状态 1，第 1 行
对象 ID 2105058535，索引 ID 1，分区 ID 72057594038845440，分配单元 ID 72057594039894016 (类型为 In-row data): 无法处理页 (1:55)。有关详细信息，请参阅其他错误消息。
消息 8939，级别 16，状态 98，第 1 行
表错误: 对象 ID 2105058535，索引 ID 1，分区 ID 72057594038845440，分配单元 ID 72057594039894016 (类型为 In-row data)，页 (1:55)。测试(IS_OFF (BUF_IOERR, pBUF->bstat))失败。值为 12716041 和 -4。
消息 8980，级别 16，状态 1，第 1 行
表错误: 对象 ID 2105058535，索引 ID 1，分区 ID 72057594038845440，分配单元 ID 72057594039894016 (类型为 In-row data)。索引节点页 (1:80)，槽 0 指向子页 (1:55) 和上一子页 (0:0)，但未遇到这些页。
消息 8978，级别 16，状态 1，第 1 行
表错误: 对象 ID 2105058535，索引 ID 1，分区 ID 72057594038845440，分配单元 ID 72057594039894016 (类型为 In-row data)。页 (1:89) 缺少上一页 (1:55) 对它的引用。可能是因为链链接有问题。
对象 'TESTTABLE' 的 1 页中有 63 行。
CHECKDB 在表 'TESTTABLE' (对象 ID 2105058535)中发现 0 个分配错误和 4 个一致性错误。
CHECKDB 在数据库 'TESTDB10' 中发现 0 个分配错误和 4 个一致性错误。
---------------

use master
go
SP_CONFIGURE 'ALLOW UPDATES',1 
go
RECONFIGURE WITH OVERRIDE
go
ALTER DATABASE TESTDB10 SET EMERGENCY
go
ALTER DATABASE TESTDB10 set single_user with rollback immediate
GO

DBCC CHECKDB(TESTDB10, 'REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE TESTDB10 set multi_user
go