MS SQL Server 快速指南

---

---

MS SQL Server - 概述

本章介绍 SQL Server，讨论其用法、优势、版本和组件。

什么是 SQL Server？

• 它是由微软开发的一种软件，根据 RDBMS 的规范实现。

• 它也是一种 ORDBMS。

• 它是平台相关的。

• 它既是基于 GUI 的软件，也是基于命令行的软件。

• 它支持 SQL（SEQUEL）语言，这是一种 IBM 产品，非过程化的、通用的数据库语言，并且不区分大小写。

SQL Server 的用法

• 创建数据库。
• 维护数据库。
• 通过 SQL Server Analysis Services (SSAS) 分析数据。
• 通过 SQL Server Reporting Services (SSRS) 生成报表。
• 通过 SQL Server Integration Services (SSIS) 执行 ETL 操作。

Learn SQL in-depth with real-world projects through our SQL certification course. Enroll and become a certified expert to boost your career.

SQL Server 的版本

SQL Server 组件

SQL Server 采用客户端-服务器架构，因此它支持两种类型的组件 - (a) 工作站和 (b) 服务器。

• 工作站组件安装在每个设备/SQL Server 操作员的机器上。这些只是与服务器组件交互的界面。例如：SSMS、SSCM、Profiler、BIDS、SQLEM 等。

• 服务器组件安装在集中式服务器上。这些是服务。例如：SQL Server、SQL Server 代理、SSIS、SSAS、SSRS、SQL 浏览器、SQL Server 全文搜索等。

SQL Server 实例

• 实例是 SQL Server 的一个安装。
• 实例是同一软件的完全副本。
• 如果我们安装“n”次，则将创建“n”个实例。
• SQL Server 中有两种类型的实例：a) 默认 b) 命名。
• 在一台服务器上仅支持一个默认实例。
• 在一台服务器上支持多个命名实例。
• 默认实例将服务器名称作为实例名称。
• 默认实例服务名称为 MSSQLSERVER。
• 2000 版支持 16 个实例。
• 2005 及更高版本支持 50 个实例。

实例的优势

• 在一台机器上安装不同的版本。
• 降低成本。
• 分别维护生产、开发和测试环境。
• 减少临时数据库问题。
• 分离安全权限。
• 维护备用服务器。

MS SQL Server - 版本

SQL Server 提供多种版本。本章列出了多个版本及其功能。

• 企业版 - 这是最高端的版本，具有完整的功能集。

• 标准版 - 当不需要高级功能时，它的功能少于企业版。

• 工作组版 - 适用于大型公司的远程办事处。

• Web 版 - 专为 Web 应用程序设计。

• 开发人员版 - 类似于企业版，但仅许可给一个用户用于开发、测试和演示。它可以轻松升级到企业版，而无需重新安装。

• Express 版 - 这是免费的入门级数据库。它只能使用 1 个 CPU 和 1 GB 内存，数据库的最大大小为 10 GB。

• Compact 版 - 这是用于移动应用程序开发的免费嵌入式数据库。数据库的最大大小为 4 GB。

• 数据中心版 - 新版 SQL Server 2008 R2 的主要变化是数据中心版。数据中心版没有内存限制，并且支持超过 25 个实例。

• 商业智能版 - 商业智能版是 SQL Server 2012 中的新版本。此版本包括标准版中的所有功能，并支持高级 BI 功能，如 Power View 和 PowerPivot，但它缺乏对高级可用性功能的支持，如 AlwaysOn 可用性组和其他联机操作。

• 企业版评估版 - SQL Server 评估版是获得 SQL Server 的功能齐全且免费实例以进行学习和开发解决方案的好方法。此版本从安装之日起内置 6 个月的有效期。

MS SQL Server - 安装

SQL Server 支持两种类型的安装 -

• 独立安装
• 基于群集的安装

检查

• 检查服务器的 RDP 访问权限。
• 检查服务器的操作系统位数、IP 和域。
• 检查您的帐户是否在管理员组中以运行 setup.exe 文件。
• 软件位置。

需求

• 版本、版本、SP 和修补程序（如果有）。
• 数据库引擎、代理、SSAS、SSIS、SSRS 的服务帐户（如果有）。
• 命名实例名称（如果有）。
• 二进制文件、系统、用户数据库的位置。
• 身份验证模式。
• 排序规则设置。
• 功能列表。

2005 的先决条件

• 安装支持文件。
• .net framework 2.0。
• SQL Server 本机客户端。

2008 和 2008R2 的先决条件

• 安装支持文件。
• .net framework 3.5 SP1。
• SQL Server 本机客户端。
• Windows Installer 4.5/更高版本。

2012 和 2014 的先决条件

• 安装支持文件。
• .net framework 4.0。
• SQL Server 本机客户端。
• Windows Installer 4.5/更高版本。
• Windows PowerShell 2.0。

安装步骤

步骤 1 - 从 https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019 下载评估版

下载软件后，根据您的下载（32 位或 64 位）选项，将提供以下文件。

ENU\x86\SQLFULL_x86_ENU_Core.box

ENU\x86\SQLFULL_x86_ENU_Install.exe

ENU\x86\SQLFULL_x86_ENU_Lang.box

或

ENU\x86\SQLFULL_x64_ENU_Core.box

ENU\x86\SQLFULL_x64_ENU_Install.exe

ENU\x86\SQLFULL_x64_ENU_Lang.box

注意 - X86（32 位）和 X64（64 位）

步骤 2 - 双击“SQLFULL_x86_ENU_Install.exe”或“SQLFULL_x64_ENU_Install.exe”，它将提取“SQLFULL_x86_ENU”或“SQLFULL_x86_ENU”文件夹中安装所需的 文件。

步骤 3 - 单击“SQLFULL_x86_ENU”或“SQLFULL_x64_ENU_Install.exe”文件夹，然后双击“SETUP”应用程序。

为了便于理解，这里我们使用了 SQLFULL_x64_ENU_Install.exe 软件。

步骤 4 - 单击“setup”应用程序后，将打开以下屏幕。

步骤 5 - 单击上面屏幕左侧的“安装”。

步骤 6 - 单击上面屏幕右侧的第一个选项。将打开以下屏幕。

步骤 7 - 单击“确定”，将弹出以下屏幕。

步骤 8 - 单击“下一步”以获取以下屏幕。

步骤 9 - 确保选中产品密钥选择，然后单击“下一步”。

步骤 10 - 选中复选框以接受许可证选项，然后单击“下一步”。

步骤 11 - 选择 SQL Server 功能安装选项，然后单击“下一步”。

步骤 12 - 选择“数据库引擎服务”复选框，然后单击“下一步”。

步骤 13 - 输入命名实例（这里我使用了 TestInstance），然后单击“下一步”。

步骤 14 - 单击上面屏幕上的“下一步”，将出现以下屏幕。

步骤 15 - 选择上面列出的服务的帐户名称和启动类型，然后单击“排序规则”。

步骤 16 - 确保选中正确的排序规则选择，然后单击“下一步”。

步骤 17 - 确保选中身份验证模式选择和管理员，然后单击“数据目录”。

步骤 18 - 确保选择上述目录位置，然后单击“下一步”。将出现以下屏幕。

步骤 19 - 单击上面屏幕上的“下一步”。

步骤 20 - 单击上面屏幕上的“下一步”以获取以下屏幕。

步骤 21 - 确保正确选中上述选择，然后单击“安装”。

安装成功，如上图所示。单击“关闭”以完成。

MS SQL Server - 架构

为了便于理解，我们将 SQL Server 的架构分为以下几个部分 -

• 通用架构
• 内存架构
• 数据文件架构
• 日志文件架构

通用架构

客户端 - 请求发起的来源。

查询 - SQL 查询，它是高级语言。

逻辑单元 - 关键字、表达式和运算符等。

网络数据包 - 与网络相关的代码。

协议 - 在 SQL Server 中，我们有 4 种协议。

• 共享内存（用于本地连接和故障排除）。

• 命名管道（用于 LAN 连接中的连接）。

• TCP/IP（用于 WAN 连接中的连接）。

• VIA - 虚拟接口适配器（需要供应商设置特殊的硬件，并且从 SQL 2012 版本开始已弃用）。

服务器 - SQL 服务安装和数据库驻留的位置。

关系引擎 - 实际执行操作的地方。它包含查询解析器、查询优化器和查询执行器。

查询解析器（命令解析器）和编译器（翻译器） - 检查查询的语法，并将查询转换为机器语言。

查询优化器 - 以查询、统计信息和代数树作为输入，生成执行计划作为输出。

执行计划 - 类似于路线图，包含查询执行过程中所有步骤的顺序。

查询执行器 - 在这里，查询将借助执行计划逐步执行，并且还会联系存储引擎。

存储引擎 - 负责在存储系统（磁盘、SAN 等）上存储和检索数据、数据操作、锁定和管理事务。

SQL OS - 位于主机（Windows 操作系统）和 SQL Server 之间。数据库引擎上执行的所有活动都由 SQL OS 处理。SQL OS 提供各种操作系统服务，例如内存管理（处理缓冲池、日志缓冲区）以及使用阻塞和锁定结构进行死锁检测。

检查点进程 - 检查点是一个内部进程，它将缓冲区缓存中所有脏页（已修改的页）写入物理磁盘。此外，它还将日志缓冲区中的日志记录写入物理文件。将缓冲区缓存中的脏页写入数据文件也称为脏页硬化。

它是一个专用进程，由 SQL Server 在特定时间间隔自动运行。SQL Server 为每个数据库单独运行检查点进程。检查点有助于在意外关机或系统崩溃/故障的情况下减少 SQL Server 的恢复时间。

SQL Server 中的检查点

在 SQL Server 2012 中，有四种类型的检查点：

• 自动 - 这是最常见的检查点，它作为后台进程运行，以确保 SQL Server 数据库可以在恢复间隔 - 服务器配置选项定义的时间限制内恢复。

• 间接 - 这是 SQL Server 2012 中的新功能。它也运行在后台，但为了满足为配置了该选项的特定数据库指定的用户指定的目标恢复时间。一旦选择了给定数据库的 Target_Recovery_Time，它将覆盖为服务器指定的恢复间隔，并避免对该数据库进行自动检查点。

• 手动 - 此检查点与其他任何 T-SQL 语句一样运行，一旦发出检查点命令，它将运行到完成。手动检查点仅对当前数据库运行。您还可以指定 Checkpoint_Duration，它是可选的 - 此持续时间指定您希望检查点完成的时间。

• 内部 - 作为用户，您无法控制内部检查点。在特定操作上发出，例如

  • 关闭会在所有数据库上启动检查点操作，除非关闭不干净（使用 nowait 关闭）。

  • 如果恢复模型从完整/批量日志更改为简单。

  • 在进行数据库备份时。

  • 如果您的数据库处于简单恢复模型中，则检查点进程会在日志满 70% 或基于服务器选项 - 恢复间隔时自动执行。

  • 用于添加或删除数据/日志文件的 ALTER DATABASE 命令也会启动检查点。

  • 当数据库的恢复模型为批量日志并且执行了最小日志记录操作时，也会发生检查点。

  • 数据库快照创建。

• 延迟写入器进程 - 延迟写入器出于完全不同的原因将脏页推送到磁盘，因为它需要释放缓冲池中的内存。当 SQL Server 遇到内存压力时，就会发生这种情况。据我所知，这是由内部进程控制的，并且没有设置。

SQL Server 不断监控内存使用情况以评估资源争用（或可用性）；它的工作是确保始终有足够的可用空间。作为此过程的一部分，当它注意到任何此类资源争用时，它会触发延迟写入器通过将脏页写入磁盘来释放一些内存中的页。它采用最近最少使用 (LRU) 算法来确定要刷新到磁盘的页。

如果延迟写入器始终处于活动状态，则可能表示内存瓶颈。

内存架构

以下是内存架构的一些主要特征。

• 所有数据库软件的主要设计目标之一是最大程度地减少磁盘 I/O，因为磁盘读取和写入是最耗费资源的操作之一。

• Windows 中的内存可以使用虚拟地址空间调用，该空间由内核模式（操作系统模式）和用户模式（SQL Server 等应用程序）共享。

• SQL Server“用户地址空间”分为两个区域：MemToLeave 和缓冲池。

• MemToLeave (MTL) 和缓冲池 (BPool) 的大小由 SQL Server 在启动时确定。

• 缓冲区管理是实现 I/O 高效率的关键组成部分。缓冲区管理组件包含两种机制：缓冲区管理器用于访问和更新数据库页，缓冲池用于减少数据库文件 I/O。

• 缓冲池进一步细分为多个部分。其中最重要的是缓冲区缓存（也称为数据缓存）和过程缓存。缓冲区缓存将数据页保存在内存中，以便可以从缓存中检索经常访问的数据。另一种方法是从磁盘读取数据页。从缓存中读取数据页通过最大程度地减少所需的 I/O 操作（本质上比从内存中检索数据慢）来优化性能。

• 过程缓存保存存储过程和查询执行计划，以最大程度地减少生成查询计划的次数。您可以使用 DBCC PROCCACHE 语句查找有关过程缓存中大小和活动的信息。

缓冲池的其他部分包括：

• 系统级数据结构 - 保存有关数据库和锁的 SQL Server 实例级数据。

• 日志缓存 - 预留用于读取和写入事务日志页。

• 连接上下文 - 与实例的每个连接都有一个小的内存区域来记录连接的当前状态。此信息包括存储过程和用户定义函数参数、游标位置等。

• 堆栈空间 - Windows 为 SQL Server 启动的每个线程分配堆栈空间。

数据文件架构

数据文件架构包含以下组件：

文件组

可以将数据库文件组合到文件组中，以进行分配和管理。任何文件都不能属于多个文件组。日志文件永远不是文件组的一部分。日志空间与数据空间分开管理。

SQL Server 中有两种类型的文件组：主文件组和用户定义文件组。主文件组包含主数据文件和未专门分配到其他文件组的任何其他文件。系统表的所有页面都分配在主文件组中。用户定义文件组是在创建数据库或更改数据库语句中使用文件组关键字指定的任何文件组。

每个数据库中的一个文件组充当默认文件组。当 SQL Server 将页面分配给在创建时未指定文件组的表或索引时，这些页面将从默认文件组分配。要将默认文件组从一个文件组切换到另一个文件组，它应该具有 db_owner 固定数据库角色。

默认情况下，主文件组是默认文件组。用户应该具有 db_owner 固定数据库角色才能分别备份文件和文件组。

文件

数据库有三种类型的文件：主数据文件、辅助数据文件和日志文件。主数据文件是数据库的起点，并指向数据库中的其他文件。

每个数据库都有一个主数据文件。我们可以为主数据文件指定任何扩展名，但推荐的扩展名是.mdf。辅助数据文件是该数据库中除主数据文件之外的文件。某些数据库可能有多个辅助数据文件。某些数据库可能没有单个辅助数据文件。辅助数据文件的推荐扩展名是.ndf。

日志文件保存用于恢复数据库的所有日志信息。数据库必须至少有一个日志文件。一个数据库可以有多个日志文件。日志文件的推荐扩展名是.ldf。

数据库中所有文件的位置都记录在主数据库和数据库的主文件中。大多数情况下，数据库引擎使用主数据库中的文件位置。

文件有两个名称：逻辑名称和物理名称。逻辑名称用于在所有 T-SQL 语句中引用文件。物理名称是 OS_file_name，它必须遵循操作系统的规则。数据和日志文件可以放置在 FAT 或 NTFS 文件系统上，但不能放置在压缩的文件系统上。一个数据库最多可以有 32,767 个文件。

扩展

扩展是为表和索引分配空间的基本单位。一个扩展是 8 个连续页或 64KB。SQL Server 有两种类型的扩展：统一扩展和混合扩展。统一扩展仅由单个对象组成。混合扩展由多达八个对象共享。

页

它是 MS SQL Server 中数据存储的基本单位。页的大小为 8KB。每个页的开头是 96 字节的标头，用于存储系统信息，例如页的类型、页上的可用空间量以及拥有该页的对象的 ID。SQL Server 中有 9 种类型的数据页。

• 数据 - 包含所有数据行，除了文本、ntext 和图像数据。

• 索引 - 索引条目。

• 文本/图像 - 文本、图像和 ntext 数据。

• GAM - 有关已分配扩展的信息。

• SGAM - 有关系统级已分配扩展的信息。

• 页面空闲空间 (PFS) - 有关页面上可用空闲空间的信息。

• 索引分配映射 (IAM) - 有关表或索引使用的扩展的信息。

• 批量更改映射 (BCM) - 有关自上次备份日志语句以来通过批量操作修改的扩展的信息。

• 差异更改映射 (DCM) − 包含自上次备份数据库语句以来发生更改的范围的信息。

日志文件架构

SQL Server 事务日志在逻辑上运行，就像事务日志是一系列日志记录一样。每个日志记录都由日志序列号 (LSN) 标识。每个日志记录都包含其所属事务的 ID。

数据修改的日志记录要么记录执行的逻辑操作，要么记录修改数据的先前映像和后续映像。先前映像是在执行操作之前的数据副本；后续映像是在执行操作之后的数据副本。

恢复操作的步骤取决于日志记录的类型 -

• 记录的逻辑操作。
  • 要向前回滚逻辑操作，请再次执行该操作。
  • 要向后回滚逻辑操作，请执行相反的逻辑操作。
• 记录的先前映像和后续映像。
  • 要向前回滚操作，请应用后续映像。
  • 要向后回滚操作，请应用先前映像。

事务日志中记录了不同类型的操作。这些操作包括 -

• 每个事务的开始和结束。

• 每个数据修改（插入、更新或删除）。这包括系统存储过程或数据定义语言 (DDL) 语句对任何表的更改，包括系统表。

• 每个扩展和页面分配或取消分配。

• 创建或删除表或索引。

回滚操作也会记录在日志中。每个事务都会在事务日志上预留空间，以确保存在足够的日志空间来支持由显式回滚语句或遇到错误引起的回滚。此预留空间在事务完成后释放。

从必须存在于成功数据库范围回滚的第一个日志记录到最后一个写入的日志记录的日志文件部分称为日志的活动部分或活动日志。这是数据库完全恢复所需的日志部分。活动日志的任何部分都不能被截断。此第一个日志记录的 LSN 称为最小恢复 LSN (Min LSN)。

SQL Server 数据库引擎在内部将每个物理日志文件划分为多个虚拟日志文件。虚拟日志文件没有固定大小，并且物理日志文件没有固定数量的虚拟日志文件。

数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。数据库引擎试图保持少量虚拟文件。管理员无法配置或设置虚拟日志文件的大小或数量。虚拟日志文件影响系统性能的唯一时间是物理日志文件由较小的大小和 growth_increment 值定义时。

size 值是日志文件的初始大小，growth_increment 值是在每次需要新空间时添加到文件中的空间量。如果由于许多小的增量导致日志文件增长到很大，则它们将具有许多虚拟日志文件。这会减慢数据库启动以及日志备份和还原操作的速度。

我们建议您为日志文件分配接近所需最终大小的 size 值，并使用相对较大的 growth_increment 值。SQL Server 使用预写日志 (WAL)，它保证在将关联的日志记录写入磁盘之前，不会将任何数据修改写入磁盘。这维护了事务的 ACID 属性。

MS SQL Server - 管理工作室

SQL Server Management Studio 是一个工作站组件\客户端工具，如果我们在安装步骤中选择工作站组件，则将安装此工具。这允许您从图形界面连接和管理 SQL Server，而不必使用命令行。

为了连接到 SQL Server 的远程实例，您将需要此软件或类似软件。它由管理员、开发人员、测试人员等使用。

以下方法用于打开 SQL Server Management Studio。

第一种方法

开始 → 所有程序 → MS SQL Server 2012 → SQL Server Management Studio

第二种方法

转到运行并键入 SQLWB（对于 2005 版本）SSMS（对于 2008 及更高版本）。然后点击回车。

SQL Server Management Studio 将以以下快照所示的方式打开，无论使用上述哪种方法。

MS SQL Server - 登录数据库

登录是访问 SQL Server 的简单凭据。例如，您在登录 Windows 或甚至您的电子邮件帐户时提供用户名和密码。此用户名和密码构建了凭据。因此，凭据仅仅是用户名和密码。

SQL Server 允许四种类型的登录 -

• 基于 Windows 凭据的登录。
• 特定于 SQL Server 的登录。
• 映射到证书的登录。
• 映射到非对称密钥的登录。

在本教程中，我们感兴趣的是基于 Windows 凭据的登录和特定于 SQL Server 的登录。

基于 Windows 凭据的登录允许您使用 Windows 用户名和密码登录到 SQL Server。如果您需要创建自己的凭据（用户名和密码），则可以创建特定于 SQL Server 的登录。

要创建、更改或删除 SQL Server 登录，您可以采用以下两种方法之一 -

• 使用 SQL Server Management Studio。
• 使用 T-SQL 语句。

以下方法用于创建登录 -

第一种方法 – 使用 SQL Server Management Studio

步骤 1 − 连接到 SQL Server 实例后，展开登录文件夹，如下面的快照所示。

步骤 2 − 右键单击登录，然后单击新建登录，将打开以下屏幕。

步骤 3 − 填写登录名、密码和确认密码列，如上图所示，然后单击确定。

登录将创建，如下面的图像所示。

第二种方法 – 使用 T-SQL 脚本

Create login yourloginname with password='yourpassword'

要创建登录名为 TestLogin 且密码为 'P@ssword' 的登录，请运行以下查询。

Create login TestLogin with password='P@ssword'

MS SQL Server - 创建数据库

数据库是表、视图、存储过程、函数、触发器等对象的集合。

在 MS SQL Server 中，提供了两种类型的数据库。

• 系统数据库
• 用户数据库

系统数据库

安装 MS SQL Server 时会自动创建系统数据库。以下是系统数据库的列表 -

• Master
• Model
• MSDB
• Tempdb
• Resource（在 2005 版本中引入）
• Distribution（仅用于复制功能）

用户数据库

用户数据库由用户（有权创建数据库的管理员、开发人员和测试人员）创建。

以下方法用于创建用户数据库。

方法 1 – 使用 T-SQL 脚本或还原数据库

以下是创建 MS SQL Server 中数据库的基本语法。

Create database <yourdatabasename>

或

Restore Database <Your database name> from disk = '<Backup file location + file name>

示例

要创建名为 'Testdb' 的数据库，请运行以下查询。

Create database Testdb

或

Restore database Testdb from disk = 'D:\Backup\Testdb_full_backup.bak'

注意 − D:\backup 是备份文件的位置，Testdb_full_backup.bak 是备份文件名

方法 2 – 使用 SQL Server Management Studio

连接到 SQL Server 实例并右键单击数据库文件夹。单击新建数据库，将出现以下屏幕。

在数据库名称字段中输入您的数据库名称（例如：要创建名称为 'Testdb' 的数据库），然后单击确定。Testdb 数据库将创建，如下面的快照所示。

MS SQL Server - 选择数据库

在继续使用以下任何方法之前，请根据您的操作选择您的数据库。

方法 1 – 使用 SQL Server Management Studio

示例

要运行查询以选择名为 'msdb' 的数据库上的备份历史记录，请选择 msdb 数据库，如下面的快照所示。

方法 2 – 使用 T-SQL 脚本

Use <your database name>

示例

要运行您的查询以选择名为 'msdb' 的数据库上的备份历史记录，请通过执行以下查询来选择 msdb 数据库。

Exec use msdb

该查询将打开 msdb 数据库。您可以执行以下查询以选择备份历史记录。

Select * from backupset

MS SQL Server - 删除数据库

要从 MS SQL Server 中删除您的数据库，请使用 drop database 命令。以下两种方法可用于此目的。

方法 1 – 使用 T-SQL 脚本

以下是从 MS SQL Server 中删除数据库的基本语法。

Drop database <your database name>

示例

要删除数据库名称 'Testdb'，请运行以下查询。

Drop database Testdb

方法 2 – 使用 MS SQL Server Management Studio

连接到 SQL Server 并右键单击要删除的数据库。单击删除命令，将出现以下屏幕。

单击确定以从 MS SQL Server 中删除数据库（在本例中，名称为 Testdb，如上图所示）。

MS SQL Server - 创建备份

备份 是数据/数据库等的副本。备份 MS SQL Server 数据库对于保护数据至关重要。MS SQL Server 备份主要有三种类型 - 完全或数据库、差异或增量以及事务日志或日志。

备份数据库可以使用以下两种方法之一完成。

方法 1 – 使用 T-SQL

完全类型

Backup database <Your database name> to disk = '<Backup file location + file name>'

差异类型

Backup database <Your database name> to 
   disk = '<Backup file location + file name>' with differential

日志类型

Backup log <Your database name> to disk = '<Backup file location + file name>'

示例

以下命令用于将名为 'TestDB' 的数据库完全备份到 'D:\' 位置，备份文件名 'TestDB_Full.bak'

Backup database TestDB to disk = 'D:\TestDB_Full.bak'

以下命令用于将名为 'TestDB' 的数据库差异备份到 'D:\' 位置，备份文件名 'TestDB_diff.bak'

Backup database TestDB to disk = 'D:\TestDB_diff.bak' with differential

以下命令用于将名为 'TestDB' 的数据库日志备份到 'D:\' 位置，备份文件名 'TestDB_log.trn'

Backup log TestDB to disk = 'D:\TestDB_log.trn'

方法 2 – 使用 SSMS（SQL SERVER Management Studio）

步骤 1 − 连接到名为 'TESTINSTANCE' 的数据库实例并展开数据库文件夹，如下面的快照所示。

步骤 2 − 右键单击 'TestDB' 数据库并选择任务。单击备份，将出现以下屏幕。

步骤 3 − 选择备份类型（完全\差异\日志）并确保检查目标路径，即创建备份文件的位置。选择左上角的选项以查看以下屏幕。

步骤 4 − 单击确定以创建 'TestDB' 数据库完全备份，如下面的快照所示。

MS SQL Server - 恢复数据库

还原 是将数据从备份复制并将其应用于日志事务的过程。还原是您对备份执行的操作。获取备份文件并将其恢复为数据库。

还原数据库选项可以使用以下两种方法之一完成。

方法 1 – T-SQL

语法

Restore database <Your database name> from disk = '<Backup file location + file name>'

示例

以下命令用于还原名为 'TestDB' 的数据库，备份文件名 'TestDB_Full.bak' 可在 'D:\' 位置找到，如果您要覆盖现有的数据库。

Restore database TestDB from disk = ' D:\TestDB_Full.bak' with replace

如果您要使用此还原命令创建新数据库，并且目标服务器中没有类似的数据、日志文件路径，则使用 move 选项，如下面的命令所示。

确保 D:\Data 路径存在，如以下命令中用于数据和日志文件所示。

RESTORE DATABASE TestDB FROM DISK = 'D:\ TestDB_Full.bak' WITH MOVE 'TestDB' TO 
   'D:\Data\TestDB.mdf', MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'

方法 2 – SSMS（SQL SERVER Management Studio）

步骤 1 − 连接到名为 'TESTINSTANCE' 的数据库实例并右键单击数据库文件夹。单击还原数据库，如下面的快照所示。

步骤 2 − 选择设备单选按钮并单击省略号以选择备份文件，如下面的快照所示。

步骤 3 − 单击确定，将弹出以下屏幕。

步骤 4 − 选择左上角的文件选项，如下面的快照所示。

步骤 5 − 选择左上角的选项，然后单击确定以还原 'TestDB' 数据库，如下面的快照所示。

MS SQL Server - 创建用户

用户指的是 MS SQL Server 数据库中的帐户，用于访问数据库。

可以使用以下两种方法之一创建用户。

方法 1 – 使用 T-SQL

语法

Create user <username> for login <loginname>

示例

要创建用户名 'TestUser' 并将其映射到 TestDB 数据库中的登录名 'TestLogin'，请运行以下查询。

create user TestUser for login TestLogin

其中“TestLogin”是作为登录创建的一部分创建的登录名。

方法 2 – 使用 SSMS（SQL Server Management Studio）

注意 − 在创建用户帐户之前，我们必须首先使用任何名称创建登录名。

让我们使用名为“TestLogin”的登录名。

步骤 1 − 连接 SQL Server 并展开数据库文件夹。然后展开名为“TestDB”的数据库（我们将在其中创建用户帐户）并展开安全文件夹。右键单击用户，然后单击“新建用户”以查看以下屏幕。

步骤 2 − 在用户名字段中输入“TestUser”，然后单击省略号以选择名为“TestLogin”的登录名，如下面的快照所示。

步骤 3 − 单击“确定”以显示登录名。再次单击“确定”以创建“TestUser”用户，如下面的快照所示。

MS SQL Server - 分配权限

权限是指管理主体对可保护对象访问级别规则。您可以在 MS SQL Server 中授予、撤销和拒绝权限。

要分配权限，可以使用以下两种方法之一。

方法 1 – 使用 T-SQL

语法

Use <database name>
Grant <permission name> on <object name> to <username\principle>

示例

要为名为“TestDB”数据库中名为“TestTable”的对象上的名为“TestUser”的用户分配选择权限，请运行以下查询。

USE TestDB
GO
Grant select on TestTable to TestUser

方法 2 – 使用 SSMS（SQL Server Management Studio）

步骤 1 − 连接到实例并展开文件夹，如下面的快照所示。

步骤 2 − 右键单击 TestUser 并单击“属性”。将出现以下屏幕。

步骤 3 单击“搜索”并选择特定选项。单击“对象类型”，选择“表”，然后单击“浏览”。选择“TestTable”并单击“确定”。将出现以下屏幕。

步骤 4 选择“选择”权限下“授予”列的复选框，然后单击“确定”，如上图快照所示。

步骤 5 选择授予“TestUser”的“TestDB”数据库中“TestTable”的权限。单击“确定”。

MS SQL Server - 监控数据库

监视是指检查数据库状态、设置（可以是所有者姓名、文件名、文件大小、备份计划等）。

SQL Server 数据库主要可以通过 SQL Server Management Studio 或 T-SQL 进行监视，还可以通过各种方法进行监视，例如创建代理作业和配置数据库邮件、第三方工具等。

可以检查数据库状态是联机还是处于任何其他状态，如下面的快照所示。

根据以上屏幕，所有数据库都处于“联机”状态。如果任何数据库处于任何其他状态，则将显示该状态，如下面的快照所示。

MS SQL Server - 服务

MS SQL Server 提供以下两种服务，这对于数据库创建和维护是必须的。还列出了可用于不同目的的其他附加服务。

• SQL Server
• SQL Server 代理

其他服务

• SQL Server 浏览器
• SQL Server 全文搜索
• SQL Server Integration Services
• SQL Server Reporting Services
• SQL Server Analysis Services

可以使用以下方法使用上述服务。

启动服务

要启动任何服务，可以使用以下两种方法之一。

方法 1 – Services.msc

步骤 1 − 转到“运行”，键入 services.msc 并单击“确定”。将出现以下屏幕。

步骤 2 − 要启动服务，请右键单击服务，然后单击“启动”按钮。服务将启动，如下面的快照所示。

方法 2 – SQL Server 配置管理器

步骤 1 − 使用以下过程打开配置管理器。

开始→所有程序→MS SQL Server 2012→配置工具→SQL Server 配置管理器。

步骤 2 − 选择服务名称，右键单击并单击“启动”选项。服务将启动，如下面的快照所示。

停止服务

要停止任何服务，可以使用以下三种方法之一。

方法 1 - Services.msc

步骤 1 − 转到“运行”，键入 services.msc 并单击“确定”。将出现以下屏幕。

步骤 2 − 要停止服务，请右键单击服务并单击“停止”。选定的服务将停止，如下面的快照所示。

方法 2 – SQL Server 配置管理器

步骤 1 − 使用以下过程打开配置管理器。

开始→所有程序→MS SQL Server 2012→配置工具→SQL Server 配置管理器。

步骤 2 − 选择服务名称，右键单击并单击“停止”选项。选定的服务将停止，如下面的快照所示。

方法 3 – SSMS（SQL Server Management Studio）

步骤 1 − 连接到实例，如下面的快照所示。

步骤 2 − 右键单击实例名称并单击“停止”选项。将出现以下屏幕。

步骤 3 − 单击“是”按钮，将打开以下屏幕。

步骤 4 − 在上述屏幕上单击“是”选项以停止 SQL Server 代理服务。服务将停止，如下面的屏幕截图所示。

注意

• 我们无法使用 SQL Server Management Studio 方法启动服务，因为由于服务已停止状态而无法连接。

• 在停止 SQL Server 服务时，我们不能排除停止 SQL Service 代理服务，因为 SQL Server 代理服务是依赖服务。

MS SQL Server - 高可用性技术

高可用性 (HA) 是一种解决方案/流程/技术，用于使应用程序/数据库在计划内或计划外停机期间 24x7 可用。

主要而言，MS SQL Server 中有五个选项可以为数据库实现/设置高可用性解决方案。

复制

源数据将通过复制代理（作业）复制到目标。对象级技术。

术语

• 发布服务器是源服务器。
• 分发服务器是可选的，并存储订阅服务器的复制数据。
• 订阅服务器是目标服务器。

日志传送

源数据将通过事务日志备份作业复制到目标。数据库级技术。

术语

• 主服务器是源服务器。
• 辅助服务器是目标服务器。
• 监视服务器是可选的，将由日志传送状态监视。

镜像

主数据将通过网络事务基础复制到辅助，借助镜像端点和端口号。数据库级技术。

术语

• 主体服务器是源服务器。
• 镜像服务器是目标服务器。
• 见证服务器是可选的，用于进行自动故障转移。

群集

数据将存储在共享位置，该位置由主服务器和辅助服务器根据服务器的可用性使用。实例级技术。需要使用共享存储的 Windows 群集设置。

术语

• 活动节点是 SQL 服务正在运行的位置。
• 被动节点是 SQL 服务未运行的位置。

AlwaysOn 可用性组

主数据将通过网络事务基础复制到辅助。数据库级技术组。需要没有共享存储的 Windows 群集设置。

术语

• 主副本是源服务器。
• 辅助副本是目标服务器。

以下是配置 HA 技术（镜像和日志传送）的步骤，但不包括群集、AlwaysOn 可用性组和复制。

步骤 1 − 获取源数据库的一个完整备份和一个 T-log 备份。

示例

要为“TESTINSTANCE”作为主服务器和“DEVINSTANCE”作为辅助 SQL Server 的“TestDB”数据库配置镜像/日志传送，请编写以下查询以在源（TESTINSTANCE）服务器上获取完整备份和 T-log 备份。

连接到“TESTINSTANCE”SQL Server 并打开新查询，然后编写以下代码并执行，如下面的屏幕截图所示。

Backup database TestDB to disk = 'D:\testdb_full.bak'
GO
Backup log TestDB to disk = 'D:\testdb_log.trn'

步骤 2 − 将备份文件复制到目标服务器。

在本例中，我们只有一个物理服务器和两个已安装的 SQL Server 实例，因此无需复制，但如果两个 SQL Server 实例位于不同的物理服务器中，我们需要将以下两个文件复制到安装了“DEVINSTANCE”实例的辅助服务器的任何位置。

步骤 3 − 使用“norecovery”选项在目标服务器上使用备份文件还原数据库。

示例

连接到“DEVINSTANCE”SQL Server 并打开“新建查询”。编写以下代码以还原名为“TestDB”的数据库，该数据库与主数据库（“TestDB”）的名称相同，用于数据库镜像。但是，我们可以为日志传送配置提供不同的名称。在本例中，让我们使用“TestDB”数据库名称。对于两次（完整和 t-log 备份文件）还原，使用“norecovery”选项。

Restore database TestDB from disk = 'D:\TestDB_full.bak'
with move 'TestDB' to 'D:\DATA\TestDB_DR.mdf',
move 'TestDB_log' to 'D:\DATA\TestDB_log_DR.ldf',
norecovery
GO
Restore database TestDB from disk = 'D:\TestDB_log.trn' with norecovery

刷新“DEVINSTANCE”服务器中的数据库文件夹，以查看已还原的数据库“TestDB”，其还原状态如下面的快照所示。

步骤 4 − 根据您的要求配置 HA（日志传送、镜像），如下面的快照所示。

示例

右键单击“TESTINSTANCE”SQL Server（为主服务器）的“TestDB”数据库，然后单击“属性”。将出现以下屏幕。

步骤 5 − 根据您的要求，选择名为“镜像”或“事务日志传送”（以红色框突出显示）的选项，如上图所示，并按照系统本身引导的向导步骤完成配置。

MS SQL Server - 报表服务

报表是一个可显示的组件。

用法

报表基本上用于两个目的 - 公司内部运营和公司外部运营。

Reporting Services

这是一项用于创建和发布各种报表的服务。

以下是开发任何报表所需的三个要求。

• 业务流程
• 布局
• 查询/过程/视图

BIDS（直到 2008 R2 的 Business Intelligence Studio）和 SSDT（从 2012 年开始的 SQL Server Data Tools）是开发报表的环境。

以下是打开 BIDS/SSDT 环境以开发报表的步骤。

步骤 1 − 根据 Microsoft SQL Server 程序组中的版本打开 BIDS/SSDT。将出现以下屏幕。在本例中，SSDT 已打开。

步骤 2 − 转到左上角的“文件”。单击“新建”并选择“项目”。将打开以下屏幕。

步骤 3 − 在以上屏幕中，选择左上角“商业智能”下的“Reporting Services”，如下面的屏幕截图所示。

步骤 4 − 在以上屏幕中，根据您的要求选择“报表服务器项目向导”（它将引导您逐步完成向导）或“报表服务器项目”（它将用于选择自定义设置）来开发报表。

MS SQL Server - 执行计划

执行计划将由查询优化器在统计信息和代数/处理器树的帮助下生成。它是查询优化器的结果，并说明如何执行您的工作/需求。

有两种不同的执行计划 - 估计和实际。

估计执行计划表示优化器视图。

实际执行计划指示执行查询的内容以及执行方式。

执行计划存储在称为计划缓存的内存中，因此可以重复使用。每个计划仅存储一次，除非优化器决定查询执行的并行性。

SQL Server 中有三种不同的执行计划格式 - 图形计划、文本计划和 XML 计划。

SHOWPLAN 是想要查看执行计划的用户所需的权限。

示例 1

以下是查看估计执行计划的步骤。

步骤 1 − 连接到 SQL Server 实例。在本例中，“TESTINSTANCE” 是实例名称，如下面的快照所示。

步骤 2 − 点击上面屏幕上的“新建查询”选项，并编写以下查询。在编写查询之前，选择数据库名称。在本例中，“TestDB” 是数据库名称。

Select * from StudentTable

步骤 3 − 点击上面屏幕上红色方框中高亮显示的符号，以显示估计执行计划，如下面的屏幕截图所示。

步骤 4 − 将鼠标放在上面屏幕上红色方框上方的第二个符号（表扫描）上，以详细显示估计执行计划。将出现以下屏幕截图。

示例 2

以下是查看实际执行计划的步骤。

步骤 1 − 连接到 SQL Server 实例。在本例中，“TESTINSTANCE” 是实例名称。

步骤 2 − 点击上面屏幕上看到的“新建查询”选项，并编写以下查询。在编写查询之前，选择数据库名称。在本例中，“TestDB” 是数据库名称。

Select * from StudentTable

步骤 3 − 点击上面屏幕上红色方框中高亮显示的符号，然后执行查询以显示实际执行计划以及查询结果，如下面的屏幕截图所示。

步骤 4 − 将鼠标放在屏幕上红色方框上方的第二个符号（表扫描）上，以详细显示实际执行计划。将出现以下屏幕截图。

步骤 5 − 点击上面屏幕左上角的“结果”，以获得以下屏幕。

MS SQL Server - 集成服务

此服务用于执行 ETL（提取、转换和加载数据）和管理操作。BIDS（2008 R2 之前的商业智能工作室）和 SSDT（2012 年开始的 SQL Server 数据工具）是开发包的环境。

SSIS 基本架构

解决方案（项目的集合）---> 项目（包的集合）---> 包（ETL 和管理操作的任务的集合）

在包下，以下组件可用：

• 控制流（容器和任务）
• 数据流（源、转换、目标）
• 事件处理程序（发送消息、电子邮件）
• 包资源管理器（包中所有内容的单一视图）
• 参数（用户交互）

以下是打开 BIDS\SSDT 的步骤。

步骤 1 − 根据版本从 Microsoft SQL Server 程序组中打开 BIDS\SSDT。将出现以下屏幕。

步骤 2 − 上面的屏幕显示 SSDT 已打开。转到上面图像左上角的“文件”，然后点击“新建”。选择“项目”，将打开以下屏幕。

步骤 3 − 在上面屏幕的左上角选择“商业智能”下的“Integration Services”，以获得以下屏幕。

步骤 4 − 在上面屏幕中，根据您的需求选择“Integration Services 项目”或“Integration Services 导入项目向导”来开发/创建包。

MS SQL Server - 分析服务

此服务用于分析海量数据并应用于业务决策。它还用于创建二维或多维业务模型。

在 SQL Server 2000 版本中，它被称为 MSAS（Microsoft Analysis Services）。

从 SQL Server 2005 开始，它被称为 SSAS（SQL Server Analysis Services）。

模式

有两种模式：本机模式（SQL Server 模式）和 SharePoint 模式。

模型

有两种模型：表格模型（用于团队和个人分析）和多维模型（用于企业分析）。

BIDS（2008 R2 之前的商业智能工作室）和 SSDT（2012 年开始的 SQL Server 数据工具）是用于处理 SSAS 的环境。

步骤 1 − 根据版本从 Microsoft SQL Server 程序组中打开 BIDS\SSDT。将出现以下屏幕。

步骤 2 − 上面的屏幕显示 SSDT 已打开。转到上面图像左上角的“文件”，然后点击“新建”。选择“项目”，将打开以下屏幕。

步骤 3 − 在上面屏幕中，选择“商业智能”下的“Analysis Services”（如左上角所示）。将弹出以下屏幕。

步骤 4 − 在上面屏幕中，根据您的需求从列出的五个选项中选择任何一个选项来处理 Analysis Services。