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

possible duplicate of [CREATE DATABASE using file in default path](http://stackoverflow.com/questions/1637628/create-database-using-file-in-default-path) — Castrohenge, Apr 15 '14 at 10:05

Gayan Dasanayake · Answer 1 · 2013-05-17T01:19:55.500

6

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

edited May 17 '13 at 01:19

answered May 17 '13 at 01:09

Gayan Dasanayake

1,933
2
17
22

score 1 · Answer 2 · answered Oct 24 '12 at 03:10

1

try this

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

CREATE DATABASE DatabaseName;

answered Oct 24 '12 at 03:10

Rikin Patel

8,848
7
70
78

score 1 · Answer 3 · answered Mar 31 '11 at 11:24

1

You can use the following variables, that respectively contain the root directory of your install and the data directory :

SELECT @@basedir, @@datadir;

answered Mar 31 '11 at 11:24

Wookai

20,883
16
73
86

1

Just include `@@datadir` in your FILENAME parameter. – Wookai Mar 31 '11 at 11:53
1

: CREATE DATABASE [asst] : ON (NAME = 'asst_dat', FILENAME = @@datadir, SIZE = 62, FILEGROWTH = 10%) : LOG ON (NAME = 'asst_log', FILENAME = @@datadir, SIZE = 146, FILEGROWTH = 10%) This didn't work, wrong syntax – Eliazar Mar 31 '11 at 12:02
i am getting error like Must declare the scalar variable "@@datadir" – Rikin Patel Oct 24 '12 at 03:09
Think this is MySQL not MS SQL – Tilo Feb 16 '15 at 19:01
1

For MS-SQL 2012 and greater you can user: SELECT CONVERT(sysname, SERVERPROPERTY('InstanceDefaultDataPath')); for getting the default path – thezapper Nov 12 '18 at 20:16

score 1 · Answer 4 · answered Mar 31 '11 at 13:27

Thanks, überjesus, I've simplified your code a little bit

DECLARE @rows varchar(MAX),
        @script nvarchar(MAX);
SET @rows = (SELECT physical_name AS current_file_location 
FROM sys.master_files 
where name = 'master');
SET @rows = Replace(@rows, 'master.mdf', '')
SELECT @rows;
set @script = 'CREATE DATABASE [assist1]
ON (NAME = ''asst_dat'', FILENAME = ''' + @rows  + 'assist1.mdf'' , SIZE = 62, FILEGROWTH = 10%)    
LOG ON (NAME = ''asst_log'', FILENAME = ''' + @rows + 'assist1_log.ldf'' , SIZE = 146, FILEGROWTH = 10%);'
exec(@script);

Thanks for a great idea!

score 0 · Answer 5 · answered Mar 31 '11 at 12:43

Assuming you are using SQL Server 2005 or 2008 this should do it. Although it's probably a bit longer than you expected :-) You could put the main part of the code into a function and call that every time you need to create a database.

declare @instance_name nvarchar(200), 
        @system_instance_name nvarchar(200), 
        @registry_key nvarchar(512), 
        @path_data nvarchar(260), 
        @path_log nvarchar(260), 
        @value_name nvarchar(20),
        @script nvarchar(4000);

set @instance_name = coalesce(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
set @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

/* determine default location for data files */
exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @path_data output;
if @path_data is null
begin
    /* this is only executed if we are using the default instance */
    set @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path_data output;
    set @path_data = @path_data + '\Data';
end;

/* determine default location for log files */
exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @path_log output;
if @path_log is null
begin
    /* this is only executed if we are using the default instance */
    set @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path_log output;
    set @path_log = @path_log + '\Data';
end;

set @script = 'CREATE DATABASE [asst] 
    ON (NAME = ''asst_dat'', FILENAME = ''' + @path_data  + '\yourfile.mdf'' , SIZE = 62, FILEGROWTH = 10%)
    LOG ON (NAME = ''asst_log'', FILENAME = ''' + @path_log + '\yourfile.ldf'' , SIZE = 146, FILEGROWTH = 10%);'

exec(@script);

You can not use variables in the CREATE DABASE statement. That's why you have to create a variable that holds the command and execute that as a script.

CREATE DATABASE in default path

5 Answers5