November 10, 2010 at 9:37 pm
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
November 11, 2010 at 2:11 am
Really Nice information. Thanks for sharing.
Thanks
November 11, 2010 at 4:32 am
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
November 11, 2010 at 5:20 am
great article. will make a future project i know i have coming up much easier...
thanks
November 11, 2010 at 8:06 am
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
November 11, 2010 at 8:25 am
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
November 11, 2010 at 9:58 am
I am a newbie here and just wanna say Hi to everyone. I am Daniel from Pennsylvania, US.
__________________
November 11, 2010 at 10:39 am
"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
November 11, 2010 at 11:04 am
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
November 11, 2010 at 11:07 am
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
November 11, 2010 at 11:15 am
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
November 12, 2010 at 5:40 am
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
November 12, 2010 at 8:22 am
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
November 12, 2010 at 8:38 am
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.
November 12, 2010 at 10:52 am
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply