你好。我是初学者!遇到问题了!要求:创建一个名为SCHOOL的数据库,1有PRIMARY文件组和两个USER_DEFINED文件组,2第一个USER_DEFINED文件组设置为DEFAULT文件组;
这是我做的有问题:
CREATE DATABASE School
ON PRIMARY
(NAME = sch1,
FILENAME = 'E:\sh1_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),(NAME = sch2,
FILENAME = 'E:\sh2_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),DEFAULT
(NAME = sl1,
filename = 'e:\sl1_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),(NAME = sl2,
filename = 'e:\sl2_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),LOG ON
(NAME = SCHOOLLOG,
FILENAME = 'E:\SLOG.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),GO提示在DEFAULT处有错。请问这里该怎么写!!!
这是我做的有问题:
CREATE DATABASE School
ON PRIMARY
(NAME = sch1,
FILENAME = 'E:\sh1_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),(NAME = sch2,
FILENAME = 'E:\sh2_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),DEFAULT
(NAME = sl1,
filename = 'e:\sl1_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),(NAME = sl2,
filename = 'e:\sl2_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),LOG ON
(NAME = SCHOOLLOG,
FILENAME = 'E:\SLOG.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),GO提示在DEFAULT处有错。请问这里该怎么写!!!
Create Database语法CREATE DATABASE database_name
[ ON
[ PRIMARY ] [ <filespec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON { <filespec> [ ,...n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]
[;]To attach a database
CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG }
[;]<filespec> ::=
{
(
NAME = logical_file_name ,
FILENAME = 'os_file_name'
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
) [ ,...n ]
}<filegroup> ::=
{
FILEGROUP filegroup_name [ DEFAULT ]
<filespec> [ ,...n ]
}<external_access_option> ::=
{
DB_CHAINING { ON | OFF }
| TRUSTWORTHY { ON | OFF }
}
<service_broker_option> ::=
{
ENABLE_BROKER
| NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
}
GO
IF DB_ID (N'Sales') IS NOT NULL
DROP DATABASE Sales;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = '''+ @data_path + 'SPri1dat.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = '''+ @data_path + 'SPri2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = '''+ @data_path + 'SG1Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = '''+ @data_path + 'SG1Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = '''+ @data_path + 'SG2Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = '''+ @data_path + 'SG2Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''+ @data_path + 'salelog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
);
GO
ON PRIMARY
(NAME = sch1,
FILENAME = 'E:\sh1_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%), (NAME = sch2,
FILENAME = 'E:\sh2_dat.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%), FILEGROUP SalesGroup1 default
(NAME = sl1,
filename = 'e:\sl1_dat.ndf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),
FILEGROUP SalesGroup2
(NAME = sl2,
filename = 'e:\sl2_dat.ndf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%)LOG ON
(NAME = SCHOOLLOG,
FILENAME = 'E:\SLOG.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
这不是要变通的..都是固定语法.
(NAME = sl1,
filename = 'e:\sl1_dat.ndf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%),
FILEGROUP SalesGroup2
(NAME = sl2,
filename = 'e:\sl2_dat.ndf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%)要给出分组名
如果要设置哪个分组为默认的 就在该分组后面 加 default
你没有指定分组名
GO
IF DB_ID (N'Sales') IS NOT NULL
DROP DATABASE Sales;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1); -- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = '''+ @data_path + 'SPri1dat.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = '''+ @data_path + 'SPri2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = '''+ @data_path + 'SG1Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = '''+ @data_path + 'SG1Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = '''+ @data_path + 'SG2Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = '''+ @data_path + 'SG2Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''+ @data_path + 'salelog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
);
GO
-----------------------------------------------------------
小梁同志用的是 2005吧
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),FILEGROUP SalesGroup1 default
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO服务器: 消息 156,级别 15,状态 1,行 13
在关键字 'default' 附近有语法错误。以上是否正确!!!为什么在分析器里还是说有错,8楼所说,就是这样吧!!直接在要指定的组后面加default,
我是用SQL2000
CREATE DATABASE SCHOOL
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ), FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ), FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ) LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
ALTER DATABASE school MODIFY FILEGROUP SalesGroup1 DEFAULT
2005用我的可以
但是2000的数据库创建后 默认的文件组为 PRIMARY文件组 要创建后手动修改
这点和2005不一样