How to change default path for database creation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to change default path for database creation

Author

Topic

Nazri
Starting Member

8 Posts

Posted - 2007-05-01 : 02:15:45

Hi,
In my system, for creating a databae the default path is in C:\ drive, but iwant to change into D:\ drive as a default path.Please tell me how to change??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 02:18:27

Enterprise Manager - right click on the Server - property - Database settings - Default Data / Log Direcotry

Nazri
Starting Member

8 Posts

Posted - 2007-05-01 : 02:28:46

i am using sql server 2005, you mean that after creating the database i can change the path, but i want ,before creating a database can i change the default path.??? then whenever i create a database i automatically should created in D:\ drive(after changig default path)

quote:
Originally posted by Nazri

Hi,
In my system, for creating a databae the default path is in C:\ drive, but iwant to change into D:\ drive as a default path.Please tell me how to change??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 02:33:45

"before creating a database can i change the default path"
YES

SQL Server Management Studio - right click on the Server - property - Database settings - Database default location

Nazri
Starting Member

8 Posts

Posted - 2007-05-01 : 02:39:53

Than u very much khtan...

quote:
Originally posted by khtan

"before creating a database can i change the default path"
YES

SQL Server Management Studio - right click on the Server - property - Database settings - Database default location

KH

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-12-12 : 06:32:40

Is it possible to make this change programatically? i.e how can you change the default Data and default Log files via a script solution?

Jack Vamvas
--------------------
http://www.ITjobfeed.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 10:47:09

Yes you can.

USE [master]
GO
--Data file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'Location'
GO
-- Log file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'Location'
GO

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2008-12-12 : 17:22:10

Here is the link

http://gvphubli.blogspot.com/2008/04/default-database-file-path.html

TechnologyYogi
http://gvphubli.blogspot.com/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 17:33:38

quote:
Originally posted by gvphubli

Here is the link

http://gvphubli.blogspot.com/2008/04/default-database-file-path.html

TechnologyYogi
http://gvphubli.blogspot.com/

It can be achieved by just scripting in Server properties for database location.

Jason100
Starting Member

34 Posts

Posted - 2008-12-18 : 22:23:06

if you have created a database ,
you can
1.0 stop you sql server
2.0 copy the database .mdf and .log to you will store drive
3.0 drop the old database
4.0 Attach the database

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-18 : 23:17:09

quote:
Originally posted by Jason100

if you have created a database ,
you can
1.0 stop you sql server
2.0 copy the database .mdf and .log to you will store drive
3.0 drop the old database
4.0 Attach the database

.
Bad suggestion. Why stop SQL Server? You can just use ALTER Database Modify..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources