Create database with t-SQL without specifying its file name

Question

I try to run the following SQL statement to create a database:

string strSQL = "CREATE DATABASE " + strDatabaseName +
    " ON PRIMARY " +
    "(" +
    "SIZE = 10MB, FILEGROWTH = 20%) " +
    "LOG ON (" +
    "SIZE = 5MB, " +
    "FILEGROWTH = 20%)" +
    " COLLATE SQL_Latin1_General_CP1_CI_AS;";

I want to use a default .mdf and .ldf file locations, but specify the size and file-growth parameters. The issue is that when I run it I get an error:

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

So is there's any way to do what I'm trying to achieve?

sahalMoidu · Accepted Answer · 2013-10-23 01:00:19Z

4

Try This.

      string strSql = " DECLARE @data_path nvarchar(256); "+
"SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)"+
 "                 FROM master.sys.master_files"+
  "                WHERE database_id = 1 AND file_id = 1);"+


"EXECUTE ('CREATE DATABASE " + strDataBaseName +
"ON PRIMARY "+
   "("+
   "  NAME = FileStreamDB_data "+
   " ,FILENAME = ''' + @data_path + '" + strDataBaseName +"_data.mdf''"+
   " ,SIZE = 10MB"+
   " ,MAXSIZE = 50MB"+
   " ,FILEGROWTH = 15%"+
   " )LOG ON ("+
   " NAME = FileStreamDB_log"+
   " ,FILENAME = ''' + @data_path + '" + strDataBaseName + "_log.ldf''" +
   " ,SIZE = 5MB, "+
   " FILEGROWTH = 20%)"+
"     COLLATE SQL_Latin1_General_CP1_CI_AS')";

answered Oct 23, 2013 at 1:00

sahalMoidu

1,1629 silver badges15 bronze badges

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

3 Comments

ahmd0 Over a year ago

Thanks. That worked nicely. I see that you took the path to the 1st available database and used it to construct my new DB path. I'm curious, is DB with id=1 always master.mdf? And will this work on a remote SQL Server?

sahalMoidu Over a year ago

Yes.It should be, since there wont be a sql-server instance without a master db and the sql server creates the master database first so the db should have id =1

Ben Challenor Over a year ago

Note you can get the actual default paths from the server properties SERVERPROPERTY('instancedefaultdatapath') and SERVERPROPERTY('instancedefaultlogpath'). This is what SSMS is doing under the hood - check out SQL Server Profiler when you open the New Database dialog.

ahmd0 · Accepted Answer · 2013-10-24 00:28:20Z

-1

Based on @sahalMoidu's suggestions, here's a slightly updated version of his SQL:

DECLARE @dbFileName nvarchar(256);
CREATE DATABASE [testdb2] COLLATE SQL_Latin1_General_CP1_CI_AS;
SET @dbFileName = (SELECT [name] FROM master.sys.master_files WHERE [database_id] = DB_ID(N'testdb2') AND file_id = 1);
EXECUTE ('ALTER DATABASE [testdb2]
 MODIFY FILE(
NAME = ''' + @dbFileName + ''',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 20%
);

answered Oct 24, 2013 at 0:28

ahmd0

17.5k36 gold badges146 silver badges236 bronze badges

Collectives™ on Stack Overflow

Create database with t-SQL without specifying its file name

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related