1.1 系统数据库概述
1.1.1数据库引擎：
数据库引擎是（SQL Server Database Engine，SSDE）是SQL Serve系统的核心服务，负责完成业务数据的存储、处理、查询和安全管理等操作
1.1.2系统数据库：
1. master数据库：
①**“数据库的数据库”**
②记录SQL Server**系统的所有系统级信息**
③SQL Server的**初始化信息**
2. msdb数据库
①存储计划信息
②存储备份和恢复相关的信息，尤其是SQL Serve代理需要使用它来执行安排工作和警报，记录操作者等操作。
3. model数据库
①**创建的所有数据库的模板。**
②当执行CREATE DATABASE语句时，将通过复制 model 数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。
③如果修改model数据库，之后创建的所有数据库都将继承这些修改。
4. tempdb数据库
①保存所有临时表和临时存储过程。
②每次启动SQL Server时，都要重新创建tempdb。
1.2 创建数据库（重点）
1.2.1 SQL Server Management Studio创建数据库
优点：简单直观
1.2.2 Transact-SQL语句创建数据库
优点：可以将创建数据库的脚本保存下来，在其他计算机上运行以创建相同的数据库
create database test_tqon primary --默认就属于primary文件组，可省略(/*--数据文件的具体描述--*/name='test_tq', --主数据文件的逻辑名称filename='E:\sqlserver2012\test_tmp\test_tq.mdf', --主数据文件的物理名称size=5mb, --主数据文件的初始大小maxsize=100mb, --主数据文件增长的最大值filegrowth=15% --主数据文件的增长率(默认按10%增长)),(/*--数据文件的具体描述--*/name='test_tq2', --次要数据文件的逻辑名称filename='E:\sqlserver2012\test_tmp\test_tq2.ndf', --次要数据文件的物理名称size=5mb, --次要数据文件的初始大小maxsize=100mb, --次要数据文件增长的最大值filegrowth=15% --次要数据文件的增长率)log on(/*--日志文件的具体描述，各参数含义同上--*/name='test_tq2_log', --日志数据文件的逻辑名称filename='E:\sqlserver2012\test_tmp\test_tq2_log.ldf', --日志数据文件的物理名称size=2mb, --日志数据文件的初始大小filegrowth=1mb --日志数据文件的增长大小)
关键字解释：
PRIMARY:指明主文件组中的主要数据文件。一个数据库中只能有一个主要数据文件，如果省略PRIMARY关键字，则系统指定语句中的第一个文件为主要数据文件。
LOG ON ：指明事务日志文件的明确定义。如果不指定，系统会自动创建日志文件。
NAME：数据文件或日志文件的逻辑文件名。
FILENAME：物理文件名。
SIZE：指定数据文件或日志文件的初始大小，默认单位是MB。
MAXSIZE：最大容量。缺省该项则默认为UNLIMITED。
FILEGROWTH：增长幅度。默认单位为MB。如果没指定该项 ，则默认按10%增长。
1.3 数据文件和文件组
SQL Server将数据库映射为一组操作系统文件，数据和日志信息分别存储在不同的文件中 。
文件：数据最终存放在计算机硬盘（存储装置）上的物理文件。而数据和日志都是保存在这些文件里面的。
数据文件：保存数据。用于存储数据库中所有对象，如表、视图、存储过程等。分为主要数据文件和次要数据文件。
日志文件：保存数据库的操作记录。
| 文件 | 说明 |
|---|---|
| 主要数据文件 | 包含数据库的**启动信息和数据库中其他文件的指针，文件扩展名为.mdf。每个数据库必须且只有一个主文件，该文件存放两种对象，即用户对象和系统对象**。用户对象包含表、视图及存储过程等，用于保存用户输入的数据；系统对象有表名、数据库用户帐号、索引地址等维护数据库工作所需要的信息。 |
| 次要数据文件 | 次要数据文件主要**存储用户数据，文件的扩展名为.ndf。辅助存放用户数据，但不能存放系统对象。使用次要数据文件可以让数据库文件不断得到扩充，并可以通过将不同的文件存放到不同的磁盘空间中实现同时对多个磁盘进行访问**，从而提高数据存储的并发性。 |
| 事务日志文件 | SQL Sever主要通过事务日志文件来实现数据的恢复，文件扩展名为.ldf。数据库必须至少包含一个事务日志文件，每个日志文件仅能被一个数据库所拥有，数据和日志信息不应混在一起。 |
增加文件组合文件：
use master --指定使用的数据库
go
--向数据库增加新文件组
alter database Stu_db3 add filegroup file_group2
go
--向数据库中增加新文件
alter database Stu_db3 add file(name=N'new_datafile3',filename=N'E:\sqlserver2012\test_tmp\new_datafile3.ndf',size=3072kb,filegrowth=1024kb)to filegroup file_group2 --制定文件所在文件组
删除文件和文件组：
use Stu_db3
go
alter database Stu_db3 remove file new_datafile3 --不加扩展名
注：使用T-SQL语言删除文件祖时必须先删除文件组中包含的所有文件，否则报错。
修改文件和文件组属性：
use Stu_db3
go
alter database Stu_db3
modify filegroup file_group2 name=anygroup --修改文件组名称
use Stu_db3
go
alter database Stu_db3
modify filegroup anygroup readonly --修改文件组中全部文件全部只读
use Stu_db3
go
alter database Stu_db3
modify file(name=Stu_db3,newname=anyname) --修改文件名称
1.4 管理数据库
1.4.1删除数据库
注意：
1、系统数据库master、tempdb、model、msdb不能删除
2、正在使用的数据库不能删除
3、数据库被删除之后，文件及其数据都从服务器上的磁盘中被删除。一旦删除数据库，它将被永久删除，所以删除需谨慎。
1.4.2修改数据库
修改文件大小：
use test_tq
go
alter database test_tq --修改文件大小
modify file
(name=test_tq2,size=30mb
)
移动文件位置：
use test_tq
go
alter database test_tq --移动文件位置
modify file
(name=test_tq2,filename=N'E:\sqlserver2012\test_move\test_tq2.ndf'
)
**错误信息：**无法打开物理文件“E:\sqlserver2012\test_move\test_ms.mdf”。操作系统错误 5:“5(拒绝访问。)”。
错误原因是因为移动文件路径后，文件的操作权限不足
解决办法：更改权限，选中.mdf文件-----右击-----安全------ Authenticated Users----编辑----安全控制允许
修改数据库名称：
use test_ms
go
alter database test_ms modify name=test_ms_new --将数据库名test_ms更改为test_ms_new
更改数据库选项：
ALTER DATABASE Stu_db2 SET SINGLE_USER --单用户
ALTER DATABASE Stu_db2 SET READ_ONLY –只读
ALTER DATABASE Stu_db2 SET AUTO_SHRINK ON --自动收缩
GO
扩展数据库三种方式：
1、设置数据库为自动增长方式2、增加数据库中的数据文件和日志文件的大小，也就是修改它们的MAXSIZE属性3、为数据库增加新的次要数据文件或日志文件
收缩数据库：
设置数据库为自动收缩，通过设置AUTO_SHRINK数据库选项。
ALTER DATABASE Stu_db2 SET AUTO_SHRINK ON
手动执行DBCC SHRINKDATABASE语句来收缩整个数据库的大小。
DBCC SHRINKDATABASE('要收缩的数据库名','可用空间的比例')
注意：不能将整个数据库收缩到比其原始大小还要小！
1.4.3分离和附加数据库
**适用场合：**如果要将数据库更改到同一计算机的不同SQL sever实例或要移动数据库，分离和附加数据库最合适。
分离数据库是指将数据库从SQL Sever实例中删除，但使数据库在其数据文件和事务日志文件中保持不变，之后，就可以使用这些文件将数据库附加到任何SQL Sever实例，包括分离该数据库发服务器。
两种方法：一、指定的数据库右键，任务→分离。
二、T-SQL语言
--分离
use master
go
exec sp_detach_db test_ms_new
--附加
use master
go
create database test_ms_new on
(filename='E:\sqlserver2012\test_tmp\test_ms.mdf'
),
(filename='E:\sqlserver2012\test_tmp\test_ms_log.ldf'
)
for attach
1.4.4移动和复制数据库
注意：仅适用于同一SQL Sever实例内移动数据库文件。若将数据库移动到另一个SQL Sever实例中或另一台服务器上，请使用备份和还原或分离和附加操作。
移动数据库文件步骤：
1、在执行文件移动前先将数据库状态设置为OFFLINE
use master
go
alter database test_ms_new set OFFLINE
2、将所有的文件在windows资源管理器中移动到新的位置
3、对于已移动的每个文件，执行以下语句：
use master
go
alter database test_ms_new
modify file(name='test_ms',filename='E:\sqlserver2012\test_move\test_ms.mdf'
)
4、设置完成后，恢复数据库的状态为ONLINE
use master
go
alter database test_ms_new set ONLINE
错误信息：无法打开物理文件“E:\sqlserver2012\test_move\test_ms.mdf”。操作系统错误 5:“5(拒绝访问。)”。
错误原因是因为移动文件路径后，文件的操作权限不足
解决办法：更改权限，选中.mdf文件-----右击-----安全------ Authenticated Users----编辑----安全控制允许
1.4.5备份和还原数据集
备份：
还原：
报错：
解决方法：把限制访问改为：SINGLE_USER
改完如图：
重新还原：还原成功
本文链接：https://my.lmcjl.com/post/12038.html
4 评论