CREATE DATABASE using file in default path

recursive_44743 0
Viewed 32626

I want to create an SQL script that creates a database. Right now, I have this:

CREATE DATABASE [Documents] ON  PRIMARY 
( NAME = N'Documents', FILENAME = N'Documents.mdf')
 LOG ON 
( NAME = N'Documents_log', FILENAME = N'Documents_log.ldf')
 COLLATE SQL_Latin1_General_CP1_CI_AS

However, this generates the following error:

Msg 5105, Level 16, State 2, Line 2
A file activation error occurred. The physical file name 'Documents.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I know the problem is that I did not specify fully qualified path for the filenames. But I want to be able to run this script regardless of the directory structure of the database server. Is there some way to use a default path?

sql-server sql database
6 Answers

Adding onto @Blade's answer. Here's an example of getting the default path server properties and using the EXECUTE method:

DECLARE @DefaultDataPath varchar(max)
SET @DefaultDataPath = (SELECT CONVERT(varchar(max), SERVERPROPERTY('INSTANCEDEFAULTDATAPATH')))
DECLARE @DefaultLogPath varchar(max)
SET @DefaultLogPath = (SELECT CONVERT(varchar(max), SERVERPROPERTY('INSTANCEDEFAULTLOGPATH')))

EXECUTE('
CREATE DATABASE [blah] ON PRIMARY
( NAME = N''blah'', FILENAME = ''' + @DefaultDataPath + 'blah.mdf'', SIZE = 167872KB, MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG ON
( NAME = N''blah_Log'', FILENAME = ''' + @DefaultDataPath + 'blah_Log.mdf'', SIZE = 2048KB, MAXSIZE = 2048GB, FILEGROWTH = 16384KB );
COLLATE SQL_Latin1_General_CP1_CI_AS;
');
GO

Note that if you do a USE to switch dbs, the local variable scope is lost. So if you're creating multiple dbs in a script, either create them all at the beginning or copy the variables' declare/set to each create.

user_17303160
Related