How to Specify Data and Log File Paths When Creating a SQL Server Database Using Parameters?

Using Parameters to Specify Data and Log File Paths During Database Creation

When creating a database in SQL Server, you may want to specify the path where the data and log files should be located. This can be especially useful for automating database creation or deploying to different environments.

To accomplish this, you can utilize parameters in the CREATE DATABASE statement. However, the code you provided:

DECLARE @DataFilePath AS NVARCHAR(MAX)

SET @DataFilePath = N'C:\ProgramData\Gemcom\'

DECLARE @LogFilePath AS NVARCHAR(MAX)

SET @DataFilePath = N'C:\ProgramData\Gemcom\'


USE master
Go
CREATE DATABASE TestDB
ON 
PRIMARY 
( NAME = N'TestDB_Data', FILENAME = @DataFilePath )
LOG ON 
( NAME = N'TestDB_Log', FILENAME = @LogFilePath  )

GO

will not work as-is. This is because you have set the value for @DataFilePath twice, which overrides the intended value.

Instead, you can utilize dynamic SQL to construct the CREATE DATABASE statement at runtime. Here's how you can do it:

DECLARE @sql AS NVARCHAR(MAX)

SELECT @sql = 'CREATE DATABASE TestDB ON PRIMARY ( NAME = ''TestDB_Data'', 
 FILENAME = ' + quotename(@DataFilePath) + ') LOG ON ( NAME = ''TestDB_Log'', 
FILENAME = ' + quotename(@LogFilePath) + ')'

EXEC (@sql)

This approach ensures that the parameters are correctly referenced in the dynamic SQL statement, allowing you to specify the desired file paths for your database.

The above is the detailed content of How to Specify Data and Log File Paths When Creating a SQL Server Database Using Parameters?. For more information, please follow other related articles on the PHP Chinese website!