数据库原理实验报告(2)

南 京 晓 庄 学 院

《数据库原理与应用》

课程实验报告

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

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

班级： 11软工转本2 学号： 1130708 11130710 姓名： 马琦 乔凌杰

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”(教学管理）数据库的过程。

新建数据库如下图所示：

进入sql server management studio 主界面，选择数据库右击新建数据库。 如何修改数据库

进入sql server management studio 主界面，选择数据库右击属性即可看到数据库信息，可更改数据库基本信息。

修改数据库

删除数据库的方法

进入sql server management studio 主界面，选择数据库右击删除，即可删除数据库。

删除数据库

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

ALTER DATABASE 教学管理 ADD LOG FILE (

Name='jxgl_ext11',

FileName='D:\\MyDB\\db\\jxgl_ex1.ldf', Size=4 )

添加日志文件，为数据库的日志文件更换了路径和逻辑名、大小的日志文件。

ALTER DATABASE 教学管理 ADD FILE (

Name='jxgl_ext1',

FileName='D:\\MyDB\\db\\jxgl_ex1.mdf', Size=4 )

添加数据文件，为数据库的日志文件更换了路径和逻辑名、大小的日志文件。

CREATE DATABASE 教学管理 ON PRIMARY (

Name =JXGL,

FileName='D:\\MyDB\\JXGL_Data.mdf', Size=3MB, MaxSize=100MB, filegrowth=1MB ) LOG ON (

Name=JXGL_Log,

FileName='D:\\MyDB\\JXGL_Log.ldf', Size=1MB,

MaxSize=UNLIMITED, FileGrowth=10% );

创建了数据库和数据库对应的数据文件、日志文件并定义了逻辑名和大小以及增长率等信息。

SQL语句删除数据库 删除了数据库“TM” drop database TM

检查所输入SQL语句有无语法错，确认正确后, 按F5键或单击“执行”按钮，这样就删除了数据库“TM”。

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

进入sql server management studio 主界面，选择服务器对象选项下的备份设备选择设备名称以及文件位置，必须是已经建立的目录。右击选定数据库，选择任务->备份选择添加文件路径，备份成功会提示已备份成功。

使用 SQL Server Management Studio 可以完成4种类型的数据库还原操作： 1、覆盖现有数据库 2、保留复制设置

3、还原每个数据库之前进行提示 4、限制访问还原的数据库

接着回到对象资源管理器中，展开数据库右键单击tm数据库，依次选取任务->还原。选择目标数据库，源数据库和备份集并且需要在选项菜单中勾选覆盖现有数据库才可。 提示：

1、 以上还原、备份方法也可以直接选中要操作的数据库对象。 2、采用上示例方法，也可以将一个 SQL Server数据库备份并导入到另一个 SQL SERVER 数据库服务器中。（可以试着自已扩展下）

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

backup database TM to disk='E:\\myDB\\TM.bak'

(2)还原

restore database TM from disk='E:myDB\\TM.bak'

注意:不能直接进入要备份或者要还原的数据库裏面进行备份和还原

4．实验思考：

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

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

它将数据存储在表和索引中，包含数据库的启动信息，还包含一些系统表，这些表记载数据

库对象及其他文件的位置信息。 次要数据文件

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

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

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

备份（backup）实际就是数据的副本，备份的目的是为了防止不可预料的数据丢 失和应用错误。

转储（restore）是指当数据文件或控制文件出现损坏时，将已备份的副本文件还原到原数据库的过程

数据库转存实现热备份恢复功能。通过修改初始化文件里的路径，实现转存。启 动到MOUNT状态下，将所有数据文件和日志文件通过ALTER DATABASE RENAME FILE '原始路径/名称' TO '当前路径/名称';然后将数据库打开，重建TEMP tablespace即可。

数据转储是数据库恢复中采用的基本技术。

所谓转储即dba定期地将数据库复制到磁带或另一个磁盘上保存起来的过程。 当数据库遭到破坏后可以将后备副本重新装入，将数据库恢复到转储时的状态。 静态转储：在系统中无运行事务时进行的转储操作。静态转储简单，但必须等待 正运行的用户事务结束才能进行。同样，新的事务必须等待转储结束才能执行。 显然，这会降低数据库的可用性。

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

增量转储则指每次只转储上一次转储后更新过的数据。

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

③如果数据或日志文件非空不能删除，查找SQL Server中缩小文件大小的方法。 在SQL Server中，所有对数据库执行的更新操作都会记录在数据库的事务日志文件中，除非将数据库设为可自动收缩的或手动的对数据库进行了收缩，否则事务日志文件将一直增长，直到达到事先设定的日志文件增长上限或用尽所有可用的磁盘空间。

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

? 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企业管理器中的数据库

维护计划向导很方便的实现（一般可以设为在每天夜里业务不繁忙的某个时刻自动执行备份）。

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

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

由于数据文件的大小是随数据库中数据量的增长而增长的，数据库中已删除的数据所占的空间可以供新插 入的数据使用；而在定期执行了事务日志的备份后，我们可以将日志文件的大小控制在一个比较合理的范

围。因此，一般不需要对数据库进行收缩，也不推荐将数据库设为自动收缩模式。 建议仅在以下情况下执行数据库的收缩： 1、磁盘空间不足 2、数据文件很大，但其中只包含较少量的数据（可能是以前有大量数据，但后来删除了很多），并且预期今后数据库中的数据量也不会很大。 3、由于长期未进行事务日志备份，导致事务日志文件过大。 减小事务日志文件大小的另一种方法

首先在该数据库中执行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、事务日志文件收缩完成后，建议立即执行一次数据库的完全备份并根据实际需要制定适当的数据库备份计划。

④思考后续实验过程中，你计划采用哪种方法备份自己的数据库实验操作结果？并说明为什么采用该方法。

数据库备份有四种类型 ? 完全备份 ? 事务日志备份 ? 差异备份 ? 文件备份

采用完全备份

完全备份就是指对某一个时间点上的所有数据(包含用户表、系统表、索引、视图和存储过程等所有数据库对象)或应用进行的一个完全拷贝。

实际应用中就是用一盘磁带对整个系统进行完全备份，包括其中的系统和所有数据。 这种备份方式最大的好处就是只要用一盘磁带，就可以恢复丢失的数据。因此大大加快了系统或数据的恢复时间。

本文来源：https://www.bwwdw.com/article/6b1h.html