How often have you had the opportunity to go back to basics and learn the same thing again in a different manner? How many of you are developers here? I am sure each one of us write a piece of code that gets onto the production – how often have you been revisiting the same code say after a week post release. Have you ever tried to look at the code to enhance it or do the same thing differently? I am sure the majority of you never even want to look at the old code you wrote. This is second to human nature and trust me, you are not alone in this planet with this attitude. Let us learn about Database Default Locations.
I am no different, but I always am on the hunt to learn new ways to work for the same old problems we had for ages. When you just got Management studio and write a “CREATE DATABASE” command without any paths, where does the files get created? Often it is a mystery and for new age DBA’s it would be almost impossible. I wanted to check the blog about this and found a number of posts describing the problem – either using TSQL, UI etc. Some are here for a reference.
SQL SERVER – Changing Default Installation Path for SQL Server
SQL SERVER – Find Location of Data File Using T-SQL
From a recap perspective, the best solution and the most used solution to this is using the UI. We can go to SQL Server Management Studio -> Right Click on Server node -> Properties -> Database Settings. We will be presented with a section where the default location for our database data and log files are present.
From SQL server 2016, there is an enhancement added to the SERVERPROPERTY () function, wherein we can get this information that is useful.
-- SERVER PROPERTIES added for Default Paths
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath
As you can see now, we have gathered this important information via standard TSQL without any registry hacks or string manipulations etc.
As I personally felt this was an interesting addition. It was also nice to know that a total of 8 more properties was added to the SERVERPROPERTY function.
SELECT
SERVERPROPERTY ('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY ('InstanceDefaultLogPath') AS InstanceDefaultLogPath,
SERVERPROPERTY ('ProductBuild') AS ProductBuild,
SERVERPROPERTY ('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY ('ProductMinorVersion') AS ProductMinorVersion,
SERVERPROPERTY ('ProductBuildType') AS ProductBuildType,
SERVERPROPERTY ('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY ('ProductUpdateReference') AS ProductUpdateReference
I am sure you will find some of these interesting and please make sure to use these in your environments as SQL Server 2016 gets rolled out. I am sure there will be tons of learnings for sure.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Here a small script with a fallback to the SQL data root path stored in the registry:
DECLARE @instanceName SQL_VARIANT, @instancePath SQL_VARIANT, @sqlRootRegKey NVARCHAR(1024)
, @sqlDataRoot NVARCHAR(512), @sqlDataPath NVARCHAR(512), @sqlLogPath NVARCHAR(512);
— Get SQL data root path from registry as fallback if database default paths are empty
SELECT @instanceName = ISNULL(SERVERPROPERTY(‘InstanceName’), ‘MSSQLServer’);
EXECUTE [master].dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL’, @instanceName, @instancePath OUTPUT;
SELECT @sqlRootRegKey = ‘Software\Microsoft\Microsoft SQL Server\’ + CONVERT(VARCHAR(64), @instancePath) + ‘\Setup’;
EXECUTE [master].dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, @sqlRootRegKey, ‘SQLDataRoot’, @sqlDataRoot OUTPUT;
— Read database default paths with fallback to SQL data root path
SELECT @sqlDataPath = ISNULL(CAST(SERVERPROPERTY(‘InstanceDefaultDataPath’) AS NVARCHAR(512)), @sqlDataRoot);
SELECT @sqlLogPath = ISNULL(CAST(SERVERPROPERTY(‘InstanceDefaultLogPath’) AS NVARCHAR(512)), @sqlDataRoot);
SELECT @sqlDataPath, @sqlLogPath, @sqlDataRoot;
Thanks for sharing.
Thanks for this good article.
SERVERPROPERTY (‘InstanceDefaultBackupPath’) AS InstanceDefaultBackupPath is also an option :-)