tsql - 如何在 SQL 脚本中定义 mdf、ldf 文件默认位置路径

Question

我想将下面的脚本提供给我的客户以安装新数据库。
我如何重写这部分脚本以反射(reflect)它们的特定数据和日志文件位置:

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

我客户的windows盘不是c盘。
我可以更改这些路径以反射(reflect)它们的 sql 安装路径吗？
我怎样才能告诉这个脚本找到正确的位置？

Answer 1

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