--==========================================================-- -- 作者:彭建军 -- 日期:2005-06-21 -- 页面:Page1 -- 概要:创建、修改、删除、分离、附加、备份、还原数据库 -- 说明:以下示例均在[查询分析器]下进行,可配合图形界面进行测试 --==========================================================-- --数据库创建示例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 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 01:25 , Processed in 0.243540 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.