SQL Server 备份和还原全攻略

 

 

一、知识点

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

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

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

事务日志备份：在特定事务日志备份之前执行的完整数据库备份和上次差异备份（如果有）。在完整数据库备份之后执行的所有事务日志备份或在特定事务日志备份之前执行的差异备份（如果您还原了差异备份）。如果你设置了恢复模式为【简单】，你将无法使用【事务日志】备份。SQL Server 2000 和 SQL Server 2005： 创建事务日志备份，您必须使用完整恢复或大容量日志记录恢复模型。

部分备份：通过指定 READ_WRITE_FILEGROUPS 创建的备份称为“部分备份”。在简单恢复模式下，只允许对只读文件组执行文件组备份。还原的数据备份类型：数据库备份、部分备份或文件备份。对于数据库备份或部分备份，日志备份序列必须从数据库备份或部分备份的结尾处开始延续。对于一组文件备份，日志备份序列必须从整组文件备份的开头开始延续。

文件备份：“文件备份”包含一个或多个文件（或文件组）中的所有数据。

日志链：连续的日志备份序列称为“日志链”。日志链从数据库的完整备份开始。通常，仅当第一次备份数据库时，或者将恢复模式从简单恢复模式切换到完整恢复模式或大容量日志恢复模式之后，才会开始一个新的日志链。除非在创建完整数据库备份时选择覆盖现有备份集，否则现有的日志链将保持不变。在该日志链保持不变的情况下，便可从媒体集中的任何完整数据库备份还原数据库，然后再还原相应恢复点之前的所有后续日志备份。恢复点可以是上次日志备份的结尾，也可以是任何日志备份中的特定恢复点。

一个备份方案例子：某个站点在星期天晚上执行完整数据库备份。在白天每隔 4 小时制作一个事务日志备份集，并用当天的备份重写头一天的备份。每晚则进行差异备份。如果数据库的某个数据磁盘在星期四上午 9:12 出现故障，则该站点可以：

1)    备份当前事务日志；（已经出现故障了，如何备份当前事务日志？）

2)    还原从星期天晚上开始的数据库备份；

3)    还原从星期三晚上开始的差异备份，将数据库前滚到这一时刻；

4)    还原从早上 4 点到 8 点的事务日志备份，以将数据库前滚到早上 8 点；

5)    还原故障之后的日志备份。这将使数据库前滚到故障发生的那一刻。

 

二、还原步骤

创建一个叫TestBackup的数据库，创建一张叫Table1的表，这个时候进行一次完整备份，备份文件为：TestBackupDB-full.bak；接着创建表Table2后进行差异备份，备份文件为：TestBackupDB-diff.bak；接着创建表Table3后进行事务日志备份（如果数据库设置了恢复模式为【简单】，那么在备份类型选项中将看不到【事务日志】），备份文件为：TestBackupDB-log.bak；

创建一个叫TestBackup2的数据库，用于测试TestBackup数据库的备份文件的还原。

（图1：创建库结构）

（图2：备份类型）

       下面我们就可以对三个备份文件：TestBackupDB-full.bak、TestBackupDB-diff.bak、TestBackupDB-log.bak进行还原：

       步骤1：还原完整备份文件TestBackupDB-full.bak，选项如图4、图5所示，还原成功后数据列表就会如图6所示，这是因为恢复状态选项：不对数据库执行任何操作，不回滚未提交的事务。可以还原其他事务日志。(RESTORE WITH NORECOVERY)

（图3：进入SSMS还原）

（图4：还原常规）

（图5：还原选项）

（图6：完整备份还原）

步骤2：还原差异备份文件TestBackupDB-diff.bak，操作如步骤1所示，这个时候的数据库还是跟图6的状态一样的。

步骤3：还原事务日志备份文件TestBackupDB-log.bak，如图7进入事务日志的还原操作界面；看图8的选项中有指定事务的时间进行还原（还原过程中的恢复状态都是默认为RESTORE WITH RECOVERY，所以这里没有提及这个选项）。还原后的TestBackup2数据库，还原之后的数据库TestBackup2如图9所示。

（图7：进入事务日志）

（图8：事务日志）

（图9：还原后的数据库）

 

三、升级

通常来说文章写到这里就应该结束了，但是很幸运，再给你介绍一下如何在对表进行分区后的还原操作，从上面的操作来看只包括了mdf和ldf文件，但如果多了几个ndf文件，这些还原又一样吗？所以我称这部分的内容为升级。

情景一：如果本来就有对应的分区文件的，只要在还原的时候修改【还原为】的文件名就可以进行还原了。

情景二：如果刚刚新建了分区文件组和文件，这个时候接着还原备份就会出现图10的错误（不知道是不是在SQL Server 2005的问题）；要解决这个问题有两个方法，第一个：重启数据库服务再还原；第二个：设置数据库的【限制访问】设置为【Single】；

（图10：错误）

 

SQL Server 维护计划备份主分区

一、场景

经过一段时间表分区的实践，我们先对表进行分区（形成表分区模板）；表数据搬迁模板（迁移数据到新的分区表）；分区管理自动化（自动化进行交换分区）；详情请见：SQL Server 表分区实战系列（文章索引）

       再进一步延伸，我们就需要对这些做了表分区的库进行备份了，之前写过一篇博文：SQL Server 备份和还原全攻略，这里描述了MSSQL的一些备份概念，今天这里虽然没有用到，但是像差异备份在备份比较大的情况下使用就会有很好的效果。

       今天我们就来说说如何使用MSSQL的维护计划来备份表分区的。

假设这样一个场景：一个数据库现在已经几十G（如图1），但是占用主要空间的就是一两个表的数据（流水记录数据），其它的就是一些配置表，我们对这些配置表数据安全性要求比较高，而对流水数据比较低，那么我们有什么方案可以保证这个数据库的数据安全呢？

（图1）

 

二、方案

方案一：对于上面的场景，我们最简单、最合理的方案就是把这两个表PostSnapshot、PostLog分离出来作为一个新的数据库A，而配置表单独作为一个数据库B，这样的好处是很多的，这样对配置数据库B的备份就简单的多了，更重要的一点就是数据库A与B的读写也分离了（频繁读取配置数据库B和频繁写记录数据库A）

方案二：但是往往在很多情况是业务上不允许我们这样做，那还有没一个可以折中的办法呢？对的，这个折中的办法就是我今天想表述的内容了，我们先对这两个表PostSnapshot、PostLog进行表分区，剩下的配置表就依然还在主分区（Primary）里面，我们只需要备份主分区就可以达到备份配置表的目的了。

对于方案二，一个比较大的缺点就是在还原备份的时候，这两个表PostSnapshot、PostLog是用不了的，也删除不了，只能通过修改表名之后再创建两个新表，不过这个缺点对于这些备份来说可以忽略，因为我们重要的数据可以通过主分区的备份找回来就已经满足我们这类备份的目的了。

 

三、实践

(一) 使用【维护计划】->【维护计划向导】，在出现的窗体中需要注意一个选项，如图2所示，需要选择【每项任务单独计划】，这样才能在作业中看到不同的任务所对应的作业，这样的好处是可以单独执行某个作业。

（图2）

(二) 这个维护计划中包括了两个子计划：Subplan_Primary与Subplan_Primary_Save，Subplan_Primary的计划是每天晚上的1点钟，图3表述了作业的执行步骤与过程：

1)     首先是执行一段我们编写的T-SQL代码，如果这里不是需要进行分区备份，而是使用完整备份或者是差异备份的话，我们完全就不需要写T-SQL代码；

2)     接着删除备份的日志记录，只保留2周的记录；

3)     最后对备份的bak文件进行维护了，保留2周内的bak备份文件，2周之前的bak会自动被删除掉；

（图3）

(三) 我们的数据库模式设置为简单模式了，在这种状态下是无法对分区进行备份的，所以：

1)     首先要把数据库的模式设置为完整模式；

2)     接着使用T-SQL备份数据库的主分区；

3)     最后再把数据库的模式设置为简单模式；

使用备份还原数据库，还原后的数据库是完整模式的呢？还是简单模式的呢？当然是完整模式啦。

（图4）

(四) 在备份主分区的代码中，我们除了图4中把DISK的路径作为参数传进去执行外，我们还可以使用拼凑SQL的方式，如图5所示；

（图5）

(五) 可能很多人都不太理解为什么上面的子计划已经做了一次主分区的备份了，为什么还要在添加一个子计划呢？其实这个是为了管理上的方便，因为Subplan_Primary的备份间隔是每天，只保留2周内备份（14个bak文件），所以太久之前的备份就会丢失了，为了能保留尽可能久的备份，我们添加了Subplan_Primary_Save子计划，它每周只执行一次，保留4个月内的备份（16个bak文件）；这样要比100多个bak要节省很多的空间。（我们可以考虑每周拷贝一份Subplan_Primary中bak到其它服务器进行备份，这样就可以省略掉Subplan_Primary_Save）

Subplan_Primary与Subplan_Primary_Save的区别：

1)     在T-SQL里面保存bak的路径是不同的；

2)     文件命名中的日期格式不一样；

3)     执行计划频率不同；

4)     清除任务的保留时间不同；

5)     清除任务bak文件的路径不同；

（图6）

(六) 在测试过程中，为了看看维护计划中【清除维护任务】的效果，我们缩短了保留的天数，设置为5天，图7是执行作业前的bak文件列表，图8是设置为保留5天并执行作业后的bak文件列表。可以看到最久的一个bak文件被删除了。

（图7）

（图8）

 

四、部分T-SQL代码

--1设置完整模式

USE [master]

GO

ALTER DATABASE [Barefoot.Ant] SET RECOVERY FULL WITH NO_WAIT

GO

 

--2备份主分区

DECLARE

 @FileName VARCHAR(200),

 @CurrentTime VARCHAR(50)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

SET @FileName = 'F:\DBBackup\Ant_Primary\Ant_Primary_' + @CurrentTime

BACKUP DATABASE [Barefoot.Ant]

FILEGROUP='PRIMARY' TO DISK=@FileName WITH FORMAT

GO

 

--3设置简单模式

USE [master]

GO

ALTER DATABASE [Barefoot.Ant] SET RECOVERY SIMPLE WITH NO_WAIT

GO

 

--还原主分区

RESTORE DATABASE [TestAnt]

FILEGROUP='PRIMARY'

FROM DISK='F:\DBBackup\Ant_Primary\Ant_Primary_20110916000001.bak' WITH FILE = 1,

MOVE N'Barefoot.Ant' TO N'F:\DBBackup\TestAnt.mdf',

MOVE N'Barefoot.Ant_log' TO N'F:\DBBackup\TestAnt_log.ldf',

RECOVERY,REPLACE, STATS = 10

GO

 

使用T-SQL进行数据库备份并检查该备份文件是否存在且作出相应处理

(2010-11-07 15:48:32)

转载

标签： 杂谈

分类： SQLSERVER2008

Posted on 2010-10-02 09:46 小绿虫 阅读(140) 评论(0) 编辑 收藏 所属分类: sqlserver

USE  master
GO
EXEC  sp_addumpdevice  ' disk ' ,  ' AdvWorksData ' , 
' E:\开发软件\数据库\SQl2005示例数据库\AdvWorksData.bak '
BACKUP   DATABASE  AdventureWorks 
    TO  AdvWorksData

1.备份数据库语句：

EXEC sp_addumpdevice 'disk','数据库备份名称','文件路径'

BACKUP DATABASE 数据库名称
TO 数据库备份名称

2.sp_addumpdevice

代码

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go

ALTER   procedure   [ sys ] . [ sp_addumpdevice ]      --  1995/09/07 12:01
     @devtype   varchar ( 20 ),                 --  disk, tape, or virtual_device
     @logicalname    sysname,                 --  logical name of the device
     @physicalname    nvarchar ( 260 ),         --  physical name of the device
     @cntrltype    smallint   =   null ,         --  obsolete: controller type - ignored.
     @devstatus    varchar ( 40 )  =   null        --  obsolete: device characteristics -ignored
as
     declare   @type_enum      smallint   --  devtype enumeration value
     declare   @returncode   int
     declare   @devtypeIn   varchar ( 20 )
     select   @devtypeIn   =   @devtype
           , @devtype   =   LOWER  ( @devtype  collate Latin1_General_CI_AS)
     --  An open txn might jeopardize a recovery.
     set  implicit_transactions  off
     if   @@trancount   >   0
     begin
         raiserror ( 15002 , - 1 , - 1 , ' sys.sp_addumpdevice ' )
         return  ( 1 )
     end

     --  You must be SA to execute this sproc.
     if  ( not   is_srvrolemember ( ' diskadmin ' )  =   1 )
     begin
         raiserror ( 15247 , - 1 , - 1 )
         return  ( 1 )
     end

     --  Check out the @devtype.
     select   @type_enum   =  ( case   @devtype
         when   ' disk '                  then   2
         when   ' tape '                  then   5
         when   ' virtual_device '      then   7
         end )

     if   @type_enum   is   null
     begin
         raiserror ( 15044 , - 1 , - 1 , @devtypeIn )
         return  ( 1 )
     end

     --  Check the args are not NULL.
     if   @logicalname   is   null
     begin
         raiserror ( 15045 , - 1 , - 1 )
         return ( 1 )
     end

     --  Check to see that the @logicalname is valid.
     EXEC   @returncode   =  sys.sp_validname  @logicalname
     if   @returncode   <>   0
         return ( 1 )

     if   @physicalname   is   null
     begin
         raiserror ( 15046 , - 1 , - 1 )
         return ( 1 )
     end

     --  Prohibit certain special english words from being logical names.
     if  ( @logicalname   IN  ( ' disk ' ,  ' tape ' ,  ' virtual_device ' ))
     begin
         raiserror ( 15285 , - 1 , - 1 , @logicalname )
         return  ( 1 )
     end

     BEGIN   TRANSACTION
     --  Make sure that a device with @logicalname doesn't already exist.
     --   Always turn on the dump status bit, ignore @skip_tape (not in use)
     EXEC   %% Device().NewDevice(Name  =   @logicalname , PhysicalName  =   @physicalname ,
        Type  =   @type_enum , Size  =   0 )
     if   @@error   <>   0      --  duplicate logical name
     begin
         ROLLBACK   TRANSACTION
         raiserror ( 15026 , - 1 , - 1 , @logicalname )
         return  ( 1 )
     end

     --  Make sure physical file name would be unique among devices.
     if  ( select   count ( * )  from  master.dbo.sysdevices  where  phyname  =   @physicalname )  >   1
     begin
         ROLLBACK   TRANSACTION
         raiserror ( 15061 , - 1 , - 1 , @physicalname )
         return  ( 1 )
     end

     COMMIT   TRANSACTION

     return  ( 0 )  --  sp_addumpdevice

 

3.这样备份有一个弊端。如果没有修改数据库备份名称，多次使用原有备份的名称，会将数据累加到该原有备份文件上面，比如该数据库第一次备份是100M，还是使用此名称备份下次就是200M，300M....,这样就要判断该备份文件是否存在！

 

代码

USE  master
GO
declare   @num   int   -- 申明一个接受返回值的变量
EXEC  xp_fileexist  ' E:\开发软件\数据库\SQl2005示例数据库\AdvWorksData.bak ' , @num  output  --  执行文件存在否的验证 存在返回1 不存在返回0
if ( @num   =   1 )  -- 如果存在就给出提示或做其他功能的实现
begin
print   ' 备份文件已经存在 '
end
else   -- 该文件不存在执行备份操作
begin
EXEC  sp_addumpdevice  ' disk ' ,  ' AdvWorksData ' , 
' E:\开发软件\数据库\SQl2005示例数据库\AdvWorksData.bak '
BACKUP   DATABASE  AdventureWorks 
    TO  AdvWorksData
end

 

 改进，如果该备份文件已经存在使用当前系统日期时间作为文件名的一部分，这样每次备份几乎就不可能出现同名数据累加的现象了

 

代码

USE  master
GO
declare   @num   int   -- 申明一个接受返回值的变量
EXEC  xp_fileexist  ' E:\开发软件\数据库\SQl2005示例数据库\AdvWorksData.bak ' , @num  output  --  执行文件存在否的验证 存在返回1 不存在返回0
if ( @num   =   1 )  -- 如果存在就给出提示或做其他功能的实现
begin
declare   @file   varchar ( 120 ), @name   varchar ( 30 )
set   @file   =   ' E:\开发软件\数据库\SQl2005示例数据库\AdvWorksData_ ' +   replace ( replace ( replace ( CONVERT ( varchar ,  getdate (),  120  ), ' - ' , ' _ ' ), '   ' , ' _ ' ), ' : ' , '' ) + ' .bak '
set   @name   =   ' AdvWorksData ' + CONVERT ( VARCHAR ( 30 ), GETDATE (), 9 )
print   ' 备份文件已经存在，自动按照日期重新命名进行备份 '
EXEC  sp_addumpdevice  ' disk ' , @name  ,  @file
BACKUP   DATABASE  AdventureWorks 
    TO   @name
end
else   -- 该文件不存在执行备份操作
begin
EXEC  sp_addumpdevice  ' disk ' ,  ' AdvWorksData ' , 
' E:\开发软件\数据库\SQl2005示例数据库\AdvWorksData.bak '
BACKUP   DATABASE  AdventureWorks 
    TO  AdvWorksData
end

 

 

注意： E:\开发软件\数据库\SQl2005示例数据库\  该文件是已经事先创建好了的，还可以判断文件是否存在然后进行处理，但考虑一般备份文件的时候都有事先建立文件夹的习惯，所以这里不做处理。