Setting SQL Server Default Locations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Setting SQL Server Default Locations

21 posts, Page 2 of 3

««1 2 3 »»

Setting SQL Server Default Locations

Rate Topic

Display Mode

Topic Options

Author

Message

Caine Schneider

Posted Thursday, November 11, 2010 11:15 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 7:56 AM
Points: 323, Visits: 118

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

Carl Federl

Posted Friday, November 12, 2010 5:40 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:55 PM
Points: 2,224, Visits: 4,079

Caine posted:

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

WayneS

Posted Friday, November 12, 2010 8:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 6,367, Visits: 8,226

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

Steve Jones - SSC Editor

Posted Friday, November 12, 2010 8:38 AM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

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

WayneS

Posted Friday, November 12, 2010 10:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 6,367, Visits: 8,226

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!

Post #1020096

MO-933989

Posted Tuesday, November 16, 2010 3:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:36 AM
Points: 5, Visits: 150

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

WayneS

Posted Wednesday, December 22, 2010 9:03 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 6,367, Visits: 8,226

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!

Post #1038585

Steve Jones - SSC Editor

Posted Saturday, December 25, 2010 2:24 PM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

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

rzl_rmd

Posted Wednesday, October 05, 2011 8:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 05, 2011 8:35 PM
Points: 1, Visits: 1

That was Pretty NIce. More Powers

_________________________________________________________
seo fort worth

Post #1186319

ar20024u

Posted Monday, November 14, 2011 3:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 4:07 PM
Points: 1, Visits: 3

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 »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions