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.
DECLARE @device_directory NVARCHAR(520)SELECT @device_directory =SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)FROM master.dbo.sysaltfilesWHERE dbid = 1 AND fileid = 1print @device_directory
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.
Creating and Updating the Project Database
Retrieving the DATA folder installation path from MS SQL Server