Post reply

default file locations

  • 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

  • June 1, 2007 at 9:07 pm

    #710456

    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)

  • June 2, 2007 at 2:38 pm

    #710476

    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

  • June 2, 2007 at 3:50 pm

    #710479

    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)

  • June 3, 2007 at 9:49 am

    #710498

    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

  • June 3, 2007 at 9:57 am

    #710501

    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)

  • June 3, 2007 at 10:41 am

    #710503

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

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

  • June 5, 2007 at 10:06 am

    #710881

    thank you jeff, for your excellent example code!

  • June 5, 2007 at 7:11 pm

    #710979

    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)

  • June 5, 2007 at 7:12 pm

    #710980

    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)

  • June 5, 2007 at 11:58 pm

    #711027

    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

  • June 6, 2007 at 6:19 am

    #711075

    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)

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

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