使用默认路径下的文件创建数据库[英] CREATE DATABASE using file in default path

本文是小编为大家收集整理的关于使用默认路径下的文件创建数据库的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想创建一个创建数据库的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

但是,这会生成以下错误:

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.

我知道问题是我没有为文件名指定完全合格的路径.但是,无论数据库服务器的目录结构如何,我都希望能够运行此脚本.有什么方法可以使用默认路径?

推荐答案

您可以创建一个数据库而无需指定文件详细信息,例如:

CREATE DATABASE Documents;

其他推荐答案

创建数据库'文档'并通过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

此脚本更便宜,可以部署在多个服务器中,而无需任何修改.

其他推荐答案

请参阅如何如何我是否找到SQL Server实例的数据目录?

如果您使用的是SQL Server 2012或更高版本,则可以使用

找到默认路径
select 
  InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
  InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')

您可以使用exec()构建创建数据库语句.

如果您希望数据库的物理文件名与默认名称不同,这将很有用.

本文地址:https://www.itbaoku.cn/post/597705.html

问题描述

I want to create an SQL script that creates a database. Right now, I have this:

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?

推荐答案

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

CREATE DATABASE Documents;

其他推荐答案

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.

其他推荐答案

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

select 
  InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
  InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')

You can then use exec() to construct your CREATE DATABASE statement.

This is useful if you want the physical file names of your database to be different from the default name.