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”.

image

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