SQL 使用默认路径中的文件创建数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1637628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATE DATABASE using file in default path
提问by recursive
I want to create an SQL script that creates a database. Right now, I have this:
我想创建一个创建数据库的 SQL 脚本。现在，我有这个：
CREATE DATABASE [Documents] ON PRIMARY
( NAME = N'Documents', FILENAME = N'Documents.mdf')
LOG ON
( NAME = N'Documents_log', FILENAME = N'Documents_log.ldf')
COLLATE SQL_Latin1_General_CP1_CI_AS
However, this generates the following error:
但是，这会产生以下错误：
Msg 5105, Level 16, State 2, Line 2
A file activation error occurred. The physical file name 'Documents.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
I know the problem is that I did not specify fully qualified path for the filenames. But I want to be able to run this script regardless of the directory structure of the database server. Is there some way to use a default path?
我知道问题是我没有为文件名指定完全限定的路径。但我希望无论数据库服务器的目录结构如何，都能够运行此脚本。有没有办法使用默认路径？
采纳答案by Damir Sudarevic
You can create a database without specifying file details, like:
您可以在不指定文件详细信息的情况下创建数据库，例如：
CREATE DATABASE Documents;
回答by Gayan Dasanayake
Create the database 'Documents' and give file properties through an alter.
创建数据库“文档”并通过更改提供文件属性。
USE [master]
GO
CREATE DATABASE [Documents]
GO
ALTER DATABASE [Documents] MODIFY FILE
( NAME = N'Documents', SIZE = 512MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO
ALTER DATABASE [Documents] MODIFY FILE
( NAME = N'Documents_log', SIZE = 256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
GO
This script is more portable and can be deployed in multiple servers without any modifications.
此脚本更便携，无需任何修改即可部署在多个服务器中。
回答by Blade
See How do I find the data directory for a SQL Server instance?
If you are using SQL Server 2012 or higher, you can find the default path using
如果您使用的是 SQL Server 2012 或更高版本，则可以使用以下命令找到默认路径
select
InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')
You can then use exec() to construct your CREATE DATABASE statement.
然后您可以使用 exec() 来构建您的 CREATE DATABASE 语句。
This is useful if you want the physical file names of your database to be different from the default name.
如果您希望数据库的物理文件名与默认名称不同，这将非常有用。
回答by Nestor
I believe that you can do
我相信你能做到
CREATE DATABASE [Documents]
without the ON .... and it will get created with defaults for path and the rest.
没有 ON .... 它将使用路径和其余的默认值创建。
回答by Goows
Take a Look on how to create a Default Path. See if it helps on what you are looking for.
看看如何创建默认路径。看看它是否对你正在寻找的东西有帮助。
Cheers,
干杯，
回答by Dustin Metzgar
Adding onto @Blade's answer. Here's an example of getting the default path server properties and using the EXECUTE method:
添加到@Blade 的答案中。下面是获取默认路径服务器属性并使用 EXECUTE 方法的示例：
DECLARE @DefaultDataPath varchar(max)
SET @DefaultDataPath = (SELECT CONVERT(varchar(max), SERVERPROPERTY('INSTANCEDEFAULTDATAPATH')))
DECLARE @DefaultLogPath varchar(max)
SET @DefaultLogPath = (SELECT CONVERT(varchar(max), SERVERPROPERTY('INSTANCEDEFAULTLOGPATH')))
EXECUTE('
CREATE DATABASE [blah] ON PRIMARY
( NAME = N''blah'', FILENAME = ''' + @DefaultDataPath + 'blah.mdf'', SIZE = 167872KB, MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N''blah_Log'', FILENAME = ''' + @DefaultDataPath + 'blah_Log.mdf'', SIZE = 2048KB, MAXSIZE = 2048GB, FILEGROWTH = 16384KB );
COLLATE SQL_Latin1_General_CP1_CI_AS;
');
GO
Note that if you do a USEto switch dbs, the local variable scope is lost. So if you're creating multiple dbs in a script, either create them all at the beginning or copy the variables' declare/set to each create.
请注意，如果您执行 aUSE切换 dbs，则局部变量范围将丢失。因此，如果您在脚本中创建多个数据库，请在开始时全部创建它们或将变量的声明/设置复制到每个创建。
