Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Setting SQL Server Default Locations Expand / Collapse
Author
Message
Posted Wednesday, November 10, 2010 9:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
Comments posted to this topic are about the item Setting SQL Server Default Locations

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1019045
Posted Thursday, November 11, 2010 2:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:43 AM
Points: 1,130, Visits: 1,391
Really Nice information. Thanks for sharing.

Thanks
Post #1019148
Posted Thursday, November 11, 2010 4:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:17 AM
Points: 2,281, Visits: 4,227
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
Post #1019227
Posted Thursday, November 11, 2010 5:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:08 AM
Points: 514, Visits: 1,735
great article. will make a future project i know i have coming up much easier...
thanks
Post #1019234
Posted Thursday, November 11, 2010 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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.


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1019353
Posted Thursday, November 11, 2010 8:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 28, 2014 10:47 AM
Points: 323, Visits: 125
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



Post #1019371
Posted Thursday, November 11, 2010 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 11, 2010 9:57 AM
Points: 1, Visits: 0
I am a newbie here and just wanna say Hi to everyone. I am Daniel from Pennsylvania, US.


__________________
free photoshop tutorials
Post #1019425
Posted Thursday, November 11, 2010 10:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:17 AM
Points: 2,281, Visits: 4,227
"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
Post #1019455
Posted Thursday, November 11, 2010 11:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1019473
Posted Thursday, November 11, 2010 11:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1019474
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse