DesignDB Location

Artikel
12/18/2007

As you might know Visual Studio Team System Database Edition uses a local SQL Server database instance to validate the correctness of the code inside the database project. When you create a project a local scratch database is create for validation purposes, when you close project the database gets detached and when you re-open the project it gets re-attached if the database file actually exist and did not change in between. If changes are detected, which is detected based on the file MD5, the database gets dropped and recreated.

In this blog post I want to discuss where the Design Validation Database, or DesignDB for short is being created.

When VSDB attaches or creates the DesignDB it first determines the location of the databases for the SQL Server instance you specified to be used in Tools->Options->Database Tools->Design-time Validation Database.

Where is DesignDB created?

VSDB determines the DesignDB location is through the following T-SQL query:

    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

First the code checks the SQL Server DefaultData setting in the Registry, since this setting is optional and by default not set when SQL Server gets installed, when this returns NULL the code gets the file path from the MASTER database.

NOTE: In the case we use the master data path the underlying code separates the file name from the file path, this is not done instead the query.

VSDB only uses the DefaultData location however you should also look at the DefaultLog location, so the following query is more generic:

    1:  DECLARE @datadir nvarchar(260),

    2:          @logdir  nvarchar(260),

    3:          @rc      int

4:

    5:  EXEC @rc = [master].[dbo].[xp_instance_regread]

    6:          N'HKEY_LOCAL_MACHINE',

    7:          N'Software\Microsoft\MSSQLServer\MSSQLServer',

    8:          N'DefaultData',

    9:          @datadir output,

   10:          'no_output'

11:

12:

   13:  EXEC @rc = [master].[dbo].[xp_instance_regread]

   14:          N'HKEY_LOCAL_MACHINE',

   15:          N'Software\Microsoft\MSSQLServer\MSSQLServer',

   16:          N'DefaultLog',

   17:          @logdir output,

   18:          'no_output'

19:

   20:  SELECT  @datadir AS DefaultData,

   21:          @logdir AS DefaultLog

   22:  GO

Changing the location

What if you need or want to change the location where the design-time validation databases are created? You can achieve this by changing the DefaultData path Registry settings. You can do this in three ways.

Using SQL Server Management Studio
Using T-SQL script
Using the Registry Editor

NOTE: If you are changing the DefaultData location, you also want to change the DefaultLog location. VSDB will not use the DefaultLog setting, but when you issue a statement like CREATE DATABASE MyDB the MyDB.mdf file will end up in the DefaultData location while the MyDB_log.LDF file will end up in the DefaultLog location.

Using SSMS

You can change the DefaultData path using SSMS (SQL Server Management Studio) via the Server properties. After you started SSMS, right click on the server name and choose Properties, this will launch the Server Properties dialog.

At the bottom of the dialog under the "Database default locations" line you will find two text boxes, labeled Data and Log. The Data one reflects the state of the DefaultData and Log the DefaultLog Registry value. You can change these values in the dialog and hit OK, or you can choose the Script option and you will get the T-SQL script to apply the change using the EXEC xp_instance_regwrite statement. Using SSMS has the advantage you can browse the server side directory structure which reduces the change you enter a directory value that does not exist.

NOTE: The directory value provided does not get validated, neither by the SSMS UI or by SQL Server when changed!

Using T-SQL

You do not have to use SSMS to make these changes, you can simply execute the following query to set the location of the DefaultData and DefaultLog directory.

Set location using T-SQL:

    1:  DECLARE @datadir nvarchar(260),

    2:          @logdir nvarchar(260),

    3:          @rc int

4:

    5:  SELECT  @datadir = N'c:\temp',

    6:          @logdir = N'c:\temp'

7:

    8:  EXEC @rc = [master].[dbo].[xp_instance_regwrite]

    9:          N'HKEY_LOCAL_MACHINE',

   10:          N'Software\Microsoft\MSSQLServer\MSSQLServer',

   11:          N'DefaultData',

   12:          N'REG_SZ',

   13:          @datadir

14:

   15:  EXEC @rc = [master].[dbo].[xp_instance_regwrite]

   16:          N'HKEY_LOCAL_MACHINE',

   17:          N'Software\Microsoft\MSSQLServer\MSSQLServer',

   18:          N'DefaultLog',

   19:          N'REG_SZ',

   20:          @logdir

21:

   22:  EXEC @rc = [master].[dbo].[xp_instance_regread]

   23:          N'HKEY_LOCAL_MACHINE',

   24:          N'Software\Microsoft\MSSQLServer\MSSQLServer',

   25:          N'DefaultData',

   26:          @datadir output,

   27:          'no_output'

28:

29:

   30:  EXEC @rc = [master].[dbo].[xp_instance_regread]

   31:          N'HKEY_LOCAL_MACHINE',

   32:          N'Software\Microsoft\MSSQLServer\MSSQLServer',

   33:          N'DefaultLog',

   34:          @logdir output,

   35:          'no_output'

36:

   37:  SELECT  @datadir AS DefaultData,

   38:          @logdir AS DefaultLog

   39:  GO

Using the Registry Editor

When using the Registry Editor you first have to find the correct hive location in the Registry for the SQL Server instance, which is easy to get wrong since the difference between instances is abstracted through a ID. The previous two methods abstract you from having to do this.

You first have to look up the instances in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

Which points you to an hive location in this example MSSQL.1
REG_SZ: SQL90 = MSSQL.1

Then you can use this information to locate the hive for instance name SQL90

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
REG_SZ: DefaultData = C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
REG_SZ: DefaultLog = C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

Important: RESTART REQUIRED!

No matter which technique you used to change the values for DefaultData or DefaultLog, you always need to restart the SQL Server service in order for the new values to take effect!

After you have restart SQL Server you should restart VSDB and your DesignDB databases will now reside in the newly defined location.

-GertD

Comments

Anonymous
December 18, 2007
In the " DesignDB Location " blog post I described where the design-time validation database are created.
Anonymous
December 18, 2007
In the " DesignDB Location " blog post I described where the design-time validation database

Freigeben über