Default location of Data bases

Hi All,
I moved my main database from the C drive to the D drive
of my server. I left the rest of them on the C drive.
(master, msdb...) When I try to create a new DB it
defaults to the C drive since that is the way I installed
it. Is there a way to change that to the D drive with out
uninstalling and reinstalling?
Thanks,
JoeIn EM, right click on Server, then Properties. On Database Settings tab
choose Default data directory and Default log directory.
Ana
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:25c801c3fc6f$3a86d470$a101280a@.phx.gbl...
> Hi All,
> I moved my main database from the C drive to the D drive
> of my server. I left the rest of them on the C drive.
> (master, msdb...) When I try to create a new DB it
> defaults to the C drive since that is the way I installed
> it. Is there a way to change that to the D drive with out
> uninstalling and reinstalling?
> Thanks,
> Joe|||Thankssql

Default location of Data bases

Hi All,
I moved my main database from the C drive to the D drive
of my server. I left the rest of them on the C drive.
(master, msdb...) When I try to create a new DB it
defaults to the C drive since that is the way I installed
it. Is there a way to change that to the D drive with out
uninstalling and reinstalling?
Thanks,
JoeIn EM, right click on Server, then Properties. On Database Settings tab
choose Default data directory and Default log directory.
Ana
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:25c801c3fc6f$3a86d470$a101280a@.phx.gbl...
> Hi All,
> I moved my main database from the C drive to the D drive
> of my server. I left the rest of them on the C drive.
> (master, msdb...) When I try to create a new DB it
> defaults to the C drive since that is the way I installed
> it. Is there a way to change that to the D drive with out
> uninstalling and reinstalling?
> Thanks,
> Joe|||Thanks

Default FTData Location

I recently redistributed our database data files from one large VRAID5 disk
(G to 3 VRAID1 disks (T:, U:, V. At this point, I have moved everything
(including SQLAgent output & log files, job logs, tlogs, backups) EXCEPT:
1) FTDATA folder and its contents;
2) distmdl.mdf and distmdl.ldf
We do not use Full Text Searching, nor do we use replication. However, in
the interest of not leaving a ticking time bomb for someone who might later
want to use these features, I'd like to move these to drive T:.
Question 1: Is there an easy way to change the default location for FT
catalogs since I'm not even using full text right now? I found
http://support.microsoft.com/kb/240867/en-us for FTDATA but was hoping for a
simpler solution.
Question 2: How do I tell SQL where distmdl is if I enable replication? The
database doesn't appear to be attached in Ent Manager.
1) http://support.microsoft.com/kb/817296
2) when you create the distribution database you will be prompted where you
want to place the database files.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
news:AA566260-3CA2-44AB-A910-D2BBEEEF0529@.microsoft.com...
>I recently redistributed our database data files from one large VRAID5 disk
> (G to 3 VRAID1 disks (T:, U:, V. At this point, I have moved
> everything
> (including SQLAgent output & log files, job logs, tlogs, backups) EXCEPT:
> 1) FTDATA folder and its contents;
> 2) distmdl.mdf and distmdl.ldf
> We do not use Full Text Searching, nor do we use replication. However, in
> the interest of not leaving a ticking time bomb for someone who might
> later
> want to use these features, I'd like to move these to drive T:.
> Question 1: Is there an easy way to change the default location for FT
> catalogs since I'm not even using full text right now? I found
> http://support.microsoft.com/kb/240867/en-us for FTDATA but was hoping for
> a
> simpler solution.
> Question 2: How do I tell SQL where distmdl is if I enable replication?
> The
> database doesn't appear to be attached in Ent Manager.
>
>
|||Thanks for the info.
"Hilary Cotter" wrote:

> 1) http://support.microsoft.com/kb/817296
> 2) when you create the distribution database you will be prompted where you
> want to place the database files.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
> news:AA566260-3CA2-44AB-A910-D2BBEEEF0529@.microsoft.com...
>
>
|||Okay, one other question. What do I do with the existing distmdl? Do I
simply move the .mdf & .ldf whereever I want to, or delete them?
"Hilary Cotter" wrote:

> 1) http://support.microsoft.com/kb/817296
> 2) when you create the distribution database you will be prompted where you
> want to place the database files.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
> news:AA566260-3CA2-44AB-A910-D2BBEEEF0529@.microsoft.com...
>
>
|||do the following sp_helpdistributor and sp_helpdistributiondb. These will
tell you if the distributor is installed and if the distribution database
files you see are the ones in use.
DO NOT DELETE distmdl.mdf or distmdl.ldf as these are the model distribution
database files. If you delete these you won't be able to install replication
again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
news:87E3E406-96C8-408E-93D4-6BB86E93331F@.microsoft.com...[vbcol=seagreen]
> Okay, one other question. What do I do with the existing distmdl? Do I
> simply move the .mdf & .ldf whereever I want to, or delete them?
> "Hilary Cotter" wrote:

Default File Location

Anyone know where SQL Sever stores the default file location (path) ?
Registry.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
DefaultLog and DefaultData
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff Block" <jblock@.REMOVETHECAPSANDINVALIDfirstlook.biz.inval id> wrote in
message news:#zEKB#74EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Anyone know where SQL Sever stores the default file location (path) ?
>
|||Jeff Block wrote:
> Anyone know where SQL Sever stores the default file location (path) ?
C:\Program Files\Microsoft SQL Server\MSSQL\Data
David Gugick
Imceda Software
www.imceda.com

Default File Location

Anyone know where SQL Sever stores the default file location (path) ?Registry.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
DefaultLog and DefaultData
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff Block" <jblock@.REMOVETHECAPSANDINVALIDfirstlook.biz.invalid> wrote in
message news:#zEKB#74EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Anyone know where SQL Sever stores the default file location (path) ?
>|||Jeff Block wrote:
> Anyone know where SQL Sever stores the default file location (path) ?
C:\Program Files\Microsoft SQL Server\MSSQL\Data
--
David Gugick
Imceda Software
www.imceda.com

Default Drive for new DBs

Is there a way to change the default location, including drive, of a SQL
isntall that was initially installed to the C drive?Space Junk wrote:
> Is there a way to change the default location, including drive, of a
> SQL isntall that was initially installed to the C drive?
By "default location", do you mean where the executables are installed?
Or do you mean where the data and log files are installed?
--
David Gugick
Imceda Software
www.imceda.com|||I mean where data and logs are installed by default. Sorry I thought I said
that.
Example, adding a bigger HDD to a system and needing new data bases to
default to the new drive.
"David Gugick" wrote:
> Space Junk wrote:
> > Is there a way to change the default location, including drive, of a
> > SQL isntall that was initially installed to the C drive?
> By "default location", do you mean where the executables are installed?
> Or do you mean where the data and log files are installed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Space Junk wrote:
> I mean where data and logs are installed by default. Sorry I thought
> I said that.
> Example, adding a bigger HDD to a system and needing new data bases to
> default to the new drive.
Just add a new data file on the database to the new drive. SQL Server
will fill the data files (or log files) proportionally. If you want to
actually move the data and log files, you can use sp_detachdb and
sp_attach_db / sp_attach_single_file_db to disconnect them and reconnect
once they are on the new drive.
--
David Gugick
Imceda Software
www.imceda.com|||You can specify the database default folders for data and log files from
Enterprise Manager under server properties --> database settings.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> Is there a way to change the default location, including drive, of a SQL
> isntall that was initially installed to the C drive?|||I know how to move a data base, and data base settings only shows the C
drive. I do not need to change the folder data bases are being stored in. I
need to change the DEFAULT drive.
It is looking like a re-installation.
"Dan Guzman" wrote:
> You can specify the database default folders for data and log files from
> Enterprise Manager under server properties --> database settings.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> > Is there a way to change the default location, including drive, of a SQL
> > isntall that was initially installed to the C drive?
>
>|||I tried a different physical HDD and then that new drive showed up under DB
Settings under DB properties. I am not sure why the other functioning drive
did not show up.
"Space Junk" wrote:
> I know how to move a data base, and data base settings only shows the C
> drive. I do not need to change the folder data bases are being stored in. I
> need to change the DEFAULT drive.
> It is looking like a re-installation.
> "Dan Guzman" wrote:
> > You can specify the database default folders for data and log files from
> > Enterprise Manager under server properties --> database settings.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> > news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> > > Is there a way to change the default location, including drive, of a SQL
> > > isntall that was initially installed to the C drive?
> >
> >
> >|||Space Junk wrote:
You can easily default new database data and log files under the
database settings tab in SQL EM. Just change the Default Data and
Default Log locations.
David Gugick
Imceda Software
www.imceda.com|||I was trying that, however me previous second HDD was not showing up in
there, only drive C. Not sure why, the drive was functioning normally. I
put a new drive in, and check again, then I had the option to choose other
than the C drive.
"David Gugick" wrote:
> Space Junk wrote:
> You can easily default new database data and log files under the
> database settings tab in SQL EM. Just change the Default Data and
> Default Log locations.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>sql

Default Drive for new DBs

Is there a way to change the default location, including drive, of a SQL
isntall that was initially installed to the C drive?
Space Junk wrote:
> Is there a way to change the default location, including drive, of a
> SQL isntall that was initially installed to the C drive?
By "default location", do you mean where the executables are installed?
Or do you mean where the data and log files are installed?
David Gugick
Imceda Software
www.imceda.com
|||I mean where data and logs are installed by default. Sorry I thought I said
that.
Example, adding a bigger HDD to a system and needing new data bases to
default to the new drive.
"David Gugick" wrote:

> Space Junk wrote:
> By "default location", do you mean where the executables are installed?
> Or do you mean where the data and log files are installed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Space Junk wrote:
> I mean where data and logs are installed by default. Sorry I thought
> I said that.
> Example, adding a bigger HDD to a system and needing new data bases to
> default to the new drive.
Just add a new data file on the database to the new drive. SQL Server
will fill the data files (or log files) proportionally. If you want to
actually move the data and log files, you can use sp_detachdb and
sp_attach_db / sp_attach_single_file_db to disconnect them and reconnect
once they are on the new drive.
David Gugick
Imceda Software
www.imceda.com
|||You can specify the database default folders for data and log files from
Enterprise Manager under server properties --> database settings.
Hope this helps.
Dan Guzman
SQL Server MVP
"Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> Is there a way to change the default location, including drive, of a SQL
> isntall that was initially installed to the C drive?
|||I know how to move a data base, and data base settings only shows the C
drive. I do not need to change the folder data bases are being stored in. I
need to change the DEFAULT drive.
It is looking like a re-installation.
"Dan Guzman" wrote:

> You can specify the database default folders for data and log files from
> Enterprise Manager under server properties --> database settings.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
>
>
|||I tried a different physical HDD and then that new drive showed up under DB
Settings under DB properties. I am not sure why the other functioning drive
did not show up.
"Space Junk" wrote:
[vbcol=seagreen]
> I know how to move a data base, and data base settings only shows the C
> drive. I do not need to change the folder data bases are being stored in. I
> need to change the DEFAULT drive.
> It is looking like a re-installation.
> "Dan Guzman" wrote:
|||Space Junk wrote:
You can easily default new database data and log files under the
database settings tab in SQL EM. Just change the Default Data and
Default Log locations.
David Gugick
Imceda Software
www.imceda.com
|||I was trying that, however me previous second HDD was not showing up in
there, only drive C. Not sure why, the drive was functioning normally. I
put a new drive in, and check again, then I had the option to choose other
than the C drive.
"David Gugick" wrote:

> Space Junk wrote:
> You can easily default new database data and log files under the
> database settings tab in SQL EM. Just change the Default Data and
> Default Log locations.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Default Drive for new DBs

Is there a way to change the default location, including drive, of a SQL
isntall that was initially installed to the C drive?Space Junk wrote:
> Is there a way to change the default location, including drive, of a
> SQL isntall that was initially installed to the C drive?
By "default location", do you mean where the executables are installed?
Or do you mean where the data and log files are installed?
David Gugick
Imceda Software
www.imceda.com|||I mean where data and logs are installed by default. Sorry I thought I said
that.
Example, adding a bigger HDD to a system and needing new data bases to
default to the new drive.
"David Gugick" wrote:

> Space Junk wrote:
> By "default location", do you mean where the executables are installed?
> Or do you mean where the data and log files are installed?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Space Junk wrote:
> I mean where data and logs are installed by default. Sorry I thought
> I said that.
> Example, adding a bigger HDD to a system and needing new data bases to
> default to the new drive.
Just add a new data file on the database to the new drive. SQL Server
will fill the data files (or log files) proportionally. If you want to
actually move the data and log files, you can use sp_detachdb and
sp_attach_db / sp_attach_single_file_db to disconnect them and reconnect
once they are on the new drive.
David Gugick
Imceda Software
www.imceda.com|||You can specify the database default folders for data and log files from
Enterprise Manager under server properties --> database settings.
Hope this helps.
Dan Guzman
SQL Server MVP
"Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
> Is there a way to change the default location, including drive, of a SQL
> isntall that was initially installed to the C drive?|||I know how to move a data base, and data base settings only shows the C
drive. I do not need to change the folder data bases are being stored in.
I
need to change the DEFAULT drive.
It is looking like a re-installation.
"Dan Guzman" wrote:

> You can specify the database default folders for data and log files from
> Enterprise Manager under server properties --> database settings.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Space Junk" <SpaceJunk@.discussions.microsoft.com> wrote in message
> news:A66E48D4-8B2B-4948-B349-F2BDA2EFA09A@.microsoft.com...
>
>|||I tried a different physical HDD and then that new drive showed up under DB
Settings under DB properties. I am not sure why the other functioning drive
did not show up.
"Space Junk" wrote:
[vbcol=seagreen]
> I know how to move a data base, and data base settings only shows the C
> drive. I do not need to change the folder data bases are being stored in.
I
> need to change the DEFAULT drive.
> It is looking like a re-installation.
> "Dan Guzman" wrote:
>|||Space Junk wrote:
You can easily default new database data and log files under the
database settings tab in SQL EM. Just change the Default Data and
Default Log locations.
David Gugick
Imceda Software
www.imceda.com|||I was trying that, however me previous second HDD was not showing up in
there, only drive C. Not sure why, the drive was functioning normally. I
put a new drive in, and check again, then I had the option to choose other
than the C drive.
"David Gugick" wrote:

> Space Junk wrote:
> You can easily default new database data and log files under the
> database settings tab in SQL EM. Just change the Default Data and
> Default Log locations.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Default database location?

I have the default location for Data setup to be G:\Microsoft SQL
Server\Data (Database Settings under server properties).
But, when I invoke "Copy Database" from Management, it places the data on
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
Why doesn't the Copy Database Wizard respect my setting?
OlavOlav
It probably takes it from a model database
SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
WHERE [name] = 'model'
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||I'm confused!
Why are there multiple places to configure the same kind of option?
Olav
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUaZJ8WXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Olav
> It probably takes it from a model database
> SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
> WHERE [name] = 'model'
>
>
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>|||You can view the default directory with the following :-
exec master..xp_regread
'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\Setup','SQLDataRoot'
It can be changed with EnterPrise Manager, but... this change may not stick
as you need sufficient permsion to write to registry. If you find it isn't
saving you will need to logon to the box as administrator.
HTH. Ryan
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||This is the result I got from the query on that machine:
RegQueryValueEx() returned error 2, 'The system cannot find the file
specified.'
Msg 22001, Level 1, State 1
(0 row(s) affected)
What does this indicate?
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...

> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>|||I checked in the Registry and it shows the following value for that key:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
In SQL Server Management Studio it shows:
G:\Microsoft SQL Server\Data
I'm still confused!
Why are there two different values for the same thing stored?
I'm running Management Studio logged in as an Administrator, so there should
be no problem writing to the registry.
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>

Default database location?

I have the default location for Data setup to be G:\Microsoft SQL
Server\Data (Database Settings under server properties).
But, when I invoke "Copy Database" from Management, it places the data on
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
Why doesn't the Copy Database Wizard respect my setting?
OlavOlav
It probably takes it from a model database
SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
WHERE [name] = 'model'
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||I'm confused!
Why are there multiple places to configure the same kind of option?
Olav
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUaZJ8WXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Olav
> It probably takes it from a model database
> SELECT REPLACE(filename, 'model.mdf', '') FROM master..SysDatabases
> WHERE [name] = 'model'
>
>
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>>I have the default location for Data setup to be G:\Microsoft SQL
>>Server\Data (Database Settings under server properties).
>> But, when I invoke "Copy Database" from Management, it places the data on
>> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
>> Why doesn't the Copy Database Wizard respect my setting?
>> Olav
>|||You can view the default directory with the following :-
exec master..xp_regread
'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLDataRoot'
It can be changed with EnterPrise Manager, but... this change may not stick
as you need sufficient permsion to write to registry. If you find it isn't
saving you will need to logon to the box as administrator.
--
HTH. Ryan
"Olav" <x@.y.com> wrote in message
news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>I have the default location for Data setup to be G:\Microsoft SQL
>Server\Data (Database Settings under server properties).
> But, when I invoke "Copy Database" from Management, it places the data on
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
> Why doesn't the Copy Database Wizard respect my setting?
> Olav
>|||This is the result I got from the query on that machine:
RegQueryValueEx() returned error 2, 'The system cannot find the file
specified.'
Msg 22001, Level 1, State 1
(0 row(s) affected)
What does this indicate?
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>>I have the default location for Data setup to be G:\Microsoft SQL
>>Server\Data (Database Settings under server properties).
>> But, when I invoke "Copy Database" from Management, it places the data on
>> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
>> Why doesn't the Copy Database Wizard respect my setting?
>> Olav
>|||I checked in the Registry and it shows the following value for that key:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
In SQL Server Management Studio it shows:
G:\Microsoft SQL Server\Data
I'm still confused!
Why are there two different values for the same thing stored?
I'm running Management Studio logged in as an Administrator, so there should
be no problem writing to the registry.
Olav
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uDgpjEXXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> You can view the default directory with the following :-
> exec master..xp_regread
> 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Setup','SQLDataRoot'
> It can be changed with EnterPrise Manager, but... this change may not
> stick as you need sufficient permsion to write to registry. If you find it
> isn't saving you will need to logon to the box as administrator.
> --
> HTH. Ryan
>
> "Olav" <x@.y.com> wrote in message
> news:%23xMVx0WXGHA.1348@.TK2MSFTNGP05.phx.gbl...
>>I have the default location for Data setup to be G:\Microsoft SQL
>>Server\Data (Database Settings under server properties).
>> But, when I invoke "Copy Database" from Management, it places the data on
>> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.
>> Why doesn't the Copy Database Wizard respect my setting?
>> Olav
>

Default data and log file location

How can I determine the default data and log file locations of a Smo.Server instance?

For example, my server's default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Hi,

you can use the following code to get the registry values, but the documentation is not right at this point as I won′t bring back any string if not set in the registry:

Server s = new Server(".");

Console.WriteLine(s.Settings.DefaultFile);

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks - thats what I was looking for. When the registry value is not set I will just grab the file location of the master db:

'open the master database

Dim master As Database = New Database(_server, "master")

master.Refresh()

'get the location of its mdf file

Dim masterMdfFilename = master.FileGroups.Item(0).Files.Item(0).FileName

Console.WriteLine(System.IO.Path.GetDirectoryName(masterMdfFilename))

Default data and log file location

How can I determine the default data and log file locations of a Smo.Server instance?

For example, my server's default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Hi,

you can use the following code to get the registry values, but the documentation is not right at this point as I won′t bring back any string if not set in the registry:

Server s = new Server(".");

Console.WriteLine(s.Settings.DefaultFile);

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks - thats what I was looking for. When the registry value is not set I will just grab the file location of the master db:

'open the master database

Dim master As Database = New Database(_server, "master")

master.Refresh()

'get the location of its mdf file

Dim masterMdfFilename = master.FileGroups.Item(0).Files.Item(0).FileName

Console.WriteLine(System.IO.Path.GetDirectoryName(masterMdfFilename))

Default Backup Location

Hi

I am almost ashamed to ask but I cannot find the setting

Is there a way to set the default backup location in sql 2005? I dont want to change the folder every time I specify a new media.

Thanks

Alex

Hi Alex,

I don't think you can do it with the SSMS. Here's a registry key you can modify, however:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory

Regards,
Jake Marx

|||

I suggest that you do not use the default setting in SQL Server (even if you change it) as it makes administration and maintenance more of a headache for (us) DBAs

Come up with a standard approach suitable for your organization on doing backups of databases

Default Backup Location

Where do I setup the default backup folder location on SQL Server 2005?
Thanks,
Ademar.I didn't find a way to set it with the GUI, but you can always modify the registry. On my machine, I
have a path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
And there is a key named BackupDirectory
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ademar" <ademar@.nospam.com> wrote in message news:OZ9D5YvbGHA.3344@.TK2MSFTNGP03.phx.gbl...
> Where do I setup the default backup folder location on SQL Server 2005?
> Thanks,
> Ademar.
>

Default Backup Location

How do I change the default backup location in SQL Server
2000?
Thanks in advance.From bol under backup
Note If a relative path name is entered for a backup to
disk, the backup file is placed in the default backup
directory. This directory is set during installation and
stored in the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServe
r.|||I would not recommend the use of the default backup folder. It's better to
be explicit about where you want to backup your database/log. Personally, I
don't use it and have not found it of any use. I'd be curious about any
significant value anybody may find it offers.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Evan" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9001c39f4b$17bc0ef0$a001280a@.phx.gbl...
> How do I change the default backup location in SQL Server
> 2000?
> Thanks in advance.

Default Backup Location

Where do I setup the default backup folder location on SQL Server 2005?
Thanks,
Ademar.I didn't find a way to set it with the GUI, but you can always modify the re
gistry. On my machine, I
have a path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSSQL.1\MSSQLServ
er
And there is a key named BackupDirectory
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ademar" <ademar@.nospam.com> wrote in message news:OZ9D5YvbGHA.3344@.TK2MSFTNGP03.phx.gbl...[
vbcol=seagreen]
> Where do I setup the default backup folder location on SQL Server 2005?
> Thanks,
> Ademar.
>[/vbcol]sql

Deault location for RSTempFiles

I currently have RS installed on my C: drive. Unfortunately, I did not
realize how much free space I was going to need for temp files, so now I'm
running out of space on my C: drive and RS restarts several times during a
schedule. Is it possible for me to point RSTempFiles to another drive
letter? I've searched for configuration files and the registry, but can't
find anything.
ThanksYes it is possible. It is documented in the SP1 readme. You can find this
here:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
Just search for RSTempFiles.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"troark" <t.roark@.excite.com.n0sp@.m> wrote in message
news:ei0jQFJpEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I currently have RS installed on my C: drive. Unfortunately, I did not
> realize how much free space I was going to need for temp files, so now I'm
> running out of space on my C: drive and RS restarts several times during a
> schedule. Is it possible for me to point RSTempFiles to another drive
> letter? I've searched for configuration files and the registry, but can't
> find anything.
> Thanks
>|||Great. Worked like a charm!
Thanks
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eEtgK8JpEHA.644@.tk2msftngp13.phx.gbl...
> Yes it is possible. It is documented in the SP1 readme. You can find
this
> here:
>
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79dd
aa510/SP1Readme_EN.htm
> Just search for RSTempFiles.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "troark" <t.roark@.excite.com.n0sp@.m> wrote in message
> news:ei0jQFJpEHA.3896@.TK2MSFTNGP15.phx.gbl...
> > I currently have RS installed on my C: drive. Unfortunately, I did not
> > realize how much free space I was going to need for temp files, so now
I'm
> > running out of space on my C: drive and RS restarts several times during
a
> > schedule. Is it possible for me to point RSTempFiles to another drive
> > letter? I've searched for configuration files and the registry, but
can't
> > find anything.
> >
> > Thanks
> >
> >
>