• Okay, I've done some digging around.

    My system: SQL 2008 named instance (no default instance). Changed DefaultData set to "C:\SQLData". Verified that it was set by viewing in the "Running Values"

    Using Process Monitor, I set a filter to show any access to a registry key ending with "DefaultData".

    I then performed the create database script that Carl posted.

    There were no hits on this registry key. Database was created in the directory with the master database.

    I then restarted the sql services. As it was restarting, there was one hit on a registry key ending with "DefaultData", for the proper key for this instance. I then performed the create database script again. This time, the database was created in the C:\SQLData directory. No additional hits on registry keys ending with "DefaultData"

    So, it appears that after setting this registry value, that the sql services must be restarted for it to be recognized. It seems to me that this would be a very minor thing to have the CREATE DATABASE statement read the registry value for this instance if a specific path was not specified.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2