Setting SQL Server Default Locations

Question

Post reply

Setting SQL Server Default Locations

WayneS

SSC Guru

Points: 95409
More actions
November 10, 2010 at 9:37 pm

#226248

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

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

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

Hardy21

SSCrazy Eights

Points: 9708

November 11, 2010 at 2:11 am

#1249328

Really Nice information. Thanks for sharing.

Thanks

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

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

Geoff A SSChampion Points: 11511 More actions · Answer 3

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

thanks

WayneS SSC Guru Points: 95409 More actions · Answer 4

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

Caine Schneider Old Hand Points: 355 More actions · Answer 5

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

daniel9ds Valued Member Points: 57 More actions · Answer 6

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

__________________

free photoshop tutorials

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

"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

WayneS SSC Guru Points: 95409 More actions · Answer 8

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

WayneS SSC Guru Points: 95409 More actions · Answer 9

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

Caine Schneider Old Hand Points: 355 More actions · Answer 10

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 11

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

WayneS SSC Guru Points: 95409 More actions · Answer 12

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

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 13

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.

WayneS SSC Guru Points: 95409 More actions · Answer 14

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