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
This gives:
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!