实验二数据库的创建、管理、备份及还原实验|江阴雨辰互联

2023年7月30日发(作者：)

《数据库原理与应用》

课程实验报告

实验二数据库的创建、管理、备份及还原实验

所在院(系): 数学与信息学院

班级：

学号：

姓名：

1.实验目的

(1) 掌握分别使用SQL Server Management Studio图形界面和Transact-SQL语句创建和修改数据库的基本方法；

(2) 学习使用SQL Server查询分析窗口接收Transact-SQL语句和进行结果分析。

(3) 了解SQL Server的数据库备份和恢复机制，掌握SQL Server中数据库备份与还原的方法。

2.实验要求

(1) 使用SQL Server Management Studio创建“教学管理”数据库。

(2) 使用SQL Server Management Studio修改和删除“教学管理”数据库。

(3) 使用Transact-SQL语句创建“教学管理”数据库。

(4) 使用Transact-SQL语句修改和删除“教学管理”数据库。

(5) 使用SQL Server Management Studio创建“备份设备”；使用SQL Server Management

Studio对数据库“教学管理”进行备份和还原。

(6) SQL Server 2005数据库文件的分离与附加。

(7) 按要求完成实验报告

3.实验步骤、结果和总结实验步骤/结果

(1) 总结使用SQL Server Management Studio创建、修改和册除“TM”(教学管理）数据库的过程。

利用Microsoft Sql Server Management studio 创建数据库的示例方法如下：

一、打开安装好的Microsoft Sql Server Management studio -在对象资源管理器里找到【数据库】--右键【新建数据库】到下图界面。

1、在数据库名称处输入要创建数据库的名称。

2、表格中的两个路径分别为：

2.1、上为数据库存放物理地址。

2.2、下为数据库日志存放物理地址。

3、初始大小图片中显示的为默认的值，可以根据自已的需求进行修改大小，要修改大小【点击大小值表格】会出现编辑状态文本框。如图3-1和图3-2所示：

图3-1 新建数据库图3-2 更改参数

二、对已经建功立业好的数据库，有时还需要对它的属性参数进行查看和修改。

使用SQL Server Management Studio 图形界面直接修改“教学管理”数据库名为“TM”。

图3-3 数据库重命名

单击数据库文件夹前面的+号，使之展开；用鼠标右击指定的数据库标识，在弹出的快捷菜单中选择“属性”项，在该对话框中选择“文件”项，就可以对数据库文件进行修改。可以增加数据文件，也可以删除数据文件，还可以修改数据文件的逻辑名、大小、增长率等，如图3-4所示：图3-4 数据库属性

三、对数据库进行删除

1. 在对象资源管理器中，连接到 SQL Server 数据库引擎实例，然后展开该实例。

2. 展开“数据库”，右键单击要删除的数据库，再单击“删除”。

确认选择了正确的数据库，再单击“确定”。

图3-5 删除数据库

(2) 总结在实验中为创建、修改和删除“教学管理”数据库所编写的各条T-SQL语句及其完成了什么功能。

 SQL语句建立数据库

假设在F:MyDB建立数据库“教学管理”,先在Ｅ：盘建立一个空目录MyDB。单击常用工具栏的按钮“新建查询”，就可以新建一个数据为引擎查询文档，检查所输入SQL语句有无语法错，确认正确后，按F5键或单击“执行”按钮，就可以执行该SQL语句，创建指定数据库文件位置的数据库。

文件组的参数语法如下:

1、数据库名：数据库的名称，最长为128个字符。

2、PRIMARY:该选项是一个关键字，指定主文件组中的文件。

3、LOG ON:指明事务日志文件的明确定义。

4、NAME:指定数据库的逻辑名称，这是在SQL Server系统中的使用名称，是数据库在SQL

Server中的标识符。

5、FILENAME:指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。

6、SIZE:指定数据库的初始容量大小。

7、MAXSIZE:指定操作系统文件可以增长到的最大尺寸。 8、FILEGROWTH:指定文件每次增加容量的大小，当指定的数据为0时，表示文件不增长。

特别注意：SQL 语句不区分大小写，每一项的分隔符是“逗号“，最后一项没有逗号。

将下面内容输入 [新建查询] 则建立数据库教学管理如图3-6所示：

图3-6 T-SQL创建“教学管理”数据库

 SQL语句修改数据库

使用ALTER DATABASE 语句修改“TM”数据库。

编写T-SQL语句，为前述实验步骤中建立的“教学管理”数据库增加数据文件。如：为数据库增加一个与数据文件F:MyDBJXGL_不同存储路径下（该文件路径中目录必须存在，否则需手动创建）的数据文件，如：'D:JXGL_EXTjxgl_ext_',代码如下图3-7所示：

图3-7 添加数据文件为“TM”数据库增加一个日志文件。增加日志文件使用ADD LOG FILE代码如图3-8所示：

图3-8 添加日志文件

 SQL语句删除数据库

删除了数据库“教学管理”：drop database 教学管理

(3) 总结使用SQL Server Management Studio备份与还原数据库的几种方法。

通过 SQL Server Management Studio 备份与还原数据库

下面分别介绍如何通过 SQL Server Management Studio 进行数据库的备份与还原操作。

1、通过 SQL Server Management Studio 备份数据库

使用 SQL Server Management Studio 进行数据库备份的步骤如下所示：

（1）打开 SQL Server Management Studio，右击需要备份的数据库，从弹出的快捷菜单中依次选择【任务】--【备份】命令，打开【备份数据库】对话框。如下图3-8所示：

图3-8 备份数据库

（2）在【备份数据库】对话框的【常规】页面中根据需要选择需要备份的【数据库】，在本示例选择备份数据库.接下来还可以选择【备份类型】、备份集的名称等相关参数。默认情况下，备份操作会所数据库中的数据备份到 SQL SERVER 数据库的默认工作目录如图3-9所示：

图3-9 数据库默认路径

（3）切换【备份数据库】对话框中的【选项】页面。在此页面中，可以根据需要进行相应的设置。例如，可以根据需要将现有数据备分到现有的备份集中，也可使用数据库中的当前数据覆盖现有的备份集。除此之外。还可以设置备份操作的可靠性选项。

单击【确定】按钮，即可执行备份操作。一旦备份操作结束。 SQL SERVER 数据库系统将弹出名为 Microsoft SQL Server Management Studio 的对话框，提示备份已成功完成。

如下图3-10所示。

图3-10 数据库备份成功

总结：使用 SQL Server Management Studio 可以完成4种类型的数据库还原操作：

1、覆盖现有数据库

2、保留复制设置

3、还原每个数据库之前进行提示

4、限制访问还原的数据库

通过 SQL Server Management Studio 还原数据库

使用 SQL Server Management Studio 进行数据还原操作的步骤如下所示。

（1）右击【数据库】节点，从弹出的快捷菜单中选择【还原数据库】命令，打开【还原数据库】对话框，如下图3-11所示：

图3-11 还原数据库

（2）在【还原数据库】对话框中的【常规】页面中，选择【目标数据库】下拉列表框中的【源数据库】选项。【选择用于还原的备份集】列表框中将显示用于还原TM数据库的可用备份集，且【目标数据库】将会被同时设置为TM，如下图3-12所示：

图3-12 还原源数据库

（3）单击【确定】按钮可还原该数据库。

在备份好数据库后，在另一台电脑上进行备份，则操作步骤如下：

(1)新建一个同备份数据库同名的数据库，在这里我们用的数据库为“教学管理”，把备份好的数据库放在磁盘，如图3-13所示：

图3-13 新建数据库

(2)然后点击“教学管理”数据库，选择“还原数据库”，如图3-14所示：图3-13 还原数据库

（3）在弹出的对话框中，在“目标数据库”中选择“教学管理”数据库，点源原设备”，然后添加文件，如图3-14所示：

图 3-14 源设备还原数据库

（4）选择“选项”，“覆盖现有数据库”，把“原始文件名”的文件地址都还原为新建数据库的各个文件对应得地址，如图3-15所示：图3-15 修改地址

（5）点击“确定”按钮，完成还原数据库操作，如图3-16所示：

图3-16 还原数据库成功

利用 SQL 编程进行数据库备份、还原方法

(1)备份

backup database TM to disk='F:'

(2)还原

restore database TM from disk='F:'

4．实验思考：

①SQL Server 2005物理数据库包含了哪能几种类型的文件以及它们的作用？

SQL Server 2005 数据库具有三种类型的文件：

• 主数据文件

主数据文件是数据库的起点，指向数据库中的其他文件。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是 .mdf。

它将数据存储在表和索引中，包含数据库的启动信息，还包含一些系统表，这些表记载数据库对象及其他文件的位置信息。

• 次要数据文件

除主数据文件以外的所有其他数据文件都是次要数据文件。某些数据库可能不含有任何次要数据文件，而有些数据库则含有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。

• 日志文件

SQL Server具有事务功能，日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个,建立数据库时，SQL Server会自动建立数据库的日志文件。日志文件的推荐文件扩展名是 .ldf。

②数据库备份与转储包含那些原理？

备份（backup）实际就是数据的副本，备份的目的是为了防止不可预料的数据丢

失和应用错误。如图4-1所示：

图 4-1备份工作原理图

转储（restore）是指当数据文件或控制文件出现损坏时，将已备份的副本文件还原原数据库的过程数据库转存实现热备份恢复功能。通过修改初始化文件里的路径，实现转启动到MOUNT状态下，将所有数据文件和日志文件通过ALTER DATABASE RENAMEFILE

'原始路径/名称' TO '当前路径/名称';然后将数据库打开，重建TEMPtablespace即可。

数据转储是数据库恢复中采用的基本技术。所谓转储即dba定期地将数据库复制到磁带或另一个磁盘上保存起来的过程。当数据库遭到破坏后可以将后备副本重新装入，将数据库恢复到转储时的状态。

静态转储：在系统中无运行事务时进行的转储操作。静态转储简单，但必须等待正运行的用户事务结束才能进行。同样，新的事务必须等待转储结束才能执行。显然，这会降低数据库的可用性。

动态转储：指转储期间允许对数据库进行存取或修改。动态转储可克服静态转储的缺点，它不用等待正在运行的用户事务结束，也不会影响新事务的运行。但是，转储结束时后援副本上的数据并不能保证正确有效。因为转储期间运行的事务可能修改了某些数据，使得后援副本上的数据不是数据库的一致版本。为此，须把转储期间各事务对数据库的修改活动登记下来，建立日志文件（log file）。这样，后援副本加上日志文件就能得到数据库某一时刻的正确状态。

转储还可以分为海量转储和增量转储两种方式。

海量转储是指每次转储全部数据库。增量转储则指每次只转储上一次转储后更新过的数据。从恢复角度看，使用海量转储得到的后备副本进行恢复一般说来更简单些。但如果数据库很大，事务处理又十分频繁，则增量转储方式更实用更有效。

③如果数据或日志文件非空不能删除，查找SQL Server中缩小文件大小的方法。

建议仅在以下情况下执行数据库的收缩：

1、磁盘空间不足

2、数据文件很大，但其中只包含较少量的数据（可能是以前有大量数据，但后来删除了很多），并且预期今后数据库中的数据量也不会很大。

3、由于长期未进行事务日志备份，导致事务日志文件过大。

如果当前的数据库文件或日志文件过大，可以使用以下两个命令对其进行收缩：

 DBCC SHRINKDATABASE：收缩指定数据库的所有数据和日志文件的大小

 DBCC SHRINKFILE：收缩数据库的某个指定数据或日志文件的大小

这两个命令可以释放数据库中的空闲空间，并将数据库或指定的数据库文件收缩到指定的大小，但收缩后的数据文件或日志文件的大小不会小于文件中现存的有效数据所占空间的大小。在使用以上命令收缩日志文件的时候需要注意，已写入数据库但未被截断的事务日志记录是不会被收缩的，因为虽然这部分日志记录的信息已经写入数据库文件，但在使用事务日志备份进行数据库还原的时候，还将用到其中的信息。

对于使用简单恢复模型的数据库，事务日志会在每次处理检查点（CheckPoint）时自动被截断。对于使用完全恢复模型或大容量日志记录恢复模型的数据库，事务日志只有在执行日志备份（BACKUP LOG）时才会被截断，这时事务日志中记录的信息被写入事务日志备份文件，而它们所占用的这部分空间被标记为可用（即被截断）。截断事务日志并不会使日志文件变小，但可以将其中的部分空间释放供以后写入新的日志记录使用。若要减少日志文件的物理大小，则要使用上面提到的

DBCC SHRINKDATABASE和DBCC SHRINKFILE命令。

在执行BACKUP LOG语句的时候，还可以使用WITH NO_LOG（或WITH

TRUNCATE_ONLY，含义相同）参数，这时并不真正备份事务日志，而只是截断事务日志中的非活动部分（这和普通的BACKUP LOG语句作用相同）。这适合于剩余磁盘空间不够进行事务日志备份或不打算保留事务日志中的非活动部分用于数据库恢复的情况。

避免事务日志文件增长过快以致用尽所有磁盘空间的现象发生的方法：

一种办法是将数据库设为使用简单恢复模型，这样可以使SQL Server周期性的自动截断事务日志的非活动部分，并回收其占用的空间供以后写入事务日志记录使用。但这将使数据库无法利用事务日志备份还原到即时点，降低了数据库的可靠性，因此一般不应用于生产型数据库。对于生产型数据库，推荐的做法是使用完全恢复模型，并定期进行数据库的完全备份和事务日志备份。例如每周执行一次完全备份，每天执行一次事务日志备份，这可以通过SQL Server企业管理器中的数据库维护计划向导很方便的实现（一般可以设为在每天夜里业务不繁忙的某个时刻自动执行备份）。

通过定期执行数据库的事务日志备份，可以避免日志文件的迅速增大，而使其保持一个比较稳定的大小。

虽然数据库备份文件也会占用很多磁盘空间，但随时可以将这些文件移到其他磁盘上或在不需要它们的时候将其删除，而且可以在出现故障或误操作的时候方便的进行数据库的还原。

减小事务日志文件大小的另一种方法

首先在该数据库中执行CHECKPOINT命令，然后将该数据库分离（Detach），再将与其对应的数据库日志文件（.ldf文件）改名或删除或移动到其他目录下，然后执行sp_attach_single_file_db存储过程或在企业管理器中重新将其附加（Attach）。由于找不到原来的日志文件，SQL Server将自动为该数据库建立一个大小只有504K的日志文件。但这种方法必须暂时将数据库脱机，因此一般不适宜在生产环境中使用。

如果当前数据库的事务日志文件过大，必须对其进行收缩的话，建议参照以下的步骤：

1、建议首先备份数据库（但不是必需的）：BACKUP DATABASE database_name TO

backup_device

2、备份事务日志：BACKUP LOG database_name TO backup_device

如果不需要当前事务日志中的记录进行数据库还原或没有足够的空间进行事务日志备份的

的话，也可仅执行以下命令截断事务日志：BACKUP LOG database_name WITH

NO_LOG

3、收缩事务日志文件：DBCC SHRINKFILE (log_file_name)其中log_file_name是事务日志文件的逻辑名称，可以在企业管理器中数据库属性的“事务日志”页中看到（如Northwind数据库的默认事务日志文件逻辑名称为Northwind_log）。

4、如果日志文件仍然较大的话，可以尝试重复执行一次

BACKUP LOG WITH NO_LOG和DBCC SHRINKFILE命令。

5、如果这时仍没有明显的效果，请执行DBCC OPENTRAN (database_name)检查当前数据库中是否存在长时间未提交的活动事务。有必要的话，可以断开这些连接并重新尝试截断事务日志和收缩日志文件。

6、事务日志文件收缩完成后，建议立即执行一次数据库的完全备份并根据实际需要制定适当的数据库备份计划。