How to find SQL Server instance default Data direcory location during execution

[Parimal Patel]

Hi

I have bunch of SQL scripts which create database into SQL Server 2012 and SQL Server 2014 and I would like to create database under default instance's Data Directory location.
Do i need to read this value from Registry, if yes than how i can do it?

Image 1.png (53.07 KiB) Viewed 8912 times

Regards,
Parimal Patel

[Dan]

Hi Patel,

We do not have predefined support for this.

However, note that for SQL Server 2012 and above you can take advantage of the SERVERPROPERTY (Transact-SQL). For example, you can use the following query to retrieve the DefaultDataPath and DefaultLogPath for the related instance:

Code: Select all

select 
    InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
    InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')

So, you can create a query in the SQL Query Tab that will set a property with the result.

Let me know if you need any help.

Best regards,
Dan

[Parimal Patel]

Thanks Dan

I have changed my script to find the default location as you have stated.

[Parimal Patel]

Also if i am deploying or installing application against particular SQL instance but during uninstall its not using SQL instance which i have selected during installation. Look like its executing my uninstall script again default SQL instance (which is localhost). How can i fix this issue?

[Dan]

Hello,

Can you please try to set the property that is attached to the SQL Server as persistent and see if the behaviour still persists?

In the Install Parameters page you can set the property as persistent.

Let me know if that helped.

Best regards,
Dan

[Parimal Patel]

So, How can i make [SERVER_PROP] property as a persistent Property or Parameter? I think [SERVER_PROP] is a Advanced Installer Property, is there any way i can convert to Persistent?

[Dan]

Hello,

In the Install Parameters page you can set the property as persistent. Just enable the Set persistent property option when creating the SERVER_PROP property.

Let me know if that helped.

Best regards,
Dan