SQLSERVER 备份还原1

SQLSERVER 备份还原

  参考文献：

--概念讲解：http://www.jb51.net/article/31038.htm
--事务日志概念：http://www.cnblogs.com/CareySon/archive/2012/02/13/2349751.html（非常详细）

上面这些文章已经将得很详细了，小弟只是做一些总结和具体的实例演示；你可以先看看大神的，再看看小弟的，都会有收货

sqlserver 的备份可以很简单，也可以很难，就看你对他理解多少了!

A.恢复模式

              1.简单恢复模式（将不产生事务日志，无法进行事务日志备份,在简单恢复模式下，日志仅仅是为了保证SQL Server事务的ACID。并没有恢复数据的功能）

              2.完整恢复模式(完整备份并不像其名字“完整”那样备份所有部分，而是仅备份数据库本身，而不备份日志(虽然仅仅备份少量日志用于同步）)

              3.大容量日志恢复模式 

SQL语句切换模式:

   
   ALTER DATABASE NAME SET RECOVERY SIMPLE        --简单模式

   ALTER DATABASE NAME SET RECOVERY FULL          --完整模式
   
   ALTER DATABASE NAME SET RECOVERY BULK_LOGGED   --容量模式

B.各种备份  

              1.完整备份 （创建完一个新数据库之后，强烈建议甚至强制做一次完整备份）

              2.差异备份

              3.事务日志备份

              4.文件和文件组备份

 

使用下面的语句可以看出，那些数据库做过备份

  SELECT * FROM MSDB.dbo.BACKUPSET

 C.参数讲解

 

              先讲解一些基本的语法和参数使用（主要是with 后面的参数）

               { NOINIT | INIT }

                控制备份操作是追加到还是覆盖备份介质中的现有备份集。 默认为追加到介质中最新的备份集 (NOINIT)。

 

  D.具体实例

 1.完整模式下使用事务日志备份和还原

CREATE DATABASE MC
  GO
  USE MC
  GO
 
  ALTER DATABASE MC SET RECOVERY FULL
  
  BACKUP DATABASE MC
  TO DISK='F:\TEST\MC.BAK'
 
  
   CREATE TABLE STU(
   ID INT IDENTITY(1,1),
   NAME VARCHAR(100)
   )
   GO
  
   INSERT INTO STU VALUES('LIUQI1')
   INSERT INTO STU VALUES('LIUQI2')
   INSERT INTO STU VALUES('LIUQI3')
   
   --执行一次事务日志备份
   BACKUP LOG MC
   TO DISK='F:\TEST\LOG\MC.TRN'
   
   --然后我们再插入第二数据
    INSERT INTO STU  VALUES('SECONDE')
    INSERT INTO STU  VALUES('SECONDE')
    INSERT INTO STU  VALUES('SECONDE')
    GO
    
    --进行第二次事务日志备份；
    BACKUP LOG MC
    TO DISK='F:TEST\LOG\MC1.TRN'
    
    --
    SELECT * FROM STU
    TRUNCATE TABLE STU
    SELECT * FROM STU
    --突然间，数据被全部删除了，幸好老子又备份；
    
    
    --先分离数据库mc 如果觉得无用，可以直接删除
    --先恢复完整备份的数据库；
    --USE master 
    --GO
    --DROP DATABASE MC
    
    RESTORE DATABASE NEW_MC1
    FROM DISK='F:\TEST\MC.BAK'
    WITH REPLACE,NORECOVERY
    
    RESTORE LOG NEW_MC1
    FROM DISK='F:\TEST\LOG\MC.TRN'
    WITH NORECOVERY

    
    RESTORE LOG NEW_MC1
    FROM DISK='F:\TEST\LOG\MC1.TRN'
    WITH RECOVERY
    
    USE NEW_MC1 
    GO
    SELECT * FROM STU

 2.完整模式下使用事务日志备份和还原（将数据库恢复到指定的时间点）

--1.首先创建一个数据库
CREATE DATABASE MC67
GO

--2.设置数据库的恢复模式为：完整。
ALTER DATABASE MC67 SET RECOVERY FULL

--3.进行一次完整备份(这个是必须的)
BACKUP DATABASE MC67
TO DISK='F:\TEST\MC67.BAK'
WITH INIT
GO

USE MC67
GO
--4.创建表，并插入测试数据；
CREATE TABLE STU(
 ID INT ,
 NAME VARCHAR(50)
)
GO

INSERT INTO STU VALUES(1,'刘奇1')
INSERT INTO STU VALUES(2,'刘奇2')
INSERT INTO STU VALUES(3,'刘奇3')
INSERT INTO STU VALUES(4,'刘奇4')
GO

--5.现在延迟五秒种
WAITFOR DELAY '00:00:05'

SELECT * FROM STU

--6.现在某个时间不小小心误删了数据
TRUNCATE TABLE STU
GO
SELECT GETDATE() --2015-10-06 15:05:17.380

--7.备份事务日志
BACKUP LOG MC67
TO DISK='F:\TEST\MC67_LOG.TRN'
WITH FORMAT

--8.先恢复原先的数据库；（也就是备份的）
USE master
GO
SP_DETACH_DB MC67,TRUE  ----分离数据库
GO

RESTORE DATABASE NEWMC67
FROM DISK='F:\TEST\MC67.BAK'
WITH REPLACE, NORECOVERY  --这里要使用NORECOVERY

--9.现在来恢复事务日志；
RESTORE LOG NEWMC67
FROM DISK='F:\TEST\MC67_LOG.TRN'
WITH STOPAT='2015-10-06 15:04'
--10测试
USE NEWMC67
GO
select * FROM STU

 3.简单模式下的差异备份和还原 （有缺陷）

   这种方式是有缺陷的,如：我们在每周一0点做一次完整备份，在周三0点和周五0点分别做差异备份。在简单恢复模式下，如果周六数据库崩溃。我们的恢复计划只有根据周一0点的做的完整备份恢复后，再利用周五0点的差异备份进行恢复.而周五0点之后到服务器崩溃期间所有的数据将会丢失。

   

 

 4.完整模式下的差异备份和还原

  这里值的注意是：如果进行了两次全备份，mc67_full1.bak 接着又进行一次全备份 mc67_full2.bak，而你的差异备份是在mc67_full2.bak之后额的，那么在进行还原的时候，只能使用mc67_full2.bak进行差异还原。，总之，差异备份是基于前一次的全备份而进行的差异备份

USE MASTER
GO

CREATE DATABASE MC67
GO

--USE MC67
--GO

CREATE TABLE SHOW
(
  ID INT IDENTITY(1,1),
  INFO VARCHAR(100)
)

--进行一次完整备份
BACKUP DATABASE MC67 TO DISK='F:\TEST\MC67_FULL.BAK'

--第一次添加数据
      INSERT INTO SHOW VALUES('第一次添加数据')
      INSERT INTO SHOW VALUES('第一次添加数据')
      INSERT INTO SHOW VALUES('第一次添加数据')
--第一次差异备份
     BACKUP DATABASE MC67 TO DISK='F:\TEST\MC67_DIFF1.BAK'
     WITH DIFFERENTIAL
--第二次添加数据
      INSERT INTO SHOW VALUES('第二次次添加数据')
      INSERT INTO SHOW VALUES('第二次次添加数据')
      INSERT INTO SHOW VALUES('第二次次添加数据')
--第二次差异备份
       BACKUP DATABASE MC67 TO DISK='F:\TEST\MC67_DIFF2.BAK'
       WITH DIFFERENTIAL
       
 --还原；第一差异备份的
   USE master
   GO
   RESTORE DATABASE MC67_1 FROM DISK='F:\TEST\MC67_FULL.BAK' 
   WITH NORECOVERY,
   MOVE 'MC67' TO 'F:\TEST\ONE\MC67.MDF',
   MOVE 'MC67_LOG' TO 'F:\TEST\ONE\MC67_LOG.LDF'
   
   
   RESTORE DATABASE MC67_1 FROM DISK='F:\TEST\MC67_DIFF1.BAK'
   WITH RECOVERY 
  
   USE MC67_1 
   GO
   SELECT * FROM SHOW
   --结果：
   /* 
     1    第一次添加数据
     2    第一次添加数据
     3    第一次添加数据
   
   */

   --还原第二次差异备份的数据
   USE master
   GO
   RESTORE DATABASE MC67_2 FROM DISK='F:\TEST\MC67_FULL.BAK' 
   WITH NORECOVERY,
   MOVE 'MC67' TO 'F:\TEST\TWO\MC67.MDF',
   MOVE 'MC67_LOG' TO 'F:\TEST\TWO\MC67_LOG.LDF'
   
   
   RESTORE DATABASE MC67_2 FROM DISK='F:\TEST\MC67_DIFF2.BAK'
   WITH RECOVERY 
   
   USE MC67_2
   GO
   SELECT * FROM SHOW
   --结果:
   /*
    1    第一次添加数据
    2    第一次添加数据
    3    第一次添加数据
    4    第二次次添加数据
    5    第二次次添加数据
    6    第二次次添加数据
   
   */