DesignDB Location
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