SQL SERVER 数据备份还原方法

目录
    SQL Server数据文件和日志文件介绍
    数据库备份概述
    选择备份策略和恢复模式
    简单恢复模式下的备份
    完整恢复模式下的备份
    选择数据库还原方案
    系统数据库备份与还原
    数据库损坏检查和修复指令 DBCC checkdb
    日志文件和数据文件损坏解决办法

SQL Server数据文件和日志文件介绍
    sql server 数据库有两种类型的数据文件
        主文件        .mdf    数据库的起点，存储指向数据库中其它文件的路径
        辅助文件    .ndf    除了主数据文件以外的所有其他数据文件都是辅助数据文件，辅助文件可以将用户数据扩展到多块磁盘上。
        事务日志    .ldf    日志文件中会记录对数据库进行的操作，以便于还原数据库。
    
    数据文件的存储结构
        多页（sql server 里一个页 8K ,首页用于存放有关系统信息，如页类型，页的可用空间等） 组成 区，
        多区组成段
        多段组成表空间
        多个表空间 组成 数据文件 
            
    不同类型的数据，存储在不同类型的页面
        数据文件中的各种页类型
            
            页类型                                        内容
            数据                                        包含数据行中除 text,next和image数据外的所有数据文件
            索引                                        索引项
            文本/图像                                    text,ntext和image数据
            全局分配映射表、辅助全局分配映射表            有关已分配的扩展盘分区信息
            页的可用空间等）                            有关页上可用空间的信息
            索引分配映射表                                有关表或索引所使用的扩展盘区的信息
            大容量更改映射表                            有关自上次执行 BACKUP LOG 语句后大容量操作所修改的扩展盘区的信息    
            差异更改映射表                                有关自上次执行 BACKUP DATABASE 语句后更改的扩展盘区的信息。
            
    在一个数据库里，绝大多数页都是数据页，索引页或者text/image类型，而在一个数据文件的开头，则分布了像GAM(全局分配映射表)、IAM(索引分配映射表) SGAM(共享全局分配映射表）、PFS（页可用空间）这样的管理页面。
    
    日志文件：
        日志文件是以事务为单位记录数据库的每一更新活动的文件。
        根据日志文件中记录事务的结束状态，可以将事务分为圆满事务和夭折事务。
            圆满事务：指日志文件中记录了事务的commit标识，说明日志中已经完整地记录下事务所有的更新活动。根据日志就能把事务重新执行一遍。
            
            夭折事务：指日志文件中只有事务的开始标识，而无commit标识，说明对事务更新活动的记录是不完整的，无法根据日志来重现事务，为保证事务的原子性，应该撤销这样的事务。
        
    预定式日志（Write-Ahead Logging(WAL)）    
        在内存中被更新数据写入磁盘之前，要保证对就日志信息已经写入磁盘，存入日志文件。
        事务日志并不是一步步写入磁盘，而是首先写入缓冲区后，一次性写入日志到磁盘，这样既能在日志写入磁盘这块减少IO，还能保证日志序列号（log sqeuence number，简称：LSN）的顺序
        
        基本的恢复操作：对圆满事务所做过的修改操作应执行redo操作，即重新执行该操作，修改对象被赋予新记录值，对夭折事务所做过的修改操作应执行undo操作，即撤消操作，修改对象被赋予旧记录。
        
        当遇到commit时，只会将缓冲区的日志记录写入磁盘中的日志文件。
        直到checkpoint或都lazy writer时，才会将缓冲区的数据写入磁盘。
    
    Lazy Write和CheckPoint的区别
        Lazy Write 存在的目的是对缓冲区进行管理，当缓冲区达到某一临界值时，Lazy Write 会将缓冲区内的脏页存入磁盘文件中。而将末修改的页释放并回收资源。
        
        checkpoint存在的意义是减少服务器的恢复时间（Recovery Time）
        检查点（checkpoints）机制。它的主要作用就是保证在检查点时刻外存上的日志文件和数据文件的内容完全一致。
        
        sql server 可以在SSMS中对恢复间隔参数的配置设置 checkpoint 的间隔
            服务器属性》数据库设置》恢复》恢复间隔（分钟）：

    SQL Server事务日志维护知识
        如果日志文件缺失或者损坏，将等同于数据库损坏。
        多个日志文件不会提高性能（因为日志文件是线性写入）
        如果日志文件在，数据文件损坏，数据还能恢复到故障点（之前有做过一次完整备份，数据库的恢复模式是完整）
        对事务日志的日常备份工作可以有效的防止日志文件过分消耗磁盘的空间。
        
DBA在数据库备份工作上面临的挑战
    如何减少备份恢复时间
    如何将数据库恢复到想要的时间点
    如何迁移数据库到一台新的机器 
    应该选择什么样的备份策略

数据库备份概述
    根据每次备份的目标不同，分为数据备份和日志备份
    从是否拷贝所有的数据来分，数据备份又可以分完整备份和差导备份
    copy-only仅复制备份，不破坏现有的备份策略
    尾日志备份，尾日志就是从最后一个事务日志备份到发生故障的那一时刻的活动日志，技巧就是使用NO_TRUNCATE选项，即使数据文件不存在也能进行日志备份。
    
    做数据恢复之前，应该最先备份尾日志，不然会丢失数据。
    
如何选择备份策略和还原模式
    先问自己几个问题：
        能容忍多长时间的数据丢失
        准备投入多少人力物力来做数据库备份与恢复策略。
        是否需要做日志备份。
        
恢复模式：完整、大容量日志、简单 
    简单：
        不备份日志
        自动回收日志空间
    
    完全：
        需要日志备份
        不会由于数据文件丢失或损坏而造成信息丢失
        可以还原到任意时刻
    
    大容量日志：
        不能总保证日志备份成功，不推荐在生产环境下使用
        如果通过大容量机制定期导入数据，则可以临时数据库的恢复模式改为大容量日志模式，以获得更好的大容量负载性能。 
    

简单恢复模式下的备份
    简单恢复模式下，不能做日志备份
    数据库备在遇到故障时需要恢复的话，不能够恢复到故障的那个点上。
    差异备份与完整备份结合应用，并适当提高差异备份的频率，可以降低因没有备份事务日志所造成的风险，但是这只能起到降低的作用，而无法完全避免。
    
        
完整恢复模式下的备份
    选取完整恢复模式，就可以使用日志备份，完整恢复模式能使数据库恢复到故障时间点。
    建议经常执行日志备份，将工作丢失的风险限定在业务要求所允许的范围内。
    出现故障后，尝试备份尾日志，如果尾日志备份成功，则数据更新不会丢失。

以下角色可以执行数据库备份操作
    sysadmin
    db_owner
    db_backupoperator
    
数据库的差异备份和增量备份的区别：
    差异备份：备份自上一次完全备份之后有变化的数据。
    增量备份：备份自上一次备份（包含完全备份、差异备份、增量备份）之后有变化的数据。
    
    增量备份与差异备份还原时的区别：
    差异备份还原：需要第一次 完全备份 + 最后一次差异备份 + 尾日志 
    增量备份还原：需要第一次 完全备份 + 增量备份1 +增量备份2+.....+ 最后一次增量备份n + 尾日志。
    
    增量备份和差异备份的区别：

　　在Windows中一般都三种备份种类：完全备份、差异备份、增量备份。

　　完全备份：

　　备份全部选中的文件夹，并不依赖文件的存档属性来确定备份那些文件。（在备份过程中，任何现有的标记都被清除，每个文件都被标记为已备份，换言之，清除存档属性）。

　　差异备份：

　　差异备份是针对完全备份：备份上一次的完全备份后发生变化的所有文件。（差异备份过程中，只备份有标记的那些选中的文件和文件夹。它不清除标记，既：备份后不标记为已备份文件，换言之，不清除存档属性）。

　　增量备份：

　　增量备份是针对于上一次备份（无论是哪种备份）：备份上一次备份后，所有发生变化的文件。（增量备份过程中，只备份有标记的选中的文件和文件夹，它清除标记，既：备份后标记文件，换言之，清除存档属性。）

　　不同备份类型可以存在一定组合，参考示例如下：

　　完全备份和差异备份：

　　在星期一进行完全备份，在星期二至星期五进行差异备份。如果在星期五数据被破坏了，则你只需要还原星期一完全的备份和星期四的差异备份。这种策略备份数据需要较少的时间，但还原数据使用较少的时间。

　　完全备份和增量备份：

　　在星期一进行完全备份，在星期二至星期五进行增量备份。如果在星期五数据被破坏了，则你需要还原星期一正常的备份和从星期二至星期五的所有增量备份。这种策略备份数据需要较少的时间，但还原数据使用较多的时间

        
选择数据库还原方案演示
    创建数据 adventureworks
        create database adventureworks
        go
        
    使用 testdb数据库
        use adventureworks
        go 
    
    创建表
        create table testtable
        (number int,
        name varchar(50)
        )
    
    查看到的记录条数
        SELECT COUNT(*) AS toal FROM usertb
        GO 
    
    使用脚本模拟连接插入数据
        insert into testtable values(1,'aaaa')
            go
    
    做一个完整备份（注：需要在相应路径创建目录）
        backup database adventureworks 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='AdentureWorks')
        order by s.backup_finish_date desc;

    再插入数据，再做日志备份
        insert into testtable values(2,'bbbb')
            go
        
        SELECT COUNT(*) FROM testtable;
        
        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
    
    做一个差异备份（with differential 表示差异备份）
        backup database adventureworks to disk='c:\temp\advdiff4.bak' with differential
            go   
    
    再使用数据库备份文件还原（可以图形操作，注意，如果用命令操作，得先备份尾日志）
        
    在BACKUP DATABASE命令中没有什么新的，除了在WITH条件从句里包含了COMPRESSION关键字。这告诉SQL Server压缩这个数据库备份，因为默认情况下压缩是关闭的。
    
    不压缩备份
    DECLARE @timediff DATETIME
    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　
    
    
    压缩备份
    压缩备份：当前解决方案
        1.在备份过程中压缩数据
        2.备份之后进行压缩，这个方案要求额外的存储空间和时间。
        
    SET @timediff=GETDATE()
    BACKUP DATABASE AdventureWorks
    TO DISK='C:\temp\SQLDataAdventureWorks_compressed.bak'
　　WITH COMPRESSION

　    SELECT DATEDIFF(ms,@timediff,GETDATE()) AS compressed_time_in_ms
    
    备注：无法通过早期版本的SQL server进行读取无法与末压缩的备份在同一媒体中共存SQL server 2008版本以后的功能。
    
    
    镜像备份媒体
        镜像备份媒体通过提供冗余来提高备份的可靠性。
    
        BACKUP DATABASE northwind
            TO DISK='c:\temp\northwind-A.bak'
            MIRROR TO DISK='d:\temp\northwind-B.bak'
            WITH FORMAT, INIT
            GO 
    
    备份加密 
        在SQL Server2012之前的版本中，BACKUP语句中，可以定义备份集密码，使用密码可以防止可利用SQL Server工具末经授权地执行还原操作，此密码仅仅是给备份附加了一个密码，并没有对备份数据加密。
        
        透明数据加密TPE功能，但它是加密整个数据库，从而使得备份也加密
        
        在SQL Server2014 版本中，如果使用SQL Server2014的原生备份加密，则无需对整个数据库进行加密，备份仅仅在被写入磁盘之前被加密，原生备份加密如果结合备份压缩功能，那么先会进行压缩，再加密，从而得到非常高的压缩比率。
        CERTIFICATE :证书
        
        
        USE master
        GO 
        先创建一个加密证书
        CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mssql1234@slh'
        CREATE CERTIFICATE SampleDB_Backup_Certificate
            WITH SUBJECT = 'sample_backu_cert'
            GO
        
        备份证书
        BACKUP CERTIFICATE SampleDB_Backup_Certificate TO FILE='c:\temp\SampleDB_Backup_Certificate.cer'
    
    检查备份文件是否有效
    RESTORE VERIFYONLY FROM DISK='c:\temp\northwind-A.bak'    
        GO 
        
    检查备份文件头文件信息
    RESTORE HEADERONLY FROM DISK='c:\temp\northwind-A.bak'    
        GO
    
    检查备份文件历史物理信息
    RESTORE FILELISTONLY FROM DISK='c:\temp\northwind-A.bak'    

还原
还原数据库备份文件到新的位置
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'

日志文件和数据文件损坏解决办法
1.备份分为，全备、增量备份、日志备份（只有在完整模式和大容量模式下才能日志备份）、数据分离

实际操作
（在实际生产应用中）一般都是全备+增量备份+日志备份 

建立一个备份设备
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

大中型数据库备份还原
    恢复模式设置成完整--能使用事务日志恢复到出故障那个时间点。
    备份策略 -- 完整备份 +　事务日志备份　
    
备份步骤：
    
    做全备
        BACKUP DATABASE 数据库名 TO DISK='c:\temp\adv1.bak'
    做第1次日志备份 
        BACKUP LOG 数据库名 TO DISK='c:\temp\adv1.bak'
    
    做第2次日志备份 
        BACKUP LOG 数据库名 TO DISK='c:\temp\adv1.bak'
    .....
    做第n次日志备份 
        BACKUP LOG 数据库名 TO DISK='c:\temp\adv1.bak'    
    
还原步骤：
    先做尾日志日志备份
        BACKUP LOG 数据库名 TO DISK='c:\temp\adv1.bak' WITH NORECOVERY 
    
    查看备份文件里有那些文件或类型
    Restore headeronly from disk='c:\temp\adv1.bak'  
    
    先使用完整备份文件还原
    RESTORE DATABASE FROM DISK='c:\temp\adv1.bak'
    
    使用第一个日志还原
    RESTORE DATABASE FROM DISK='c:\temp\adv1.bak' WITH FILE=1,REPLACE,NORECOVERY
        
        REPLACE参数:重置，覆盖现有的
        NORECOVERY参数:设置数据还原后先不回滚（如果不加这参数，还原完成就直接回滚没有完成的事务）
    
    再用第二个日志还原
    RESTORE DATABASE FROM DISK='c:\temp\adv1.bak' WITH FILE=2,REPLACE,NORECOVERY
    。。。。。
    RESTORE DATABASE FROM DISK='c:\temp\adv1.bak' WITH FILE=n
    
    注：还原时一定要存在备份时间相同的路径
    
用命令恢复到某一时间点
        Restore database 数据库名 from  disk=’备份文件路径bak’ with file=1,replace,norecovery
        再用日志还原
        Restore log 数据库名 from disk=’备份文件路径bak’ with  file=2,recovery,stopat=’还原的时间‘
        注：时间点格式 ‘2018-01-10 14:33:00’

8.    在数据库文件坏掉的情况下， 备份日志 还原到故障点
先完整备份，
再插入记录，再日志备份
停掉数据库服务，修改数据库文件，模拟数据库坏掉的情况，再启动数据库服务，再打开数据库，发现数据库已坏掉

在数据库损坏后备份日志
    Backup lo 数据库名  to disk=’备份文件路径bak’ with no_truncate
        no_truncate : 数据库损坏后再备份日志。
Restore headeronly from disk=’备份文件路径bak’     查看备份文件里有那些文件或类型

再还原到故障点
        Restore database 数据库名 from  disk=’备份文件路径bak’ with file=1,replace,norecovery
        Restore log 数据库名 from  disk=’备份文件路径bak’ with file=2, norecovery
Restore log 数据库名 from  disk=’备份文件路径bak’ with file=3

    
    清空表 truncate