In SQL Server 2000 there are two ways to identify the New Database default locations ie. where the data and log files are created when a database is created without specifying a filespec for the data or log file (CREATE DATABASE blah). These values are stored in the registry and can be queried using SQL Server Enterprise Manager by Right clicking on the SQL Server instance you wish to check and selectingProperties and selecting the Database Settings tab.
Or by executing the TSQL statements below to read the values directly from the registry:
EXEC master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultData’
EXEC master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultLog’
EXEC master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultLog’
Returns:
Value Data
———— ————-
DefaultData c:\MSSQL\Data
DefaultData c:\MSSQL\Data
(1 row(s) affected)
Value Data ———— ————-
DefaultLog c:\MSSQL\Log
(1 row(s) affected)
The values can be updated using Enterprise Manager or using the TSQL example below. In the example below the New Database default locations directory for both the data and log is being changed to c:\foo. Note: The SQL Server service needs to be restarted for these changes to take effect.
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultLog’,REG_SZ,N’c:\foo’
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultData’,REG_SZ,N’c:\foo’
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’DefaultData’,REG_SZ,N’c:\foo’
