Revisions to LocalDB v14 creates wrong path for mdf files - Database Administrators Stack Exchange

deleted 1 character in body

Source Link

edited Mar 8, 2018 at 5:58

151
1
4

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = ''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), @dataFilePath)
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = ''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), @dataFilePath)
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = ''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), @dataFilePath
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

Edited code based on Solomons comment

Source Link

edited Mar 8, 2018 at 5:49

Nikolaj Dam Larsen

151
1
4

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = %s''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), quotename(@dataFilePath)
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = %s )', 
    quotename(@databaseName), @databaseName, quotename(@dataFilePath)
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = ''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), @dataFilePath)
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

added 11 characters in body

Source Link

edited Mar 7, 2018 at 22:09

Nikolaj Dam Larsen

151
1
4

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = 'CREATEFORMATMESSAGE(
 DATABASE ' + quotename(@databaseName)'CREATE +DATABASE '
%s ON PRIMARY ( NAME = '%s, +FILENAME @databaseName= +%s )', FILENAME 
 = ' + quotename(@dataFilePath@databaseName) +, '@databaseName, quotename(@dataFilePath)'
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = 'CREATE DATABASE ' + quotename(@databaseName) + '
ON PRIMARY ( NAME = ' + @databaseName + ', FILENAME = ' + quotename(@dataFilePath) + ' )'

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = %s )',  
    quotename(@databaseName), @databaseName, quotename(@dataFilePath)
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

Source Link

created Mar 7, 2018 at 21:59

Nikolaj Dam Larsen

151
1
4

Loading

Stack Exchange Network

Return to Answer