使用默认路径中的文件创建数据库

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?

我知道问题是我没有为文件名指定完全限定的路径。但是我希望能够运行这个脚本，而不考虑数据库服务器的目录结构。有什么方法可以使用默认路径吗?

5 个解决方案

#1

You can create a database without specifying file details, like:

您可以在不指定文件细节的情况下创建数据库，例如:

CREATE DATABASE Documents;

创建数据库文件;

#2

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.

该脚本更具有可移植性，可以在不做任何修改的情况下部署在多个服务器上。

#3

See How do I find the data directory for a SQL Server instance?

查看如何查找SQL服务器实例的数据目录?

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.

如果您希望数据库的物理文件名与默认名称不同，这将非常有用。

#4

I believe that you can do

我相信你能做到

CREATE DATABASE [Documents]

without the ON .... and it will get created with defaults for path and the rest.

没有在....它会被创建为path和其他的默认值。

#5

Take a Look on how to create a Default Path. See if it helps on what you are looking for.

看看如何创建默认路径。看看它是否有助于你寻找的东西。

Cheers,

欢呼,

#1