DefaultDataPath

VSDB uses the the ($DefaultDataPath) SQLCMD variable to represent the location where you would place your data and log file of your database. The deployment engine sets the value of SQLCMD variable by querying SQL Server using the following query:

    1:  DECLARE @value nvarchar(512),

    2:          @rc    int;

    3:  EXEC    @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @value output-- , 'no_output'; 

    4:  SELECT  @value AS [Value]

The problem is that this value by default is not getting populated when you install SQL Server! You have to set it using SQL Server Management Studio, using the Server properties dialog, on the Database Settings tab,  you will find the “Database default locations”.

You can programmatically set it using the following query:

    1:  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'd:\dbs\MSSQL10.MSSQLSERVER\MSSQL\DATA'

This brings up the next question, what does VSDB return as the value if the DefaultData location Registry entry is not set? It returns the location of the master.mdf file. If you would turn on SQL tracing you will see the following query fly by:

    1:  DECLARE @filepath nvarchar(260),

    2:          @rc int

    3:   

    4:  EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @filepath output, 'no_output' 

    5:   

    6:  IF ((@filepath IS NOT NULL) AND (CHARINDEX(N'\', @filepath, len(@filepath)) = 0))

    7:      SELECT     @filepath = @filepath + N'\'

    8:   

    9:  IF (@filepath IS NULL)

   10:      SELECT     @filepath = [sdf].[physical_name]

   11:      FROM       [master].[sys].[database_files] AS [sdf]

   12:      WHERE      [file_id] = 1

   13:   

   14:  SELECT @filepath AS FilePath

Now this still does not mean it works, because SQL Server or SQL Server Management Studio not validate if this location actually exists and is accessible from within SQL Server.

So the question becomes how can you protect yourself against this inside my deployment?

The following script can be helpful as a pre-deployment script to check if the $(DefaultDataPath) location actually exists and take action accordingly.

DataPathExistsCheck.sql

    1:  -- If we raise an error we want to stop execution of the deployment engine

    2:  :ON ERROR EXIT

    3:   

    4:  SET NOCOUNT ON

    5:  DECLARE @value nvarchar(512),

    6:          @rc    int;

    7:  EXEC    @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @value output-- , 'no_output'; 

    8:  -- debug: shows the output

    9:  -- SELECT  @value AS [Value], @rc as [RC];

   10:   

   11:  DECLARE @result int

   12:  DECLARE @table as table(FileExists int, DirExist int, ParentDirExists int)

   13:  -- debug: shows the output

   14:  -- EXEC [master].[dbo].[xp_fileexist] @value

   15:  INSERT  @table EXEC [master].[dbo].[xp_fileexist] @value

   16:   

   17:  IF EXISTS (SELECT * FROM @table WHERE DirExist = 1)

   18:  BEGIN

   19:      PRINT ' DIRECTORY ' + @value + ' EXISTS'

   20:  END

   21:  ELSE

   22:  BEGIN

   23:      PRINT ' DIRECTORY ' + @value + ' DOES NOT EXIST'

   24:      RAISERROR('DIRECTORY DOES NOT EXIST', 16, 1)

   25:  END

   26:   

   27:  :ON ERROR IGNORE 

I hope this is helpful and builds a better understanding on how VSDB works under the covers,

GertD @ www.DBProj.com