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 — Castrohenge
– Castrohenge, Commented Apr 15, 2014 at 10:05

Gayan Dasanayake · Accepted Answer · 2013-05-17 01:19:55Z

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, 2013 at 1:19

answered May 17, 2013 at 1:09

Gayan Dasanayake

2,0212 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rikin Patel · Accepted Answer · 2012-10-24 03:10:10Z

2

try this

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

CREATE DATABASE DatabaseName;

answered Oct 24, 2012 at 3:10

Rikin Patel

9,4738 gold badges74 silver badges80 bronze badges

Comments

Wookai · Accepted Answer · 2011-03-31 11:24:48Z

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, 2011 at 11:24

Wookai

21.9k16 gold badges76 silver badges86 bronze badges

5 Comments

Wookai Over a year ago

Just include @@datadir in your FILENAME parameter.

Eliazar Over a year ago

: 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

Rikin Patel Over a year ago

i am getting error like Must declare the scalar variable "@@datadir"

Tilo Over a year ago

Think this is MySQL not MS SQL

thezapper Over a year ago

For MS-SQL 2012 and greater you can user: SELECT CONVERT(sysname, SERVERPROPERTY('InstanceDefaultDataPath')); for getting the default path

Eliazar · Accepted Answer · 2011-03-31 13:27:02Z

1

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!

answered Mar 31, 2011 at 13:27

Eliazar

911 silver badge4 bronze badges

Comments

überjesus · Accepted Answer · 2011-03-31 12:43:21Z

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.

Collectives™ on Stack Overflow

CREATE DATABASE in default path

5 Answers 5

Comments

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related