Product(s):	Promis.e, Bentley Substation
	Version(s):	through 08.11.13.140
	Environment:	N/A
	Area:	Database Platform Support
	Subarea:	N/A

Background

When creating SQL Server databases using a script, the SQL data folder path included in paths to the database file and the transaction log in the script may not reflect the actual directory structure of the machine.  In this case the paths need to be corrected, since the CREATE DATABASE instruction will not create folders that do not exist and will instead fail.

Below is a script useful for determining the data folder path used by the instance of SQL Server to which SQL Server Management Studio is currently connected. The database and transaction log can be created in this path. 

Script

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory =
SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles
WHERE dbid = 1 AND fileid = 1
print @device_directory

Steps to Accomplish

1. In SQL Server Management Studio, click the New Query button.
2. Copy the above script into the new query pane.
3. Right click the query pane and select Execute, or click the Execute button on the toolbar.

The path that results in the Messages pane can then be used as needed.  For example: highlight the path and press CTRL+C or right-click > Copy to copy to the clipboard, then highlight the similar but incorrect portion of the database path in the database creation script, and press CTRL+V to paste and overwrite the incorrect path.  Repeat for the incorrect portion of the transaction log path.

See Also

[[3594|Creating and Updating the Project Database]]

External Links

Retrieving the DATA folder installation path from MS SQL Server 

Original Author:

Matt_P