CREATE DATABASE using file in default path

Question

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?

score 78 · Answer 1 · edited Oct 06 '16 at 01:05

78

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.

edited Oct 06 '16 at 01:05

JumpingJezza

5,498
11
67
106

answered May 17 '13 at 01:06

Gayan Dasanayake

1,933
2
17
22

Also you can use `NEWNAME` if you want to amend the logical name; i.e. `NAME` references the original name, `NEWNAME` says what to change it to. https://www.mssqltips.com/sqlservertip/4407/rename-logical-database-file-name-for-a-sql-server-database/ – JohnLBevan Oct 07 '19 at 16:36

Damir Sudarevic · Accepted Answer · 2019-07-29T15:34:29.063

25

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

CREATE DATABASE Documents;

edited Jul 29 '19 at 15:34

answered Oct 28 '09 at 14:48

Damir Sudarevic

21,891
3
47
71

7

True, but what if I need to specify additional attributes like SIZE and FILEGROWTH? I've seen the solutions using XP's to read the registry but what if you aren't running on the default instance? That registry key/value wouldn't give the right answer. Still searching so I don't really consider this answered myself. – Christopher Painter Jul 29 '10 at 13:25
6

after create database add alter database script for specify additional attributes like SIZE and FILEGROWTH. – Rikin Patel Oct 24 '12 at 03:15

score 4 · Answer 3 · edited May 23 '17 at 11:46

4

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.

edited May 23 '17 at 11:46

Community

1
1

answered Nov 30 '16 at 16:53

Blade

582
4
10

Thanks. However how do I use this in the CREATE DATABASE script? Executing CREATE DATABASE [Documents] ( NAME = N'Documents', FILENAME = @InstanceDefaultDataPath + N'Documents.mdf' ... gives an error - you can't concatenate the result of your query with the DB dame inside a CREATE DATABASE script – Reversed Engineer Feb 07 '18 at 09:35
@ReversedEngineer I think you would need to build the CREATE DATABASE statement and execute it dynamically. – UberDoodles Jan 21 '20 at 13:58

score 3 · Answer 4 · answered May 22 '20 at 20:51

Adding onto @Blade's answer. Here's an example of getting the default path server properties and using the EXECUTE method:

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 USE to 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.

score 1 · Answer 5 · edited Jan 26 '12 at 09:47

1

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

Cheers,

edited Jan 26 '12 at 09:47

answered Oct 28 '09 at 14:47

Goows

809
2
7
10

proost!, prost!, santé, salute, salud!, živjeli!, kippis! – Roel Sep 07 '17 at 13:26

score 1 · Answer 6 · answered Oct 28 '09 at 14:49

1

I believe that you can do

CREATE DATABASE [Documents]

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

answered Oct 28 '09 at 14:49

Nestor

13,706
11
78
119

CREATE DATABASE using file in default path

6 Answers6

Linked