I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into some difficulties trying to use a variable for the file paths.
Declare @logpath nvarchar(256), @datapath nvarchar(256), @filenameLog nvarchar(256), @filenamedata nvarchar(256); SET @logpath = (select LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1) from sys.master_files where name = 'modeldev') SET @datapath = (select LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1) from sys.master_files where name = 'modellog') set @filenamelog = @logpath + 'test.ldf' set @filenamedata = @datapath + 'test.mdf' CREATE DATABASE [test] CONTAINMENT = NONE ON PRIMARY ( NAME = test, FILENAME = @filenamedata , SIZE = 51200KB , FILEGROWTH = 10240KB ) LOG ON ( NAME = test_log, FILENAME = @filenamelog , SIZE = 5120KB , FILEGROWTH = 5120KB ) GO
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@filenamedata'.
What am I missing? I feel like it's something obvious staring right at me but I can't seem to find it.
My source for this script was an msdn article here!