CODEWITHSUNDEEP
t-sqlsql-server-2014
SilverLight
SilverLight

Reputation: 20468

How to define mdf,ldf files default location path in SQL script

I want to give the script below to my client for installing a new database.
How can i rewrite this part of script to reflect their specific data and log file locations: 

USE [master]
GO
/****** Object:  Database [PhoneBook]    Script Date: 2016/1/13 11:02:34 AM ******/
CREATE DATABASE [PhoneBook]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'PhoneBook', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\PhoneBook.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'PhoneBook_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\PhoneBook_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

My client's windows drive is not c.
Can i change these paths to reflect their sql installation path?
How can i tell this script find the right location?

Upvotes: 4

Views: 1046

Answers (2)

Fuzzy
Fuzzy

Reputation: 3810

USE [master]
GO

DECLARE @mdfPath NVARCHAR(max), @ldfPath NVARCHAR(max) , @SQL NVARCHAR(MAX), @instName NVARCHAR(max) = 'PhoneBook'


SELECT @mdfPath = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 1)+@instName+N'.mdf'
       ,@ldfPath = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 1)+@instName+N'.ldf'
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1


SELECT @SQL =
'CREATE DATABASE [PhoneBook]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'''+@instName+''', FILENAME = N'''+@mdfPath+''' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'''+@instName+'_log'', FILENAME = N'''+@ldfPath+''' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'

PRINT(@SQL)
EXECUTE(@SQL)

GO

Upvotes: 3

db_brad
db_brad

Reputation: 923

For SQL Server 2012 and above, you can use these Server properties as found here as long as their default data and log file directories are set at the server level. You could do something like this using dynamic sql:

USE [master]
GO

DECLARE @defaultDataPath NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), SERVERPROPERTY('InstanceDefaultDataPath'))
DECLARE @defaultLogPath NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), SERVERPROPERTY('InstanceDefaultLogPath'))
DECLARE @sql NVARCHAR(MAX) = 

'CREATE DATABASE [PhoneBook]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N''PhoneBook'', FILENAME = N''' + @defaultDataPath + 'PhoneBook.mdf'' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N''PhoneBook_log'', FILENAME = N''' + @defaultLogPath + 'PhoneBook_log.ldf'' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) '

PRINT @sql
EXEC (@sql)

Upvotes: 3

Related Questions