SQL Server数据库（创建及管理）

本文主要是介绍SQL Server数据库（创建及管理），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、数据库文件：系统数据库+用户数据库

1、系统数据库：

随程序一起安装，用户提供系统所需要的数据的数据库，是SQL Server运行的基础。
1）master数据库（主数据库）
用于记录SQL Server的所有系统级别的信息，若被损坏，SQL Server将无法正常工作。

• 所有用户登录名及用户id所属的角色
• 用户数据库配置设置
• SQL Server服务器配置
• 所有的系统配置（例如端口配置、安全实现、恢复模式等）
• SQL Server 的初始化信息

2）model数据库（模板数据库）
用户创建新数据库的模板。
当创建用户数据库时，系统自动将model数据库的内容复制到用户数据库中，所以可以通过修改model数据库对创建的数据库建立一个自定义的配置。
3）msdb数据库（调度数据库）
主要用于计划警报、作业、复制与恢复等活动。
4）tempdb数据库（临时数据库）
主要用于存储用户的一些临时数据信息（如用户建立的临时表、临时存储过程和全局变量值等），仅存在于SQL Server会话期间，一旦会话结束，则将关闭tempdb数据库，并且丢失该数据库。

2、用户数据库：

由用户创建，用于保存用户应用程序数据的数据库。

二、数据库文件和文件组：

1、数据库文件

数据库文件包括数据文件和事务日志文件。

1）数据文件：
数据文件包括主数据文件和次数据文件。
主数据文件：一个数据库有且仅有一个主数据文件，用来存储数据库的启动信息以及部分或全部数据。
次数据文件：一个数据库可以有次数据文件，也可以没有。当一个数据库的数据文件太大时，可以通过使用次数据问及那使数据库文件变小，还可以将数据存储到不同的磁盘上来对数据进行分散，加快数据的存取速度。
2）事务日志文件：一个数据库必须至少有一个事务日志文件，主要用于恢复数据库日志信息。当数据库发生意外时，可以通过数据库备份和事务日志文件来恢复数据库。

2、数据库文件组

文件组是数据的集合。每个数据库在创建时都会默认包含一个文件组（包含主数据文件和次数据文件）。默认文件组（又称为主文件组），一个数据库有且仅有一个主文件组，并且默认添加的数据文件都属于该组。此外，用户可以自定义文件组。
注意：

• 一个文件或文件组只能用于一个数据库，不能用于多个数据库。
• 一个文件只能是某一个文件组的成员，而不能是多个文件组的成员。
• 日志文件不属于任何文件组。

三、数据库创建与管理

1、创建数据库

1）SQL 语句
【语句格式】

Create datbase <数据库名>
[on
{[primary](name=<数据文件逻辑文件名>,
filename='<数据文件物理文件名>'
[,size=<数据文件大小>]
[,maxsize=<数据文件最大尺寸>]
[,filegrowth=<数据文件增量>])
}[,...,n]
]

[log on
{(name=<事务日志文件逻辑文件名>,
filename='<事务日志文件物理文件名>'
[,size=<事务日志文件大小>]
[,maxsize=<事务日志文件最大尺寸>]
[,filegrowth=<事务日志文件增量>])

}[,...,n]

【参数说明】

<>：表示<>中内容是必选项，不能省略 []：表示[]中内容是可选项，可省略
Create datbase <数据库名>：创建数据库并指定数据库名字。
on：指定存放数据库的数据文件信息。 log on：指定生成事务日志文件的文件信息。
primary：指定文件为主数据文件。 name：指定数据文件/事务日志文件的逻辑文件名。
filename：指定数据文件/事务日志文件的物理文件名，包括路径和名称。
size：指定数据文件/事务日志文件的初始容量大小，默认1MB。
maxsize：指定数据库数据文件/事务日志文件可以增长的最大尺寸，直至数据充满整个硬盘。
filegrowth：指定数据文件/事务日志文件每次增长大小，默认10%。

所有的数据库文件都有两个文件名，即逻辑文件名和物理文件名。逻辑文件名是SQL语句中引用数据库文件时所用到的名称，物理文件名是两个文件在存储磁盘上的路径和名称。

2）可视化方式
启动数据库，右击【数据库】，选择【新建数据库】

得到【新建数据库】窗口，如图，在【常规】选择页中的“数据库名称”文本框中输入数据库名字。
在“所有者”中输入或选择数据库的所有者，对当前数据库有完全操作权限的用户，默认是当前登录Windows系统的管理员账户。
在“数据库文件”列表框中可以设置该数据库的初始大小、自动增长和路径。
最后，单击【确定】完成数据库的创建。


右击【刷新】，就可以新创建的数据库。

2、修改数据库

1）SQL语句
【语句格式】

Alter database<>
{add file<文件格式>[,...n][to filegroup<文件组名>]
|add log file<文件格式>[,...n]
|remove file<逻辑文件名>
|add filegroup<文件组名>
|remove filegroup<文件组名>
|modify file<文件格式>
|modify filegroup<文件组名><文件组属性>
|modify name=<新数据库名>
}
<文件格式>::=
(name=<逻辑文件名>
[,filename='<物理文件名>']
[,size=<文件大小>]
[,maxsize={<文件最大尺寸>|unlimited}]
[,filegrowth=<文件增量>])

【参数说明】

add file：指定要添加的数据文件。
to filegroup：指定将文件添加到哪个文件中。
add log file：指定添加的事务日志文件。
remove file：指定从数据库中删除的文件。
add filegroup：指定添加的文件组。
remove filegroup：指定从数据库中删除文件组及其中的所有文件。
modify file：指定如何修改给定的文件。
modify filegroup：指定将文件组应用于该文件组。
modify name：指定数据库的新名字。

举例：修改AAA数据库名为“BBB”，

alter database AAA
modify name=BBB

该语句在修改数据库名称时只是修改了数据库的逻辑名称，对该数据库的数据文件和事务日志文件没有任何影响。

【补充】使用系统存储过程对数据库名进行修改：

sp_renamedb '<旧数据库名>','<新数据库名>'

2）可视化方式
右击要修改的数据库名，选择【属性】，

【数据库属性】窗口如图所示，

• 点击【文件】选项页，在该页面中可以修改数据库所有者，默认情况下，数据库的所有者为数据库的创建者，也可以在创建数据库之后修改数据库的所有者。

• 单击【添加】或【删除】按钮，可以新增或删除数据库的数据文件和事务日志文件。
  （若是修改数据库文件，则不能对文件类型、所属文件组和路径3项进行修改，只能修改初始大小和自动增长值。主数据文件不能删除，事务日志文件必须保留一个。）

• 选择【文件组】选项页，用户可以指定默认文件组、添加新文件组、修改现有文件组和删除文件组。
  （新建的文件组不能设置为默认文件组，因为它没有包含任何文件，Primary文件组不能设置为只读。）

• 选择【选项】选项页，用户可以设置与修改数据库的排序规则、恢复模式及兼容级别。

• 选择【权限】选项页，用户可以查看或设置数据库安全对象的权限。

3、删除数据库

1）使用SQL语句删除
【语句格式】

Drop database<数据库名>[,...,n]

（可以同时删除多个数据库，只要在数据库名之间用逗号隔开）

2）使用可视化方式

• 右击要删除的数据库，选择【删除】命令。
• 弹出【删除对象】窗口，在该窗口下方可以看到默认选择的【删除数据库备份和换原历史记录信息】复选框，该复选框表示在删除数据库的同时，系统将会该数据库的备份和还原历史记录从msdb系统数据库中删除。
• 不能删除系统数据库和当前正在使用的数据库。删除数据库时，会从磁盘上物理删除数据库文件和事务日志文件，除非数据库做了备份，否则无法恢复。若数据库正在使用，仍有活动连接着，必须选择【关闭现有连接】复选框，否则在删除数据库时将会出现错误信息。
  

4、分离数据库

分离数据库，指将数据库从SQL Server2008实例中删除，将要分离的数据库与数据库管理系统脱离管理关系，但该数据库文件和事务日志文件保持不变，此时，可以将该数据库附加到其他实例中。
分离数据库时不允许存在下列任一情况：

• 数据库中存在快照；
• 必须先删除所有数据库快照，再分离数据库；
• 数据库是系统数据库；
• 该数据库已经复制并发布，如果进行复制，则数据库必须是未发布的，即首先执行系统存储过程sp_reolicationdboption，禁用发布后再进行分离。

1）使用系统存储过程分离数据库
【语句格式】

Exec sp_detach_db '<数据库名>'

举例，分离数据库student

Exec sp_detach_db 'student'

（若有进程正在使用该数据库，分离将会失败。可以先查看有哪些进程正在使用该数据库，首先在查询编辑器窗格中输入以下代码1来查看用户和进程信息，单击【执行】之后，出现使用该数据库的用户和进行信息。）
代码1：

Use master
Exec sp_who

在【结果】窗格里可以看到正在使用“student”的进程，记住这些进程的编号，用kill语句结束这些进程。

假设正在使用“student”进程的编号有12和21，在查询编辑器窗格中输入以下代码：

Use master
kill 12
kill 21
Exec sp_detach_db 'student'

2）使用可视化方式分离数据库


打开【分离数据库】窗口，如果【状态】列显示“就绪”，就表示可以正常分离，如果是“未就绪”，则【消息】列将显示有关数据库的超链接信息。当数据库设计复制时，【消息】列将出现database replicated。
数据库有一个或多个活动连接时，【消息】列将显示“<活动连接数>活动连接”。
在进行数据库分离之前，必须启动【删除连接】复选框来断开与所有活动的连接。

最后，单击【确定】按钮，完成分离操作。然后刷新【对象资源管理器】窗格中的内容，会发现该数据库已经不存在了，这就表示分离成了。

5、附加数据库

附加数据库，指将分离的数据库重新添加到当前数据库服务器或不同的数据库服务器中。
附加数据库时，所有的数据库文件（.mdf和.ndf文件）都必须有效可用。若任何数据文件的路径与所创建数据库的路径或上次附加数据库时的路径不同，则必须指定文件的当前路径。
1）使用SQL语句附加数据库
附加student数据库：

Use master
Create database student
on(filename='d:\student.mdf')
for attach

（若曾经移动过数据文件或事务日志文件，附加数据库时必须指定主数据文件和所有移动过文件的位置，包括事务日志文件。）

补充：若在附加过程中出现“无法打开物理文件xxx.mdf。操作系统错误5：5（拒绝访问。)”，解决方案如下，找到xxx.mdf与xxx_log.ldf文件，右击，选择【属性】|【安全】命令，在【组或用户名】处添加“Authenticated
Users”，更改该组权限为【完全权限】，再次附加即可。
点击查看我在附加数据库时遇到的问题以及解决方案

2）使用系统存储过程附加
使用系统存储过程sp_attach_db附加数据库student

Use master
Exec sp_attach_db,'d:\example\student.mdf'

若移动过数据库文件，则要为主数据文件和其他所有移动过位置的文件指定位置，如

Use master
Exec sp_attach_db,
'd:\example\student.mdf'
'd:\example\student_log.ldf'

(系统存储过程sp_attach_db最多只能列出16个文件，如要列出的文件超过16个，则只能用Create database语句来附加数据库。）

3）使用可视化方式附加
右击【数据库】，单击【附加】，打开【附加数据库】窗口

单击【添加】，弹出【定位数据库文件】对话框

在此对话框中，默认只显示数据库的数据文件（即.mdf文件）。因为在数据库的主数据文件中会存放其他文件的相关信息，在附加数据库时，只要指定主数据文件，其他文件的位置就知道了，但，如果在数据库分离之后，这些文件被移动过位置，就会出现“找不到”的提示，这时就需要手动查找文件。

单击【确定】完成附加操作，我们可以在【对象资源管理器】窗格里看到新附加的数据库。

这篇关于SQL Server数据库（创建及管理）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！