第五章数据库管理课件.docx

第五章数据库管理课件.docx

《第五章数据库管理课件.docx》由会员分享，可在线阅读，更多相关《第五章数据库管理课件.docx（13页珍藏版）》请在冰点文库上搜索。

第五章数据库管理课件

第八章SQLSERVER数据库系统管理

一、SQLServer2005的DBA的职能简介：

1、要对Windows操作系统有一定的了解：

建立用户和组、管理权限、创建共享、应用网络服务等。

2、对SQL语言有深入的了解。

3、建立、使用数据库。

4、管理数据库用户、用户的权限。

5、备份和恢复数据。

6、保证数据库的安全。

二、关于移动、复制数据库：

1、移动数据库、或者说是剪切数据库（如从D盘移动到C盘）。

是为了：

把数据库从一台计算机移动到另一台计算机；或从一块磁盘移动到另一块磁盘中；或从一个分区移动到另一个分区，而不用重新建数据库。

在SQLServer中可以用分离（Detach）和附加（Attach）的方法来移动数据库。

1）、先分离数据库：

Sp_detach_db数据库名称

例：

sp_detach_dbbbb

2）、用剪切将bbb库的文件从D盘移动到C盘一个文件夹下。

3）、附加数据库：

Sp_attach_db新数据库名称，所有的数据文件的新物理地址

例：

sp_attach_dbbzx,'d:

\bzx\bzx_mdf.mdf','d:

\bzx\bzx_ndf.ndf',

'd:

\bzx\bzx_ldf.ldf'

2、复制数据库（如：

在E盘建一个一样的库）。

1）、将bbb库的文件从D盘复制到C盘一个文件夹下。

2）、附加数据库。

其实移动和复制数据库只是差了分离一步，这主要是从安全性考虑，能够使这个数据库保证永远是唯一的。

例：

（将数据库student从d盘移动到G盘的文件夹下）

1先创建一个数据库:

Createdatabasestudent

On

（Name=student_mdf,

Filename='d:

\sql\student_mdf.mdf',

Size=3,

Maxsize=10,

Filegrowth=1）,

（Name=student_ndf,

Filename='d:

\sql\student_ndf.ndf',

Size=3MB,

Maxsize=30MB,

Filegrowth=20%）

Logon

（Name=student_ldf,

Filename='d:

\sql\student_ldf.ldf',

Size=3,

Maxsize=5,

Filegrowth=1）

2、分离数据库student：

sp_detach_dbstudent

3、用剪切将d盘的三个文件移动到G盘的文件夹bzx下。

4、附加，给新的数据库起名为bzx：

sp_attach_dbbzx,'g:

\bzx\student_mdf.mdf','g:

\bzx\student_ndf.ndf',

'g:

\bzx\student_ldf.ldf'

三、数据库的备份与恢复工作：

备份是指制作数据库结构和数据库的拷贝，以便在数据库遭到破坏时能够修复数据库。

2-1、备份数据库的频繁程度取决于数据库活动情况：

1、如果系统处于一个在线事务处理环境中，就要经常性地备份数据库。

2、如果系统活动不多，或主要用于决策支持，就不用太频繁备份。

3、备份要尽量安排在没有大量更新活动的时候。

2-2、SQLServer系统提供了最常用的3种类型备份：

全库备份，差异备份，事务日志备份。

1.完全数据库备份（全库备份）

这是最完整的数据库备份方式，它会将数据库内所有的对象完整地拷贝到指定的设备上。

由于它是备份完整内容，因此通常会需要花费较多的时间，同时也会占用较多的空间。

对于数据量较少，或者变动较小不需经常备份的数据库而言，可以选择使用这种备份方式。

2.差异数据库备份

差异数据库备份只会针对自从上次完全备份后所有变动的部分进行备份处理，这种备份模式必须搭配完全数据库备份一起使用，最初的备份使用完全备份保存完整的数据库内容，之后则使用差异备份只记录有变动的部分。

由于差异数据库备份只备份有变动的部分，因此比起完全数据库备份来说，通常它的备份速度会比较快，占用的空间也会比较少。

对于数据量大且需要经常备份的数据库，使用差异备份可以减少数据库备份的负担。

若是使用完全备份搭配差异备份来备份数据库，则在还原数据库的内容时，必须先加载前一个完全备份的内容，然后再加载差异备份的内容。

例如，假设我们每天都对数据库“Trade”做备份，其中星期一到星期六做的是差异备份，星期天做完全备份，当星期三发现数据库有问题，需要将数据库还原到星期二的状况时，我们必须先将数据库还原到上星期天完全备份，然后再还原星期二的差异备份。

3.事务日志备份

事务日志备份与差异数据库备份非常相似，都是备份部分数据内容，只不过事务日志备份是针对自从上一次备份后有变动的部分进行备份处理，而不是针对上一次完全备份后有变动部分的处理。

若是使用完全备份配合事务日志来备份数据库，则在还原数据库内容时，必须先加载前一个完全备份的内容，然后再按顺序还原每一个事务日志备份的内容。

2-3、数据库的恢复模式分为三类：

完全恢复模式（full），大容量日志恢复模式（bulk-logged），简单恢复模式（simple）。

完全恢复模式：

备份所有的日志和数据，支持完全性的恢复。

完全恢复模式可以恢复到任一时间点状态。

如果选择了完全恢复模式，则在还原数据库之前一定要先作尾部日志备份。

简单恢复模式：

它可以最低程度地保证备份恢复后数据的一致性，它通过除去日志开销来简化数据库备份和还原（也就是不还原日志备份），同时这种简化也可能造成部分数据的丢失。

最重要的是它不还原日志文件，所以节省空间。

其它的与完全恢复模式下的全库备份、差异备份是一样的。

简单恢复模式适使用于测试数据和只读数据库中，不可恢复到任一时间点状态。

大容量日志恢复模式：

简化了日志的操作（如大规模创建索引的还原），这样还原数据库的效率就会提高，并且减少了备份日志文件所需的空间。

但同样会造成部分数据的丢失，它只能恢复到最后日志的数据库状态。

大容量日志恢复模式比完全恢复模式的效率要高一些，需要的是最小的日志存储空间，但不可恢复到任一时间点，且有数据丢失。

当然这三种模式各有特点，所以在选择时要权衡以下因素：

数据库的性能，数据丢失的容忍程度，事务日志存储空间需求，备份和恢复过程的简化。

简单恢复模式一般用于测试数据和只读数据库；完全恢复模式灵活性很强，可以把数据库恢复到过去某时间点的状态；大容量日志恢复模式的效率要比完全恢复模式要高，需要的存储空间少，但不是很灵活。

当执行一个备份时，必须首先创建装载此备份的备份设备。

（可以是永久的或是临时的）。

另外我们的这些工作是针对于服务器、库这一级的操作，所以备份、恢复操作最好都在Master库下完成。

数据库的备份与恢复：

1、备份：

1）、创建永久性备份设备（备份文件）：

在用于一个备份操作之前就已创建了的文件称为永久性备份文件。

它们也叫作备份装置。

如果打算重复使用创建的备份文件或自动化备份数据库的任务，就必须要创建永久性备份文件。

语法：

Sp_addumpdevice存储文件的设备类型，备份文件的名称，文件的存储路径

例：

Sp_addumpdevice'disk',stuback,'d:

\sql\stuback.bak'

说明：

●存储文件的设备类型：

一般为disk

●文件的存储路径：

指的是备份文件存放的物理位置。

2）、创建临时备份设备（备份文件）：

如果不打算复用备份文件，则可创建临时性的备份文件。

当做一个数据库的一次性备份或测试备份计划时，可以使用临时性备份文件。

BACKUPDATABASE数据库名todisk=物理文件名

也就是说不用提前先创建备份文件了。

这种形式一般很少用。

一、全库备份：

这是最简单的备份形式。

创建全库备份的语法：

方法一、BACKUPDATABASE数据库名to逻辑文件名

方法二、BackupDataBase数据库名todisk=物理文件名

还原全库备份语法：

RESTOREDATABASE数据库名FROM备份文件名

例1：

1、先创建一个备份设备（备份文件）：

Sp_addumpdevice'disk',stuback,'d:

\sql\stuback.bak'

2、作stu库的全库备份：

Backupdatabasestutostuback

或：

Backupdatabasestutodisk='d:

\sql\stuback.bak'

可以选择任一种方法进行备份。

3、在stu库中建一个表abc。

Createtableabc（aint）

4、将stu库恢复：

restoredatabasestufromstuback

5、后在stu库中：

select*fromabc看是否还存在，是否被还原了。

例2：

其实，在作全库备份的时候，由于不只是一次的备份，所以有很多时候我们需要恢复到备份某一个时间点、或是某一次的备份，这样就给了我们更多的选择，如：

恢复到1月1日的备份，恢复到1月5日的备份，还是1月10日的备份。

Sp_addumpdevice'disk',stu8back,'d:

\sql\stu8back.bak'

createdatabasestu8

usestu8

Backupdatabasestu8tostu8back--第一个备份

createtablea（aint）

Backupdatabasestu8tostu8back--第二个备份

createtableb（bint）

Backupdatabasestu8tostu8back--第三个备份

createtablec（cint）

Backupdatabasestu8tostu8back--第四个备份

createtabled（dint）

Backupdatabasestu8tostu8back--第五个备份

createtablee（eint）--出现故障

restoredatabasestu8fromstu8backwithfile=1

usestu8

sp_help

--可以看到还没有usertable，相当于1月1日

restoredatabasestu8fromstu8backwithfile=2

usestu8

sp_help

--可以看到有usertablea，相当于1月5日

restoredatabasestu8fromstu8backwithfile=3

usestu8

sp_help

--可以看到有usertablea、b，相当于1月10日

restoredatabasestu8fromstu8backwithfile=4

usestu8

sp_help

--可以看到有usertablea、b、c，相当于1月15日

restoredatabasestu8fromstu8backwithfile=5

usestu8

sp_help

--可以看到有usertablea、b、c、d，相当于1月20日

以上可以看到，可以恢复到一个自定的还原点，所以要作好记录，以便在恢复时，恢复到需要的还原点。

withfile=1，此时的索引号为1，这也是系统的默认值，所以可以省略。

二、差异备份

1、差异备份格式：

BACKUPDATABASE数据库名to备份文件名withdifferential

说明：

WITHDIFFERENTIAL：

表示此备份为差异备份。

2、恢复差异备份的格式：

RESTOREDATABASE数据库名FROM备份文件名[withNORECOVERY|RECOVERY]

说明：

●备份文件名：

可以是临时的，也可以是永久的；可以是物理名，也可以是逻辑名。

●NORECOVERY：

当要作多步恢复时，应使用此选项。

RECOVERY：

该选项用于恢复最后一个文件时使用。

注：

差异备份要在全库备份的基础之上来作。

例：

sp_addumpdevice'disk',stu9back,'d:

\sql\stu9back.bak'--创建备份文件

createdatabasestu9

backupdatabasestu9tostu9back--开始在备份文件上作全库的备份

usestu9

createtableabc（aint）

backupdatabasestu9todisk='d:

\sql\stu9back.bak'withdifferential--差异备份第一次

usestu9

createtableabc2（aint）

backupdatabasestu9todisk='d:

\sql\stu9back.bak'withdifferential--差异备份第二次

restoredatabasestu9fromstu9backwithnorecovery--先恢复全库备份

restoredatabasestu9fromstu9backwithfile=2,recovery/*恢复到第一次差异备份后的状态*/

usestu9

sp_help

此时只会看到表abc。

restoredatabasestu9fromstu9backwithfile=1,norecovery--先恢复全库备份

restoredatabasestu9fromstu9backwithfile=3,recovery/*恢复到第二次差异备份后的状态*/

usestu9

sp_help

此时会看到表abc，abc2。

并且我们不用先恢复到第一次差异备份之后再来作第二次差异备份的恢复工作，而是可以直接从第二次开始恢复，也就是说可以直接恢复到任意一个作过差异备份的时间点，这是与事务日志备份的不同之处，但前提是，它同样也得先有一个全库备份的基础。

三、事务日志备份：

1、创建事务日志备份的语法：

BACKUPLOG数据库名TO备份文件名

2、恢复事务日志备份的语法：

RESTORELOG数据库名FROM备份文件名[WITHNORECOVERY|RECOVERY]

例：

sp_addumpdevice'disk',stu369back,'d:

\sql\stu369back.bak'

createdatabasestu369

alterdatabasestu369setrecoveryfull--因为在精简版当中默认的是simple

backupdatabasestu369tostu369back--先作全库备份--1

usestu369

createtableabc（aint）

backuplogstu369todisk='d:

\sql\stu369back.bak'--2

usestu369

createtableabc2（aint）

backuplogstu369todisk='d:

\sql\stu369back.bak'--3

usestu369

createtableabc3（aint）

backuplogstu369todisk='d:

\sql\stu369back.bak'--4

usestu369

createtableabc4（aint）

backuplogstu369todisk='d:

\sql\stu369back.bak'--5

usestu369

createtableabc5（aint）--此时出现故障

usemaster

backuplogstu369todisk='d:

\sql\stu369back.bak'withnorecovery--尾部日志备份

restoredatabasestu369fromstu369backwithnorecovery--先恢复全库备份

restorelogstu369fromstu369backwithfile=2,norecovery

restorelogstu369fromstu369backwithfile=3,norecovery

restorelogstu369fromstu369backwithfile=4,norecovery

restorelogstu369fromstu369backwithfile=5,recovery

usestu369

go

sp_help

此时看到是恢复到了abc,abc2,abc3,abc4

如果想还原到第五个（或是第四个、第三个）时，必须从第二个（第一个file=1可以省略，因为它就是全库备份，写上也不错，只不过是又执行了一次全库备份）开始，然后第三个，第四个，最后才是第五个，这是由事务日志备份的定义来决定的，必须要一个一个还原，一直到想还原的最后一个。

并且在SQL2005当中，大多数情况下，在恢复当前数据库数据时，要先作尾部日志备份操作，否则会导致错误。

尾部日志备份是从一个可能损坏的数据库中获得的尚未备份的日志备份，它是在故障发生之后进行的。

如果作备份与恢复时，备份用的是完整模式，则在还原之前一定要作一个尾部日志备份。

而要是作日志备份时，必须是完整模式，所以一定要作尾部日志备份，因为当恢复模式为简单（SIMPLE）模式时，不允许使用BACKUPLOG语句。

而当作全库、差异备份时，可以是完整模式，也可以是简单模式，如果用的是简单模式（也就是系统的默认模式），所以不用在恢复之前作尾部日志备份。

数据的导入与导出：

很多时候，需要在SQLServer数据库与其它类型的数据库或电子表格之间进行数据的交换，这样可以实现不同数据库类型之间的数据资源的共享，减少因为数据库类型的变化而造成的大量的数据重复录入等不必要的操作。

另外，在以前使用了其它的数据库管理系统（如：

access等），现在要更换SQLServer数据库系统时，也可以利用它来将原来的数据转储到SQLServer中。

同义词：

同义词是架构范围内的对象的另一名称，在引用基对象时，可以简化长名称。

一般它可以应用在DML语句中。

createtableabcdefg（aint）

createsynonymk1

for

abcdefg

createsynonymk2

for

jj.dbo.abcdefg--库名.所有者名.表名

select*fromabcdefg

select*fromk1

select*fromk2