CODEWITHSUNDEEP
sqlsql-servert-sql
user3832856
user3832856

Reputation: 183

Creating database without filename?

Is it possible to create a database only by giving it the logical name without the filename -> which should result in creating the database in the default path with the database name.mdf?

CREATE DATABASE A
ON PRIMARY (
name = Z)

I found this from Microsoft documentation:

If data file name is not specified, SQL Server uses database_name as both the logical_file_name and as the os_file_name. The default path is obtained from the registry. The default path can be changed by using the Server Properties (Database Settings Page) in Management Studio. Changing the default path requires restarting SQL Server.

How come that when I try the query above I get an error:

File option FILENAME is required in this CREATE/ALTER DATABASE statement.

Unless I misunderstood what data file name refers to here.

Upvotes: 3

Views: 2435

Answers (2)

d.popov
d.popov

Reputation: 4255

Just use

CREATE DATABASE A

it should work.

Upvotes: 3

Dan Guzman
Dan Guzman

Reputation: 46203

This is a restriction of the SQL Server CREATE DATABASE syntax. According to the documentation, the <filespec> clause is optional. However, when specified (to include a non-default logical name), both NAME and FILENAME must be specified. The <filespec> clause syntax is:

{  
(  
    NAME = logical_file_name ,  
    FILENAME = { 'os_file_name' | 'filestream_path' }   
    [ , SIZE = size [ KB | MB | GB | TB ] ]   
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]   
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]  
)  
}

Upvotes: 10

Related Questions