第2讲 建库、附加与分离2012.09.1412Outline 1.1 创建一个新数据库及存储该数据库的文件， 1.2 从已创建的数据库已分离文件中附加数据库。31. 建库命令CREATE DATABASE (Transact-SQL) 1.1 创建一个新数据库及存储该数据库的文件CREATE DATABASE Branch ON PRIMARY ( NAME = Branch, FILENAME = f:Web信息系统课程设计范例databasebranch.mdf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON (NAME = branchlog1, FILENAME = f:Web信息系统课程设计范例 databasebranchlog.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20)A. 创建指定数据文件和事务日志文件的数据库 41. 建库命令CREATE DATABASE (Transact-SQL) 1.1 创建一个新数据库及存储该数据库的文件USE master GO CREATE DATABASE Archive ON PRIMARY ( NAME = Arch1, FILENAME = c:program filesmicrosoft sql servermssqldataarchdat1.mdf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch2, FILENAME = c:program filesmicrosoft sql servermssqldataarchdat2.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch3, FILENAME = c:program filesmicrosoft sql servermssqldataarchdat3.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = Archlog1, FILENAME = c:program filesmicrosoft sql servermssqldataarchlog1.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Archlog2, FILENAME = c:program filesmicrosoft sql servermssqldataarchlog2.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) GO B. 指定多个数据文件和多个日志文件创建数据库 51. 建库命令CREATE DATABASE (Transact-SQL) 1.1 创建一个新数据库及存储该数据库的文件USE master GO CREATE DATABASE Products ON ( NAME = prods_dat, FILENAME = c:program filesmicrosoft sql servermssqldataprods.mdf , SIZE = 4, MAXSIZE = 10, FILEGROWTH = 1 ) GO C. 创建简单的数据库 61. 建库命令CREATE DATABASE (Transact-SQL) 1.1 创建一个新数据库及存储该数据库的文件CREATE DATABASE mytest D. 不指定文件创建数据库 71. 建库命令CREATE DATABASE (Transact-SQL) 1.1 创建一个新数据库及存储该数据库的文件USE master GO CREATE DATABASE Products2 ON ( NAME = prods2_dat, FILENAME = c:program filesmicrosoft sql servermssqldataprods2.mdf ) GO E. 不指定SIZE创建数据库 81. 建库命令CREATE DATABASE (Transact-SQL) 1.1 创建一个新数据库及存储该数据库的文件USE master GO CREATE DATABASE Sales1 ON PRIMARY ( NAME = SPri1_dat,FILENAME = D:SalesData1SPri1dat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = SPri2_dat,FILENAME = D:SalesData1SPri2dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), FILEGROUP SalesGroup1 ( NAME = SGrp1Fi1_dat, FILENAME = D:SalesData1SG1Fi1dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp1Fi2_dat,FILENAME = D:SalesData1SG1Fi2dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), FILEGROUP SalesGroup2 ( NAME = SGrp2Fi1_dat,FILENAME = D:SalesData1SG2Fi1dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp2Fi2_dat,FILENAME = D:SalesData1SG2Fi2dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log,FILENAME = D:SalesData1salelog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GOF. 使用文件组创建数据库 92. 附加与分离在某些情况下，Analysis Services 数据库管理员 (dba) 经常需要使数据库脱机一段时间，然后在同一服务器实例或其 他服务器实例上使数据库恢复联机。根据业务需要（例如 ，将数据库移到另一个磁盘以获得更好的性能、为数据库 扩容获取空间或升级产品），经常需要进行上述操作。对 于上述所有情况，可以采用程序也可以采用手工操作完成 。102. 附加与分离分离之前知道 什么信息？112. 附加与分离*.MDF和*.LDF122. 附加与分离 1.2 从已创建的数据库已分离数据库USE master; GO sp_detach_db Archive; GO132. 附加与分离 1.2 从已创建的数据库已分离文件中附加数据库USE master; GOCREATE DATABASE ArchiveON (FILENAME = D:SalesDataarchdat1.mdf ）FOR ATTACH ; GO142. 附加与分离152. 附加与分离162. 附加与分离172. 附加与分离182. 附加与分离192. 附加与分离202. 附加与分离212. 附加与分离222. 附加与分离232. 附加与分离242. 附加与分离252. 附加与分离Thank you very much!26