Thursday, March 29, 2012

data file path in SQL 2005

After I created an instance in SQL 2005, can I still change the data file
path and how? Thanks.
Do you mean the default location for databases? If so use the Management
Studio and go to the properties of the instance. One of the categories is
databases and yoiu can set the default location there.
-003KobeBrian wrote:

> After I created an instance in SQL 2005, can I still change the data file
> path and how? Thanks.
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email
|||I found the database default location. I wonder how this works since I can
see the datafile path in individual database. So I specify the database
default location at top level, will that overwrite the datafile path in
individual database?
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> Do you mean the default location for databases? If so use the Management
> Studio and go to the properties of the instance. One of the categories is
> databases and yoiu can set the default location there.
> -003KobeBrian wrote:
>
> --
> Brett I. Holcomb
> brettholcomb@.R777bellsouth.net
> Remove R777 to email
|||When you create a new database, you can specify a specific file location, or
use the default. If you do not specify a new location, the default will be
used. If you specify a location, it will override the default.
The default only applies to new databases. It will not 'overwrite' any
location for existing database files.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"-003KobeBrian" <kb@.yahoo.com> wrote in message
news:epJhkvrDHHA.348@.TK2MSFTNGP06.phx.gbl...
>I found the database default location. I wonder how this works since I can
>see the datafile path in individual database. So I specify the database
>default location at top level, will that overwrite the datafile path in
>individual database?
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>
|||Hi
If you want to move the data and log files to a different location for
existing database then you can call sp_detach_db to detach the files, move
the file (at a command prompt!) and then call sp_attach_db to re-attach the
data and log files. Alternatively you can backup the database and restore it
specifying a different location for the files on the options table or use the
MOVE clause in the RESTORE command in T-SQL. You may want to read
http://support.microsoft.com/kb/224071/
HTH
John
"-003KobeBrian" wrote:

> I found the database default location. I wonder how this works since I can
> see the datafile path in individual database. So I specify the database
> default location at top level, will that overwrite the datafile path in
> individual database?
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:%23UQ7WCrDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>
>
|||To add on to other posts.. Use the below uRL to move system databases
http://www.sqlservercentral.com/columnists/vIacoboni/2605.asp
Thanks
Hari
"-003KobeBrian" <kb@.yahoo.com> wrote in message
news:OELu8ZqDHHA.4464@.TK2MSFTNGP06.phx.gbl...
> After I created an instance in SQL 2005, can I still change the data file
> path and how? Thanks.
>
|||Very helpful. Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D9A36D72-A50F-464C-AC88-A3C5E8420123@.microsoft.com...[vbcol=seagreen]
> Hi
> If you want to move the data and log files to a different location for
> existing database then you can call sp_detach_db to detach the files, move
> the file (at a command prompt!) and then call sp_attach_db to re-attach
> the
> data and log files. Alternatively you can backup the database and restore
> it
> specifying a different location for the files on the options table or use
> the
> MOVE clause in the RESTORE command in T-SQL. You may want to read
> http://support.microsoft.com/kb/224071/
> HTH
> John
> "-003KobeBrian" wrote:

No comments:

Post a Comment