具有create database、create any database、alter any database权限的用户才可以创建数据库。创建数据库Create database xxiang2007on primary(name = xxiang2007,filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007.mdf',size=3,maxsize=10,filegrowth=1),filegroup sg(name = xxiang2007_1,filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007_1.ndf',size=3,maxsize=10,filegrowth=1)log on (name = xxiang2007_log,filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007_log.ldf',size=3,maxsize=10,filegrowth=1)go
设置数据库选项alter database xxiang2007set recovery fullgo
查看数据库信息sp_helpdb xxiang2007
更改数据库alter database xxiang2007 modify name = xxiang2008go
增大数据库一、设置数据库为自动增长方式
二、直接修改数据库文件或日志文件的大小alter database xxiang2008modify file(name =xxiang2007_1,size=11)go
三、在数据库中增加新的次要的数据文件或日志文件alter database xxiang2008add file(name = xxiang2007_2,filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007_2.ndf',size=3,maxsize=10,filegrowth=1)go
收缩数据库一、使用auto_shrink数据库选项来设置自动收缩数据库alter database xxiang2008set auto_shrink ongo
二、使用dbcc shrinkdatabase命令来收缩数据库dbcc shrinkdatabase ('xxiang2008',20)go
三、使用dbcc shrinkfile命令来收缩数据库dbcc shrinkfile ('xxiang2007_2',20)dbcc shrinkfile ('xxiang2007_2',emptyfile)把这个文件清空
修改数据库文件alter database xxiang2008remove file xxiang2007_2go管理文件夹alter database xxiang2008add filegroup xxiang2008goalter database xxiang2008modify filegroup xxiang2008 defalut 设默认文件夹modify filegroup xxiang2008 name xxiang2007goalter database xxiang2008modify filegroup xxiang2007 read_only/readwritegoalter database xxiang2008remove filegroup xxiang2007go
创建数据库快照create database xxiang2007_shapshoton(name=xxiang2007_shapshot,filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007_shapshot.snp')as snapshot of xxiang2007godrop database xxiang2007_shapshotgo
分离数据库sp_detach_db xxiang2007附加数据库create database xxiang2007on(filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007.mdf'),(filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/xxiang2007_log.ldf'),for attach
删除数据库drop database xxiang2007
