Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: Changing the default location of a new database

    If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
    C:\Program Files\Microsoft SQL Server\MSSQL\Data
    I want to change that to:-
    D:\Database Files
    I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
    C:\Program Files\Microsoft SQL Server\MSSQL\Data

    So how do I change the default?
    (It's not satisfactory to have to move each database after it's created)

    Thanks, Andy Abel

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    on 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

    select the "database settings" tab and you can change the path were data and log files get created by default.

    Under the covers, this setting is stored in the registry, here:

    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

    in the DefaultData and DefaultLog values.
    Last edited by jezemine; 10-16-06 at 16:23.

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Yes, that works! Thanks!

  4. #4
    Join Date
    Apr 2009
    Posts
    1
    Hello all
    very informative. but just have a question on this. after chaging the settings does the sql server needs to be restarted?? as when use the scrip to create db - the db is not created in the changed location unless server is restarted. pls advise- sql server 2005 ; sp1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •