【IT168 服务器学院】
--数据库创建示例1
--首先必须在C盘下建立[DataBase]文件夹
USE Master
GO
--检查是否存在测试数据库，若存在，则删除之
IF EXISTS (SELECT NAME FROM SYSDATABASES WHERE NAME = ''MyDB'')
DROP DATABASE MyDB
GO
CREATE DATABASE MyDB
ON
--数据文件
(
NAME = MyDB_dat,
FILENAME = ''C:\DataBase\MyDB_dat.mdf'',
SIZE = 10,--默认值为 MB
MAXSIZE = 50,
FILEGROWTH = 5
)
--日志文件
LOG ON
(
NAME = MyDB_log,
FILENAME = ''C:\DataBase\MyDB_log.ldf'',
SIZE = 1MB,
MAXSIZE = 25MB,
FILEGROWTH = 1MB
)
GO
--数据库创建示例2
USE Master
GO
IF EXISTS (SELECT NAME FROM SYSDATABASES WHERE NAME = ''MyDB'')
DROP DATABASE MyDB
GO
USE Master
GO
CREATE DATABASE MyDB
ON
--主数据文件
(
NAME = MyDB_dat1,
FILENAME = ''C:\DataBase\MyDB_dat1.mdf'',
SIZE = 10,--默认值为 MB
MAXSIZE = 50,
FILEGROWTH = 5
),
--次要数据文件
(
NAME = MyDB_dat2,
FILENAME = ''C:\DataBase\MyDB_dat2.ndf'',
SIZE = 10,--默认值为 MB
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
--主日志
(
NAME = MyDB_log1,
FILENAME = ''C:\DataBase\MyDB_log1.ldf'',
SIZE = 1MB,
MAXSIZE = 25MB,
FILEGROWTH = 1MB
),
--次要日志
(
NAME = MyDB_log2,
FILENAME = ''C:\DataBase\MyDB_log2.ldf'',
SIZE = 1MB,
MAXSIZE = 25MB,
FILEGROWTH = 1MB
)
GO
--删除数据库(请谨慎使用！)
DROP DATABASE MyDB
GO
--分离数据库
EXEC sp_detach_db ''MyDB''
GO
--附加数据库
CREATE DATABASE MyDB
ON PRIMARY
(
FILENAME = ''C:\DataBase\MyDB_dat1.mdf''
)
FOR ATTACH
GO
--修改数据库
--增加数据库数据文件
ALTER DATABASE MyDB
ADD FILE
(
NAME = MyDB_dat3,
FILENAME = ''C:\DataBase\MyDB_dat3.ndf'',
SIZE = 10,--默认值为 MB
MAXSIZE = 50,
FILEGROWTH = 5
)
GO
--清除数据库数据文件
ALTER DATABASE MyDB
REMOVE FILE MyDB_dat3
GO
--修改数据库配置参数
ALTER DATABASE MyDB
MODIFY FILE
(
NAME = ''MyDB_dat1'',
SIZE = 20MB
)
GO
--查询数据库信息
USE Master
GO
SELECT * FROM SYSDATABASES
--备份数据库
BACKUP DATABASE MyDB
TO DISK = ''C:\DataBase\MyDB_BackUp.BAK''
GO
--利用备份还原数据库
IF EXISTS (SELECT NAME FROM SYSDATABASES WHERE NAME = ''MyDB'')
DROP DATABASE MyDB
GO
RESTORE FILELISTONLY
FROM DISK = ''C:\DataBase\MyDB_BackUp.BAK''
RESTORE DATABASE MyDB
FROM DISK = ''C:\DataBase\MyDB_BackUp.BAK''
WITH
MOVE ''MyDB_dat'' TO ''C:\DataBase\MyDB_dat.mdf'',
MOVE ''MyDB_log'' TO ''C:\DataBase\MyDB_log.ldf''
GO
