任何能创建数据库的用户都可以创建数据库快照.

重要提示:只有 SQL Server 2005,SQL Server 2008 和 SQL Server 2008 R2 的 Enterprise Edition提供了 SQL Server 2005 中引入的数据库快照功能.

创建数据库快照的最佳方法

下面是进行如下操作的一些最佳方法:命名数据库快照,创建它们时进行计时,限制它们的数量以及将客户端连接重新定向到快照.

命名数据库快照

创建数据库快照之前,考虑如何命名它们是非常重要的.每个数据库快照都需要一个唯一的数据库名称.为了便于管理,数据库快照的名称可以包含标识数据库的信息,例如:

• 源数据库的名称.
• 该新名称用于快照的指示信息.

• 快照的创建日期和时间,序列号或一些其他的信息(例如一天中的某个时间)以区分给定的数据库上的连续快照.

例如,考虑 AdventureWorks2008R2 数据库的一系列快照.在上午 6 时和下午 6 时(基于 24 小时制)之间,以 6个小时作为间隔创建三个每日快照.每个每日快照保持 24 小时才被删除并被同一名称的新快照替换.请注意,每个快照名称指明了小时,而非天:

AdventureWorks2008R2_snapshot_0600

AdventureWorks2008R2_snapshot_1200

AdventureWorks2008R2_snapshot_1800

另外,如果这些每日快照创建的时间每天都变化,则推荐使用不太精确的命名约定,例如:

AdventureWorks2008R2_snapshot_morning

AdventureWorks2008R2_snapshot_noon

AdventureWorks2008R2_snapshot_evening

限制数据库快照的数量

随着时间的变化创建一系列快照可捕获源数据库的连续快照.每个数据库快照会一直存在直到显式删除.因为每个快照会随着原始页的更新而不断增长,所以您可能想在创建新快照后通过删除旧的快照来节省空间.

注意:如果想要还原到某个数据库快照,则需要从该数据库中删除所有其他快照.

将客户端连接到数据库快照

若要使用数据库快照,客户端需要知道它的位置.正在创建或删除另一个数据库快照时,用户可以从一个数据库快照读取.但是,如果用新快照替代现有快照,您需要将客户端重新定向到新快照.用户可以通过 SQL Server Management Studio 手动连接到数据库快照.但是,若要支持生产环境,您应该创建一个编程解决方案,该方案透明地将报表编写客户端定向到数据库的最新数据库快照.

创建数据库快照

注意:SQL Server Management Studio 不支持创建数据库快照.

创建数据库快照的唯一方式是使用 Transact-SQL.可创建数据库的任何用户都可以创建数据库快照;但是,若要创建镜像数据库的快照,您必须是 sysadmin 固定服务器角色的成员.

1. 根据源数据库的当前大小,确保有足够的磁盘空间存放数据库快照.数据库快照的最大大小为创建快照时源数据库的大小.

1. 使用 AS SNAPSHOT OF 子句对文件执行 CREATE DATABASE 语句.创建快照需要指定源数据库的每个数据库文件的逻辑名称

注意:创建数据库快照时,CREATE DATABASE 语句中不允许有日志文件,脱机文件,还原文件和不起作用的文件.

A. 对 AdventureWorks2008R2 数据库创建快照

此示例对 AdventureWorks2008R2 数据库创建数据库快照.快照名称 AdventureWorks2008R2_dbss_1800 及其稀疏文件的名称 AdventureWorks2008R2_data_1800.ss 指明了创建时间 6 P.M.(1800 小时).

CREATE DATABASE AdventureWorks2008R2_dbss1800 ON

( NAME = AdventureWorks2008R2_Data, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/Data/AdventureWorks2008R2_data_1800.ss’ )

AS SNAPSHOT OF AdventureWorks2008R2;

GO

注意:示例中随意使用了扩展名 .ss.

B. 对 Sales 数据库创建快照

此示例对 Sales 数据库创建数据库快照 sales_snapshot1200.

–Creating sales_snapshot1200 as snapshot of the

–Sales database:

CREATE DATABASE sales_snapshot1200 ON

( NAME = SPri1_dat, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/data/SPri1dat_1200.ss’),

( NAME = SPri2_dat, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/data/SPri2dt_1200.ss’),

( NAME = SGrp1Fi1_dat, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/data/SG1Fi1dt_1200.ss’),

( NAME = SGrp1Fi2_dat, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/data/SG1Fi2dt_1200.ss’),

( NAME = SGrp2Fi1_dat, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/data/SG2Fi1dt_1200.ss’),

( NAME = SGrp2Fi2_dat, FILENAME =

‘C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/data/SG2Fi2dt_1200.ss’)

AS SNAPSHOT OF Sales

GO

页面还原与使用完整恢复模式或大容量日志恢复模式的 SQL Server 数据库相关.只有读/写文件组支持页面还原.

页面还原的目的是还原一个或多个损坏的页,而不还原整个数据库.通常,要进行还原的页已经由于在访问该页时遇到错误而标记为“可疑“.可疑页在 msdb 数据库的 suspect_pages 表中进行了标识.

注意:并非所有的页面错误都需要还原.缓存数据(例如辅助索引)中可能出现的问题可以通过重新计算这些数据来解决.例如,如果数据库管理员删除一个辅助索引,然后再重新生成一个辅助索引,则损坏的数据虽然已修复,但并没有在suspect_pages 表中反映出这一情况

可以立即还原多个数据库页.日志文件备份应用于包含要恢复的页的所有数据库文件.与文件还原中一样,每次传递日志重做,前滚集都会前进一步.

页面还原用于修复隔离的损坏页.还原和恢复少量页面的速度可能比还原一个文件更快,因此减少了还原操作中处于脱机状态的数据量.然而,如果文件中要还原的不只是少量页面,则通常还原整个文件更为有效.例如,如果某个设备上的大量页都指出此设备有未解决的故障;不妨考虑还原该文件(可以还原到另一位置)并修复该设备.

页面还原方案

SQL Server 2005 和更高版本的所有 Edition 都支持在数据库脱机时还原页面(“脱机页面还原“).在 SQL Server 2005 Enterprise Edition 和更高版本中,如果页面还原过程中数据库处于联机状态,则数据库将保持联机状态.在数据库处于在线状态时还原和恢复页面的行为称作“在线页面还原“.

这些页面还原方案包括:

• 脱机页面还原
  SQL Server 2005 Standard,SQL Server 2005 Express Edition 和 SQL Server 2005 Workgroup 及更高版本仅支持脱机还原.如果数据库已经处于脱机状态,则 SQL Server 2005 Enterprise Edition 及更高版本将使用脱机还原.在脱机还原页过程中,还原损坏的页时数据库处于脱机状态.还原顺序结束时,数据库将联机.
  为了成功还原页面,已还原的页必须恢复到与数据库一致的状态.必须将不中断的日志备份链应用于最后一次完整或差异还原,以便让包含该页的文件组前进到当前的日志文件.

• 联机页面还原
  在 SQL Server 2005 Enterprise Edition 和更高版本中,情况允许时,会自动执行联机页面还原.大多数情况下,可以在数据库(包括页面要还原到的文件组)保持在线状态时还原损坏的页.联机页面还原对于因硬件错误而损坏的页尤其有用.
  有时,损坏的页需要脱机还原.例如,某些重要的页发生损坏可能会使数据库无法启动.在这类情况下,必须采用脱机还原.

注意:联机还原会尝试更新元数据,如果涉及重要的页面,则该更新可能会失败.如果联机还原尝试失败,则必须执行脱机还原.

页面还原利用了 SQL Server 2005 和更高版本中改进的页级错误报告(包含页校验和)和跟踪.通过校验和或残缺写操作检测为已损坏的页(“损坏页“)可以通过在 RESTORE 语句中指定这些页进行还原.页面还原仅适用于还原损坏的页数量较少的情况.RESTORE 语句中指定的每个页将由指定备份集中的页替换.还原的页必须恢复到与数据库一致的状态.仅还原显式指定的页.

页面还原的限制

仅可以还原数据库页.页面还原不能用于还原下列内容:

• 事务日志
• 分配页:全局分配映射 (GAM) 页,共享全局分配映射 (SGAM) 页和页可用空间 (PFS) 页.有关详细信息,请参阅管理区分配和可用空间.
• 所有数据文件的页 0(文件启动页)
• 页 1:9(数据库启动页)
• 全文目录

如果无法还原单个页,则必须使用现有的完整数据库备份或者完整文件,或文件组备份.

注意:如果要还原的页具有特殊用途(如元数据页),则联机页面还原将失败.在这些情况下,请尝试脱机页面还原.

还原页的要求

页面还原需要符合下列要求:

• 数据库必须使用完整恢复模式或大容量日志恢复模式.使用大容量日志恢复模式时存在一些问题
• 只读文件组中的页无法还原.在正在还原文件组中的页的情况下,尝试将该文件组设置为只读会失败.
• 还原顺序必须从完整备份,文件备份或文件组备份开始.
• 页面还原需要截止到当前日志文件的连续日志备份,并且必须应用所有这些备份,页才能恢复到当前日志文件的状态.

• 与在文件还原顺序中一样,您可以在每个还原步骤中向前滚集中添加更多的页.

• 数据库备份和页面还原不能同时运行.

大容量日志恢复模式和页面还原

对于使用大容量日志恢复模式的数据库,页面还原还有下列附加条件:

• 对大容量日志数据而言,在文件组或页数据处于脱机状态时进行备份是有问题的,因为日志中不记录脱机数据.任何脱机页都可能导致无法备份日志.在这种情况下,则应考虑使用 DBCC REPAIR,因为此方式导致的数据丢失少于还原到最近备份引起的数据丢失.

• 如果大容量日志数据库的日志备份遇到错误页,除非指定了 WITH CONTINUE_AFTER_ERROR,否则将失败.

• 通常,页面还原不能与大容量日志恢复模式配合使用.
  执行页面还原的最佳做法是将数据库设置为完整恢复模式,并尝试进行一次日志备份.如果可以进行日志备份,则可以继续进行页面还原.如果日志备份失败,则您将不得不丢失上一个日志备份之后的工作,或必须尝试运行 DBCC(必须使用 REPAIR_ALLOW_DATA_LOSS 选项).

基本页面还原语法

若要在 RESTORE DATABASE 语句中指定一页,需要知道该页所在文件的文件 ID 和该页的页 ID.所需语法如下:

RESTORE DATABASE database_name

   PAGE = ‘file:page [ ,…n ]‘ [ ,…n ]

   FROM <backup_device> [ ,…n ]

WITH NORECOVERY

页面还原的过程

页面还原的基本步骤如下:

1.获取要还原的损坏页的页 ID.校验和或残缺写错误将返回页 ID,并提供指定页所需的信息.若要查找损坏页的页 ID,请使用下列任一来源.

2.从包含页的完整数据库备份,文件备份或文件组备份开始进行页面还原.在 RESTORE DATABASE 语句中,使用 PAGE子句列出所有要还原的页的页 ID.

PAGE = ‘file:page [ ,…n ]‘

3.应用最近的差异.

4.应用后续日志备份.

5.创建新的数据库日志备份,使其包含已还原页的最终 LSN,即最后还原的页脱机的时间点.设置为顺序中首先还原的最终 LSN 是重做目标 LSN.包含该页的文件的联机前滚可以在重做目标 LSN 处停止.若要了解文件的当前重做目标LSN,请查看 sys.master_files 的 redo_target_lsn 列

6.还原新的日志备份.应用这个新的日志备份后,就完成了页面还原,可以开始使用页了.

注意:此顺序与文件还原顺序类似.事实上,页面还原和文件还原都可以在相同的顺序中执行.

示例

以下示例使用 NORECOVERY 还原文件 B 的四个损坏页.随后,将使用 NORECOVERY 应用两个日志备份,然后是结尾日志备份(使用 RECOVERY 还原).

重要提示:如果损坏的页存储了重要的数据库元数据,则可能必须执行脱机页面还原顺序.若要执行脱机还原,则必须使用WITH NORECOVERY 备份事务日志.

以下示例执行联机还原.此示例中,文件 B 的文件 ID 为 1,损坏的页的页 ID 分别为 57,202,916 和 1016.

RESTORE DATABASE <database> PAGE=’1:57, 1:202, 1:916, 1:1016′

   FROM <file_backup_of_file_B>

   WITH NORECOVERY;

RESTORE LOG <database> FROM <log_backup>

   WITH NORECOVERY;

RESTORE LOG <database> FROM <log_backup>

   WITH NORECOVERY;

BACKUP LOG <database> TO <new_log_backup>

RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;

GO

在一个产品介绍网站中查询产品时,由于产品的介绍性文字可能会很长,如果使用对产品介绍字段使用like进行模糊查询,性能肯定会是问题.那么如何解决这个问题呢?第一个想法就是使用全文索引.那么全文索引是什么,应该如何应用,在应用的过程中又应该注意哪些事情呢?

1.是什么:全文索引为在字符串数据中进行复杂的词搜索提供有效支持.全文索引存储关于重要词和这些词在特定列中的位置的信息.全文查询利用这些信息,可快速搜索包含具体某个词或一组词的行.

全文索引包含在全文目录中.每个数据库可以包含一个或多个全文目录.一个目录不能属于多个数据库,而每个目录可以包含一个或多个表的全文索引.一个表只能有一个全文索引,因此每个有全文索引的表只属于一个全文目录.

全文目录和索引不存储在它们所属的数据库中.目录和索引由 Microsoft 搜索服务分开管理.

全文索引必须在基表上定义,而不能在视图,系统表或临时表上定义.

依据上面的描述,可以做这样一个比喻.大家大概都见过档案柜,档案柜是将各种档案按照分类登记在档案索引卡上,这个档案柜中的就象建立的全文索引,通过这些档案索引卡可以迅速定位你要查找的卷宗所在的位置.如果不建立这些索引卡,如果卷宗数量不多还好,一旦档案数量很多的时候显然很难找到期望的卷宗,这就类似使用LIKE的情形.

全文索引和普通索引的区别: 

2.怎么用 

  a.返回包含字符串 “sea” 或 “bread” 的所有分类描述.

    Use Northwind

    Select * from categories

    where contains( description, ‘ “sea*” or “bread*” ‘)

  b.搜索产品描述中含有与 bread,candy,dry 和 meat 相关的词语的所有产品类别,如breads,candies,dried 和 meats 等.

    USE Northwind

    GO

    SELECT CategoryName

    FROM Categories

    WHERE FREETEXT (Description, ‘sweetest candy bread and dry meat’ )

    GO

3.建议

  a.仔细考虑维护全文索引的方式

    维护全文索引有三种方式:

• 完全重建
  重新扫描所有行.彻底重建全文索引.既可以立即执行完全重建,也可以通过 SQL Server 代理按调度进行.
• 基于时间戳的增量重建
  重新扫描那些从上一次完全重建或增量重建以来曾更改过的行.这样做需要在表上有一 timestamp 列.不更新时间戳的更改(如 WRITETEXT 和 UPDATETEXT)是检测不到的.可以立即执行增量重建,也可以按调度进行.

• 更改跟踪
  维护一份对索引数据的全部更改的列表.用 WRITETEXT 和 UPDATETEXT 进行的更改是检测不到的.可以用这些更改立即更新全文索引,也可以按调度进行,或者使用后台更新索引选项在更改一发生时便更新.

  b.所使用的方法取决于许多因素,如 CPU 和可用的内存,数据更改的数量和速度,可用磁盘空间的大小,以及当前全文索引的重要性等.以下建议可作为选择维护方式时的参考.

• 当 CPU 和内存不成问题,最新索引的值很高,且即时传播可以跟得上更改的速度时,使用带后台更新索引选项的更改跟踪.

• 当 CPU 和内存可以在调度时间使用,用于存储更改的磁盘空间足够大,且调度时间之间的变化并没有大到使传播所需的时间比完全重建更长时,使用带调度传播的更改跟踪.

• 如果大部分记录的更改或添加是立即发生的,应该使用完全重建.如果大部分记录是在扩展的时间段更改的,考虑使用带调度或后台更新索引的更改跟踪.
• 如果每一次更改的文档数目很多(并不是所占的百分比很高),可以使用增量重建.如果大量记录的更改是在扩展时间段发生的,考虑使用带调度或后台更新索引的更改跟踪.  

    不过即使选择好作业类型后,也应该给调度全文索引的时机进行恰当的规划.由于表中数据的改变会影响全文索引内容,所以频繁的更新数据的表不太适合进行全文索引.同时可以把调度填充全文索引的时间放在系统比较空闲的时候,而且应该考虑到进行填充可能的时间.比如你可以把填充的时间定在每天晚上0:00,这个时候应该相对空闲一些,不过一般情况下应该差不多吧). 

    另外应该模拟客户处可能的数据量做个填充实验,以便对填充索引的时间长度有所估计. 

如何恢复到快照结果,说明恢复操作的限制,并提供指向恢复过程的链接.作为从备份中还原联机数据库的替代方法,任何对源数据库具有 RESTORE DATABASE 权限的用户均可将该数据库恢复到创建数据库快照时的状态.当联机源数据库中的数据损坏时,恢复到最近的快照可能比较合适.但是,请确保该快照是在发生错误之前创建的,并且数据库尚未损坏.例如,恢复操作可以恢复最近出现的严重用户错误,如删除表.

通过从稀疏文件中将写入时复制的页复制回源数据库,恢复操作将覆盖自快照创建以来对源数据库进行的更新.只有更新过的页才会被覆盖.恢复操作随后会覆盖旧的日志文件,并重建日志.因此,以后无法将恢复后的数据库前滚到出现用户错误时的状态,并且自快照创建以来对数据库进行的更改将丢失.恢复后的数据库的元数据与创建快照时的元数据相同.

恢复到某个快照还会删除所有全文目录.

对恢复的限制

下列情况不支持恢复:

• 源数据库包含任何只读或压缩的文件组.
• 某些在创建快照时处于联机状态的文件已脱机.
• 当前存在多个数据库快照.
  只有要恢复的快照才可存在.

在恢复数据库之前,注意下列事项:

• 从数据库快照恢复不适用于媒体恢复.与定期备份集不同,数据库快照并非数据库文件的完整副本.如果数据库或数据库快照已损坏,则可能无法从快照恢复.另外,如果损坏的话,即便可以恢复,也可能无法更正该问题.
• 在恢复操作期间,快照和源数据库都不可用.源数据库和快照都将被标记为”正在还原”.如果在恢复操作期间出现错误,则当数据库再次启动时,恢复操作将会尝试完成恢复.
• 由于成功的恢复操作会自动重建日志,因此 Microsoft 建议在恢复数据库之前备份日志.虽然不能还原原始日志以便将数据库前滚,但是可以使用原始日志文件中的信息来重新构造丢失的数据.
• 恢复操作会打断日志备份链.因此,必须先对恢复的数据库进行完整数据库备份或文件备份,然后才能进行日志备份.Microsoft 建议进行完整数据库备份.

将数据库恢复到数据库快照

数据库快照不是冗余存储,因此,不针对磁盘错误或其他类型的损坏提供任何保护功能.但是,如果在联机数据库中发生用户错误,则可以将数据库恢复到发生错误之前的数据库快照.

重要提示:为了保护数据库,非常有必要定期执行备份并测试还原计划.如果必须将源数据库还原到创建数据库快照的时间点,请实施允许您执行该操作的备份策略.

恢复的数据库会覆盖原来的源数据库.恢复到快照将删除所有全文目录.

1. 标识要将数据库恢复到的数据库快照.此外,您还可以在 sys.databases 目录视图的source_database_id 列中找到某个视图的源数据库.
2. 删除其他任何数据库快照.
   有关删除快照的信息.如果数据库使用完整恢复模式,则在执行恢复之前,应先备份日志
3. 执行恢复操作.
   恢复操作要求对源数据库具有 RESTORE DATABASE 权限.若要恢复数据库,请使用下列 Transact-SQL语句:
   RESTORE DATABASE <数据库名称> FROM DATABASE_SNAPSHOT =<数据库快照名称>
   其中,<数据库名称> 是源数据库的名称,<数据库快照名称> 是要将数据库恢复到的快照的名称.注意,必须在此语句中指定快照名称而非备份设备.

注意:在恢复操作过程中,快照和源数据库都不可用.源数据库和快照都标记为”还原中”.如果在恢复操作期间发生错误,则数据库在重新启动后,将尝试完成恢复操作.

1. 如果创建数据库快照后数据库所有者发生了变化,您可能希望更新恢复的数据库的数据库所有者.

注意:已恢复的数据库将保留数据库快照的权限和配置(例如,数据库所有者和恢复模式).

1. 启动数据库.
2. 尤其在使用完整(或大容量日志)恢复模式时,可以选择备份已恢复的数据库.本节包含将数据库恢复到数据库快照的示例.

A. 恢复 AdventureWorks2008R2 数据库的快照

此示例假定 AdventureWorks2008R2 数据库当前只存在一个快照.

USE master;

— Reverting AdventureWorks2008R2 to AdventureWorks2008R2_dbss1800

RESTORE DATABASE AdventureWorks2008R2 FROM

DATABASE_SNAPSHOT = ‘AdventureWorks2008R2_dbss1800’;

GO

B. 恢复 Sales 数据库的快照

此示例假定 Sales 数据库当前存在两个快照:sales_snapshot0600 和 sales_snapshot1200.此示例删除了较旧的快照并将数据库恢复到较新的快照.

–Test to see if sales_snapshot0600 exists and if it

— does, delete it.

IF EXISTS (SELECT dbid FROM sys.databases

    WHERE NAME=’sales_snapshot0600′)

    DROP DATABASE SalesSnapshot0600;

GO

— Reverting Sales to sales_snapshot1200

USE master;

RESTORE DATABASE Sales FROM DATABASE_SNAPSHOT = ‘sales_snapshot1200’;

GO

数据库快照使用一个或多个“稀疏文件“来存储数据.创建数据库快照期间,可以使用 CREATE DATABASE 语句中的文件名来创建稀疏文件.这些文件名存储在 sys.master_files 中的 physical_name 列中.

注意:在 sys.database_files 中(无论是在源数据库中还是在快照中),physical_name 列中始终包含源数据库文件的名称.

稀疏文件是 NTFS 文件系统的一项功能.最初,稀疏文件不包含用户数据,因为未向其分配磁盘空间用来存储用户数据.

首次创建稀疏文件时,稀疏文件占用的磁盘空间非常少.随着数据写入稀疏文件,NTFS 会逐渐分配磁盘空间.稀疏文件可能会占用非常大的磁盘空间.如果数据库快照用尽了空间,将被标记为可疑,必须将其删除.但是,源数据库不会受到影响;对其执行的操作仍能继续正常进行.

稀疏文件按 64 KB 的增量增长;因此,磁盘上稀疏文件的大小始终是 64 KB 的倍数.根据从源数据库复制的页数,最新增长的 64 KB 可存放一到八个 8 KB 页.这意味着稀疏文件的大小一般来说会稍大于页实际填充的空间.

• 查看稀疏文件的实际大小
  若要了解快照的每个稀疏文件当前在磁盘上使用的字节数,可以使用sys.dm_io_virtual_file_stats动态管理视图的 size_on_disk_bytes 列.
  另外,若要查看稀疏文件占用的磁盘空间,也可以在 Microsoft Windows 中右键单击文件,再单击“属性“,然后查看“占用空间“值.

• 查看稀疏文件的最大大小
  稀疏文件最大只能增长到创建快照时相应的源数据库文件的大小.若要了解此大小,可以使用下列方法之一:
  • 使用 Windows dir 命令.
  • 在 Windows 中,选择稀疏文件,打开文件“属性“对话框,然后查看“大小“值.
  • 从数据库快照的 sys.database_files 中或从 sys.master_files 中选择 size列.sys.database_files 或 sys.master_files 中的 size 列反映了快照曾经使用过的最大空间(SQL 页数);此值相当于 Windows 的“大小“字段,不同的是此值以文件中包含的 SQL 页数表示;大小(以字节为单位)为🙁页数 * 8192)