CREATE DATABASE in default path

Question

I have simple code

: CREATE DATABASE [asst]  
: ON (NAME = 'asst_dat', FILENAME = 'C:\data' , SIZE = 62, FILEGROWTH = 10%) 
: LOG ON (NAME = 'asst_log', FILENAME = 'C:\data' , SIZE = 146, FILEGROWTH = 10%)

How can I change the FILENAME parameter to create DB in the default installation path like C:\Program Files\Microsoft SQL Server\MSSQL10_50.ATASSIST\MSSQL. So why I need it: from version to version, from instance to instance of SQL Server this locations differs

Answer 1

First create the database and then alter file properties as needed.

CREATE DATABASE [DBName]
GO

ALTER DATABASE [DBName] MODIFY FILE
( NAME = N'DBName' , SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO

ALTER DATABASE [DBName] MODIFY FILE
( NAME = N'DBName_log' , SIZE = 256MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO