Script for check the SQL Server Instance Path.

Script for check the SQL Server Instance Path.

Some time for create the database we specify the the default path including the drive name e.g(C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA).

Problem : If SQL Server Instance is installed in different drive eg(d:,e:) in that case case database creatin script getting fail.

Solution : Here is the script to extract the instance path and create the database.


USE [master]
declare @InstancePath as varchar(1000)
declare @fileName as varchar(1000)
select @InstancePath=filename from sysfiles
select @fileName = SUBSTRING(@InstancePath,0,LEN(@InstancePath)-CHARINDEX('\',REVERSE(@InstancePath))+1)
declare @stringQuiry as nvarchar(4000)
Set @stringQuiry = 'CREATE DATABASE [MAPDB] ON  PRIMARY
( NAME = N''MAPDB'', FILENAME = N''' + @fileName + + '\<DatabaseName>.mdf' + ''' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N''MAPDB_log'', FILENAME = N''' + @fileName + '\<DatabaseName>_log.ldf' + ''' , SIZE = 1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
'
EXEC sp_executesql @stringQuiry