default file locations

Question

Post reply

default file locations

martin nickel

SSC Enthusiast

Points: 199
More actions
June 1, 2007 at 7:24 pm

#116987

in management studio, on the server properties "database setting" page i can view and change "Database default locations" for data and log files. i'd like to get these values in a sql script that creates a new database.
it seems like this should be easy, but i've searched for quite a while without finding anything.
thanks a million for your ideas and suggestions!
martin

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 1

Obviously, you'll need "SA" privs to do this...

--===== Declare local variables

DECLARE @DefaultDataPath SYSNAME

DECLARE @DefaultLogPath SYSNAME

DECLARE @InstanceName SYSNAME

DECLARE @KeyPath SYSNAME

DECLARE @RootKey SYSNAME

DECLARE @ServerName SYSNAME

--===== Presets

SET @RootKey = 'HKEY_LOCAL_MACHINE'

SET @ServerName = @@SERVERNAME

SET @InstanceName = SUBSTRING(@@SERVERNAME,CHARINDEX('\',@@SERVERNAME)+1,128)

SET @KeyPath = 'SOFTWARE\Microsoft\Microsoft SQL Server\'

+ @InstanceName

+ '\MSSQLServer'

--===== Determine the default paths for data and log files

EXEC Master..xp_RegRead @RootKey, @KeyPath, 'DefaultData', @Value = @DefaultDataPath OUTPUT

EXEC Master..xp_RegRead @RootKey, @KeyPath, 'DefaultLog' , @Value = @DefaultLogPath OUTPUT

--===== Display the results

SELECT @ServerName AS ServerName,

@InstanceName AS InstanceName,

@DefaultDataPath AS DefaultDataPath,

@DefaultLogPath AS DefaultDataPath

...And, if you understand the following...

DECLARE @RootKey               SYSNAME
DECLARE @InstalledInstancePath SYSNAME
    SET @RootKey               = 'HKEY_LOCAL_MACHINE'
    SET @InstalledInstancePath = 'SOFTWARE\Microsoft\Microsoft SQL Server'

--===== Find all the installed instances of SQL Server
 CREATE TABLE #Instance (RowNum INT IDENTITY(1,1), InstanceID SYSNAME, InstanceName SYSNAME, Data NVARCHAR(128))
 INSERT INTO #Instance (InstanceID,InstanceName,Data)
   EXEC Master..xp_RegRead @RootKey, @InstalledInstancePath, 'InstalledInstances'

 SELECT * FROM #Instance

... you'll get an idea of how sophisticated you can make this...

Once you understand how to read the registry entries, Googe "xp_RegWrite" to find what you want to do... I don't want to post code that writes to the registry so no one makes a mess of their machine playing around

Lemme know if that's what you were looking for

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

another way you can find out how to change the default file location (or any other setting that's modifiable in SSMS) is to open the Server Properties dialog, change the file paths on the "Database Settings" tab, and then choose "script to new query window"

This will show you the script that would be executed if you hit the OK button. it's a good way to find out how to do things in script that you are used to doing in the GUI.

---------------------------------------
elsasoft.org

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

Heh... I've really got to get 2k5... Just for grins, Jezemine, can you post the code as in your example for us 2k guys??? Would be interesting for me to see what the differences are on this particular subject, if any.

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

note that you can use SSMS to manage both 2000 and 2005 servers, so no reason not to upgrade your client tools Jeff.

here's the output for a 2005 server - I believe it's the same as you'd get in 2000. xp_instance_regwrite is the xp that is "instance aware" so it writes to the correct location no matter how many instances are installed on the server.

USE

[master]

GO

EXEC

xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'E:\asdf'

GO

EXEC

xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\qwert'

GO

---------------------------------------
elsasoft.org

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

Thanks Jesemine...

Heh... yeah... the only reason I don't upgrade the client tools right away is because we still use 2k at work and I need to be able to answer 2k questions for work.

Haven't tried it but is it possible to have both set's of client tools in place on the same box with the understanding that the 2k tools aren't gonna work on the 2k5 db?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 6

yes, it's possible to have both sets of tools installed on the same client.

---------------------------------------
elsasoft.org

martin nickel SSC Enthusiast Points: 199 More actions · Answer 7

martin nickel

SSC Enthusiast

Points: 199

June 5, 2007 at 10:06 am

#710881

thank you jeff, for your excellent example code!

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 8

You bet, Martin... thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 9

Perfect... thanks for the info!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 10

so, Jeff, you are still on 2000 - were you aware that the 2008 CTP is out already?

Perhaps you'll end up skipping 2005 altogether!

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

---------------------------------------
elsasoft.org

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

Oh yeah... been thinking about that. Upgrading at work is going to be a bugger and rumor has it that MS is going to stop "supporting" 2000 sometime in August (although that date may have changed).

Looks like I'll be missing my old friend, Query Analyzer, no matter what

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)