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 Thursday, November 11, 2010 11:15 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
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



Post #1019478
Posted Friday, November 12, 2010 5:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 2,281, Visits: 4,241
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
Post #1019843
Posted Friday, November 12, 2010 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 5,370, Visits: 9,008
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
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 #1019970
Posted Friday, November 12, 2010 8:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,355, Visits: 15,819
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1019990
Posted Friday, November 12, 2010 10:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 5,370, Visits: 9,008
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
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 #1020096
Posted Tuesday, November 16, 2010 3:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:10 AM
Points: 5, Visits: 154
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
Post #1021348
Posted Wednesday, December 22, 2010 9:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 5,370, Visits: 9,008
FYI, I finally got around to creating a connect item for the default data/log directories not be honored until service restart here.

I encourage everyone to read it, and to vote for 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 #1038585
Posted Saturday, December 25, 2010 2:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,355, Visits: 15,819
voted. That should be a no-brainer.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1039221
Posted Wednesday, October 5, 2011 8:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 5, 2011 8:35 PM
Points: 1, Visits: 1
That was Pretty NIce. More Powers


_________________________________________________________
seo fort worth
Post #1186319
Posted Monday, November 14, 2011 3:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:20 PM
Points: 1, Visits: 9
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?
Post #1205604
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse