SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Setting SQL Server Default Locations / Latest Posts

RE: Setting SQL Server Default Locations

WayneS — Wed, 16 Nov 2011 05:56:06 GMT

Just restart the SQL Services.If you set the directories from the GUI, then those keys will be built for you.When writing this article, I didn't consider clustered instances. They may go somewhere else... I'll try to find some time to investigate.

RE: Setting SQL Server Default Locations

ar20024u — Mon, 14 Nov 2011 15:46:19 GMT

so, if my instance is missing the following registry entries:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\DefaultData & DefaultLog and i need to add these two entries on a cluster, do i need to reboot the machine or just restart sql services to take effect?

RE: Setting SQL Server Default Locations

rzl_rmd — Wed, 05 Oct 2011 20:35:54 GMT

That was Pretty NIce. More Powers_________________________________________________________[url=http://www.thefowlergroup.com/fort-worth-texas-search-engine-optimization-seo-experts/]seo fort worth[/url]

RE: Setting SQL Server Default Locations

Steve Jones - SSC Editor — Sat, 25 Dec 2010 14:24:43 GMT

voted. That should be a no-brainer.

RE: Setting SQL Server Default Locations

WayneS — Wed, 22 Dec 2010 21:03:50 GMT

FYI, I finally got around to creating a connect item for the default data/log directories not be honored until service restart [url=https://connect.microsoft.com/SQLServer/feedback/details/633050/defaultdata-defaultlog-directory-settings-not-honored-until-sql-server-service-is-restarted][u]here[/u][/url].I encourage everyone to read it, and to vote for it.

RE: Setting SQL Server Default Locations

MO-933989 — Tue, 16 Nov 2010 03:59:37 GMT

Hi,I'm trying to move the database location for and SQL2008 Cluster. I have set the default data and log file location to the new drive with no issue. However I am unable to move the existing database. I'm using the alter database method but they do not mount when I attempt to bring them back online.I think the problem is with the folder security of my new location. The original location has permissions for MSSQLSERVER. I'm unable to find this to add it to the new location.Any ideas?Thanks,Mark

RE: Setting SQL Server Default Locations

WayneS — Fri, 12 Nov 2010 10:52:45 GMT

[quote][b]Steve Jones - SSC Editor (11/12/2010)[/b][hr]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.[/quote]Thanks for the corrections Steve!

RE: Setting SQL Server Default Locations

Steve Jones - SSC Editor — Fri, 12 Nov 2010 08:38:23 GMT

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.

RE: Setting SQL Server Default Locations

WayneS — Fri, 12 Nov 2010 08:22:48 GMT

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.

RE: Setting SQL Server Default Locations

Carl Federl — Fri, 12 Nov 2010 05:40:34 GMT

Caine posted:[quote]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.[/quote]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.

RE: Setting SQL Server Default Locations

Caine Schneider — Thu, 11 Nov 2010 11:15:05 GMT

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

RE: Setting SQL Server Default Locations

WayneS — Thu, 11 Nov 2010 11:07:30 GMT

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.

RE: Setting SQL Server Default Locations

WayneS — Thu, 11 Nov 2010 11:04:21 GMT

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.

RE: Setting SQL Server Default Locations

Carl Federl — Thu, 11 Nov 2010 10:39:24 GMT

"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 !

RE: Setting SQL Server Default Locations

daniel9ds — Thu, 11 Nov 2010 09:58:01 GMT

I am a newbie here and just wanna say Hi to everyone. I am Daniel from Pennsylvania, US.__________________[url=http://www.photoshop-garden.com]free photoshop tutorials[/url]

RE: Setting SQL Server Default Locations

Caine Schneider — Thu, 11 Nov 2010 08:25:14 GMT

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 OUTPUTPRINT @smoBackupDirectory GOThis 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.aspxHope this helps.Caine

RE: Setting SQL Server Default Locations

WayneS — Thu, 11 Nov 2010 08:06:13 GMT

[quote][b]Carl Federl (11/11/2010)[/b][hr]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:[code]set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) , 250)[/code]causes the "\" to be included as part of the @servername and needs to change to:[code]set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) + 1 , 250)[/code][/quote]Carl, thanks for the feedback. However, when I run your code, the database is created in the proper location. Here are my results:[code="plain"]DBName filename------ ------------------------------------------------------master C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdfmaster C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldfDBName filename---------------- ------------------------------------------------------TestFileLocation C:\SqlData\TestFileLocation.mdfTestFileLocation C:\SqlData\TestFileLocation_log.LDFProcessed 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).[/code]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:

RE: Setting SQL Server Default Locations

Geoff A — Thu, 11 Nov 2010 05:20:42 GMT

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

RE: Setting SQL Server Default Locations

Carl Federl — Thu, 11 Nov 2010 04:32:52 GMT

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:[code="sql"]use mastergoselect 'master' as DBName , filename from master.sys.sysfiles;gocreate database TestFileLocationgoselect 'TestFileLocation' as DBName, filename from TestFileLocation.sys.sysfilesgobackup database TestFileLocation to disk = 'TestFileLocation.bak'godrop database TestFileLocationgo[/code]Also the registry read statements returns nulls because the statment:[code]set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) , 250)[/code]causes the "\" to be included as part of the @servername and needs to change to:[code]set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) + 1 , 250)[/code]

RE: Setting SQL Server Default Locations

Hardy21 — Thu, 11 Nov 2010 02:11:04 GMT

Really Nice information. Thanks for sharing.

Setting SQL Server Default Locations

WayneS — Wed, 10 Nov 2010 21:37:55 GMT

Comments posted to this topic are about the item [B]Setting SQL Server Default Locations[/B]