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

Comments

• Anonymous
  February 11, 2010
  Hi Gert, a little bit different;

---

-- Defaults überprüfen -- Basisverzeichnisse auslesen -- Installationsverzeichnis EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerSetup', N'SQLDataRoot', @SQLDataRoot OUTPUT -- Datenverzeichnis EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @DefaultData OUTPUT -- Logverzeichnis EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @DefaultLog OUTPUT -- Backupverzeichnis EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT -- Pfade aufbauen IF LEFT(REVERSE(@SQLDataRoot), 1) <> N'' SET @SQLDataRoot = @SQLDataRoot + N''    -- Regschlüssel = 0 dann Standardverzeichnis SET @DefaultData =  ISNULL(@DefaultData, @SQLDataRoot + 'DATA') SET @DefaultLog =  ISNULL(@DefaultLog, @SQLDataRoot + 'Data')

---

CU tosc