Default location for new DB

Question

Post reply

Default location for new DB

Dave -310292

SSC-Addicted

Points: 440
More actions
July 10, 2007 at 10:27 am

#175214

My application creates a new database in the C:\program files\microsoft\MSSQL......\Data location. I would like to change the default installation location for new DB's. Is there a way to do this? Possibly by changing a registry setting. I do not want to uninstall and reinstall my MSSQL to a different drive.

Viewing 9 posts - 1 through 8 (of 8 total)

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

bledu SSChampion Points: 12335 More actions · Answer 1

DECLARE @data_path nvarchar(256);

SET

@data_path = 'What ever you want'

--execute the CREATE DATABASE statement

EXECUTE

('CREATE DATABASE Test

ON

( NAME = Test_dat,

FILENAME = '''

+ @data_path + 'testdat.mdf'',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = Test_log,

FILENAME = '''

+ @data_path + 'testlog.ldf'',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )'

);

GO

Everything you can imagine is real.

rubes SSCrazy Points: 2058 More actions · Answer 2

The default directories can be set at the server level.

Go to properties, database settings and you will see a place for Database default locations. This is the same in both Enterprise Manager and SQL Server Management Studio.

Regards,
Rubes

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Once you set the default in the EM then all/any databases created without path will take the default location specified this can be only override specifying the location when you create the database.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

bledu SSChampion Points: 12335 More actions · Answer 4

how are you creating the dbs from your application at the present moment.

Everything you can imagine is real.

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

The default location specified in Enterprise Manager only applys when creating the database with Enterprise Manager and has no effect on the database file locations when using the command "CREATE DATABASE".

For a "CREATE DATABASE" without any file location specification, the files will be created in the same locations as the model database.

This has been tested.

SQL = Scarcely Qualifies as a Language

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

Accepted from query analyzer if you don't specify the path the path of the model database is taken for creating the database.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Dave -310292 SSC-Addicted Points: 440 More actions · Answer 7

I made the change in EM and tried building the DB. I worked like a champ. Thanks to all for your suggestions.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Dave,

If you want to do this from an app or even TSQL itself, see the following script for how to do it... and, yes, it is a registry change...

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1854

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)