Sunday, March 25, 2012

Changing the default location of a new database

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change that to:-
D:\Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abelon 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

select the "database settings" tab and you can change the path were data and log files get created by default.

Under the covers, this setting is stored in the registry, here:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

in the DefaultData and DefaultLog values.|||Yes, that works! Thanks!

No comments:

Post a Comment