Setting SQL Server Default Locations

  • Comments posted to this topic are about the item Setting SQL Server Default Locations

    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

  • Really Nice information. Thanks for sharing.

    Thanks

  • Regarding your statements:

    "In the case of backups, this allows you to specify a backup with just a file name (from T-SQL), and the backup will go to this directory." this does work.

    "For data/log files, new databases will default to this location unless specified otherwise.", this does not appear to work and the database file location are the same as the master database. You can test this by setting the locations for DefaultData and DefaultLog and then run:

    use master

    go

    select 'master' as DBName , filename from master.sys.sysfiles;

    go

    create database TestFileLocation

    go

    select 'TestFileLocation' as DBName, filename from TestFileLocation.sys.sysfiles

    go

    backup database TestFileLocation to disk = 'TestFileLocation.bak'

    go

    drop database TestFileLocation

    go

    Also the registry read statements returns nulls because the statment:

    set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) , 250)

    causes the "\" to be included as part of the @servername and needs to change to:

    set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) + 1 , 250)

    SQL = Scarcely Qualifies as a Language

  • great article. will make a future project i know i have coming up much easier...

    thanks

  • Carl Federl (11/11/2010)


    Regarding your statements:

    "In the case of backups, this allows you to specify a backup with just a file name (from T-SQL), and the backup will go to this directory." this does work.

    "For data/log files, new databases will default to this location unless specified otherwise.", this does not appear to work and the database file location are the same as the master database. You can test this by setting the locations for DefaultData and DefaultLog and then run:

    Also the registry read statements returns nulls because the statment:

    set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) , 250)

    causes the "\" to be included as part of the @servername and needs to change to:

    set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) + 1 , 250)

    Carl, thanks for the feedback. However, when I run your code, the database is created in the proper location. Here are my results:

    DBName filename

    ------ ------------------------------------------------------

    master C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf

    master C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    DBName filename

    ---------------- ------------------------------------------------------

    TestFileLocation C:\SqlData\TestFileLocation.mdf

    TestFileLocation C:\SqlData\TestFileLocation_log.LDF

    Processed 160 pages for database 'TestFileLocation', file 'TestFileLocation' on file 2.

    Processed 2 pages for database 'TestFileLocation', file 'TestFileLocation_log' on file 2.

    BACKUP DATABASE successfully processed 162 pages in 0.310 seconds (4.076 MB/sec).

    And thanks for finding and fixing that bug - the +1 definitely needs to be there. I guess I forgot to test this on a server with named instances on it. :blush:

    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

  • This is basically the command that is issued when you get the property via SSMS:

    DECLARE @smoBackupDirectory varchar(250)

    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @smoBackupDirectory OUTPUT

    PRINT @smoBackupDirectory

    GO

    This should return the BackupDirectory key for the instance you run it on without doing a bunch of conditional logic based on the DB engine version. This should work in 2000 on up. I think it returns an error if the key does not exist, so watch out for that.

    I know there are tools that report this information for multiple instances, but I figured I could learn SSIS and collect a bunch of useful data (database file size trending and free space monitoring) if I did it myself. The SSIS package collects Server, Instance, Database, Database File, and Backup Set data into a repository similar to the one featured here:

    http://www.windowsitpro.com/article/sql-server/DBA-Repository-2010.aspx

    Hope this helps.

    Caine

  • I am a newbie here and just wanna say Hi to everyone. I am Daniel from Pennsylvania, US.

    __________________

    free photoshop tutorials

  • "the database is created in the proper location" How interesting as I am getting different results !

    An internet search revealed that this is a bug in SQL Server that was reported by Aaron Bertrand, SQL Server MVP, on February 10th, 2010. To confirm, I re-ran the tests on two different windows servers, one server with only a named instance and the other server with a default instance and a named instance.

    When creating a database on the named instance, the named instance's default file locations specifications are not read and one of two things happens:

    a) If there is a default instance, the default instance's default file locations are used instead of the named instance's location.

    b) If there is no default instance, the file locations are from the master database location of the named instance.

    How nasty !

    SQL = Scarcely Qualifies as a Language

  • Caine,

    Thanks for this information. However, in looking in the registry for this information, it does vary based on the engine version. After 2000, there is an extra "Microsoft SQL Server" in the registry path. Also, the first "MSSQLSERVER" is the instance name, and being set to MSSQLSERVER means it will always look in the default instance for this information.

    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

  • Carl,

    Very interesting. The server I tested this on has only a default instance.

    I wonder if this is connected to what Caine posted, where it is implied that SSMS is always looking in the registry for the default instance. This would seem to provide the results that you are seeing - assuming that if a null is returned, then it defaults to the location of the master db.

    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

  • I have a server with multiple instances (MSDE and SQL Express). I run the same exact code on both and the correct value for BackupDirectory came back as if it was read from the true instance directory.

    I'd have to retest this to verify my observation about the xp_instance_regread. I recall thinking that the key specified in the xp_instance_regread procedure call didn't necessarily exist verbatim in the registry.

    Caine

  • Caine posted:

    I have a server with multiple instances (MSDE and SQL Express). I run the same exact code on both and the correct value for BackupDirectory came back as if it was read from the true instance directory.

    I absolutely agree as the command "backup database X to disk = 'X.bak'" does result in the backup file being in the location specified by the BackupDirectory registry value. This works correctly for both default and named instances.

    If you use the SSMS GUI (Object Explorer, right click "database", then select "new database"), the default locations are filled in correctly for both default and named instances.

    The problem is limited to the case of a "create database" command where

    For a default instance, the files locations are as specified.

    For named instances, the file locations are NOT as specified.

    SQL = Scarcely Qualifies as a Language

  • 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

  • That's interesting, Wayne. I don't think that's documented anywhere.

    Also, I have corrected the typos in the article, which Wayne sent me.

  • Steve Jones - SSC Editor (11/12/2010)


    That's interesting, Wayne. I don't think that's documented anywhere.

    Also, I have corrected the typos in the article, which Wayne sent me.

    Thanks for the corrections Steve!

    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

Viewing 15 posts - 1 through 15 (of 21 total)

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