Default Database Path

Hi,
Is there a way to determine where is the default path for
new database in the servder?
Thanks,You can look at your model database for this info.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Shai Goldberg" <gshai@.shamir.co.il> wrote in message
news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
> Hi,
> Is there a way to determine where is the default path for
> new database in the servder?
> Thanks,|||The default location for database files is not determined by the model
database. For a SQL 2000 default instance, the default folders are stored
in the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultLog
These can be viewed/changed with Enterprise Manager (server properties -->
database settings).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:%239F9m%23EvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> You can look at your model database for this info.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Shai Goldberg" <gshai@.shamir.co.il> wrote in message
> news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
> > Hi,
> >
> > Is there a way to determine where is the default path for
> > new database in the servder?
> >
> > Thanks,
>|||Dan, haven't had time to test this but I thought if you changed the location
for your data and log files in the model all new databases would create
their data and log files in the new area?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OeIfGIGvDHA.1596@.TK2MSFTNGP10.phx.gbl...
> The default location for database files is not determined by the model
> database. For a SQL 2000 default instance, the default folders are stored
> in the registry under:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultData
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultLog
> These can be viewed/changed with Enterprise Manager (server properties -->
> database settings).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%239F9m%23EvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > You can look at your model database for this info.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Shai Goldberg" <gshai@.shamir.co.il> wrote in message
> > news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there a way to determine where is the default path for
> > > new database in the servder?
> > >
> > > Thanks,
> >
> >
>|||Just tested it, you are right, of course, changing the location for the
model database mdf and ldf does not affect any databases created after.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OeIfGIGvDHA.1596@.TK2MSFTNGP10.phx.gbl...
> The default location for database files is not determined by the model
> database. For a SQL 2000 default instance, the default folders are stored
> in the registry under:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultData
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultLog
> These can be viewed/changed with Enterprise Manager (server properties -->
> database settings).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%239F9m%23EvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > You can look at your model database for this info.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Shai Goldberg" <gshai@.shamir.co.il> wrote in message
> > news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there a way to determine where is the default path for
> > > new database in the servder?
> > >
> > > Thanks,
> >
> >
>|||Been there, done that :-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eJ%233VVHvDHA.3224@.tk2msftngp13.phx.gbl...
> Just tested it, you are right, of course, changing the location for the
> model database mdf and ldf does not affect any databases created after.
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OeIfGIGvDHA.1596@.TK2MSFTNGP10.phx.gbl...
> > The default location for database files is not determined by the model
> > database. For a SQL 2000 default instance, the default folders are
stored
> > in the registry under:
> >
> >
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultData
> > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultLog
> >
> > These can be viewed/changed with Enterprise Manager (server
properties -->
> > database settings).
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:%239F9m%23EvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > > You can look at your model database for this info.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Shai Goldberg" <gshai@.shamir.co.il> wrote in message
> > > news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
> > > > Hi,
> > > >
> > > > Is there a way to determine where is the default path for
> > > > new database in the servder?
> > > >
> > > > Thanks,
> > >
> > >
> >
> >
>|||Dan (And all),
Thanks for your replies, especially Dan's reply.
It gave me the information I have needed.
Not I have also found that for for instances of SQL
Server2000 the location is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\[Instance Name]\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\[Instance Name]\MSSQLServer\DefaultLog
I need these location when I'm installing a database
(using Windows Installer) and I want to give the user the
ability to insert the databases into his Default database
location, which is why I can't use Enterprise Manager's
Server Settings.
Regrads,
Shai
>--Original Message--
>The default location for database files is not
determined by the model
>database. For a SQL 2000 default instance, the default
folders are stored
>in the registry under:
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLSe
rver\DefaultData
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLSe
rver\DefaultLog
>These can be viewed/changed with Enterprise Manager
(server properties -->
>database settings).
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in
message
>news:%239F9m%23EvDHA.1996@.TK2MSFTNGP12.phx.gbl...
>> You can look at your model database for this info.
>> HTH
>> --
>> Ray Higdon MCSE, MCDBA, CCNA
>> --
>> "Shai Goldberg" <gshai@.shamir.co.il> wrote in message
>> news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
>> > Hi,
>> >
>> > Is there a way to determine where is the default
path for
>> > new database in the servder?
>> >
>> > Thanks,
>>
>
>.
>|||You can simply omit the filespec to create the database in the default
location. The other file properties (size, growth, etc.) will be determined
my the model database. For example:
CREATE DATABASE MyDatabase
If you allow the user to override the defaults during the install, you can
change the CREATE DATABASE statement within your custom action according to
the items entered by the user in your installer dialogs:
CREATE DATABASE MyDatabase
ON(
NAME='MyDatabase',
FILENAME='C:\Program Files\MyCompany\MyProduct\SqlData\MyDatabase.mdf',
SIZE=10MB,
FILEGROWTH=1MB,
MAXSIZE=UNLIMITED
)
LOG ON(
NAME='MyDatabase_Log',
FILENAME='C:\Program Files\MyCompany\MyProduct\SqlLog\MyDatabase.mdf',
SIZE=5MB,
FILEGROWTH=10%,
MAXSIZE=UNLIMITED
)
FWIW, we create the database in our installer only if the database doesn't
exist. Example below. This allows the DBA to create an empty database
before the install and have full control over all database properties and we
don't need to duplicate Enterprise Manager GUI functionality in the
installer. However, if your install is done on a client machine by
non-technical users, the above approach is probably better.
IF DB_ID('MyDatabase') IS NULL
CREATE DATABASE MyDatabase
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Shai Goldberg" <gshai@.shamir.co.il> wrote in message
news:e7f401c3bc96$6f741260$a601280a@.phx.gbl...
> Dan (And all),
> Thanks for your replies, especially Dan's reply.
> It gave me the information I have needed.
> Not I have also found that for for instances of SQL
> Server2000 the location is:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
> Server\[Instance Name]\MSSQLServer\DefaultData
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
> Server\[Instance Name]\MSSQLServer\DefaultLog
> I need these location when I'm installing a database
> (using Windows Installer) and I want to give the user the
> ability to insert the databases into his Default database
> location, which is why I can't use Enterprise Manager's
> Server Settings.
> Regrads,
> Shai
> >--Original Message--
> >The default location for database files is not
> determined by the model
> >database. For a SQL 2000 default instance, the default
> folders are stored
> >in the registry under:
> >
> >HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLSe
> rver\DefaultData
> >HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLSe
> rver\DefaultLog
> >
> >These can be viewed/changed with Enterprise Manager
> (server properties -->
> >database settings).
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in
> message
> >news:%239F9m%23EvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> >> You can look at your model database for this info.
> >>
> >> HTH
> >>
> >> --
> >> Ray Higdon MCSE, MCDBA, CCNA
> >> --
> >> "Shai Goldberg" <gshai@.shamir.co.il> wrote in message
> >> news:018601c3bc3d$ccf151e0$a401280a@.phx.gbl...
> >> > Hi,
> >> >
> >> > Is there a way to determine where is the default
> path for
> >> > new database in the servder?
> >> >
> >> > Thanks,
> >>
> >>
> >
> >
> >.
> >|||BTW, the log file name in my example should have been 'MyDatabase_Log.ldf'.
--
Hope this helps.
Dan Guzman
SQL Server MVP|||Dan,
I'm, also, using WfWI and I'm using Professional edition
with version 5.1, but I have a database which is filled
with data and is of size of about 90MB so I don't just
create it because the SQL script files are two large.
Now to install the database I can do one of two things:
1. Restore the database from the installation
2. Install the database files into the computer and then
attache them as a database.
Both methods require that I'll know the location of
database file, at least as along as I know.
What I'm currently doing is installing the database files
and then attaching them into the SQL Server.
Currently I'm installing the database files in the
installation path and what I wanted was to let the
installation to insert the database file into the default
location of the SQL Server, which is why I was looking
for the registry key that gived the default location of
the database files.
This idea is not good anyhow due to the fact that the
user gives the Server Information only after he chooses
to install the database and before knowing the Server and
Instance name (if exist) I can't know the default
database paths but the user can select the directory only
with selecting to install the databases and therefore I
can't get the default databse paths location.
If you have any idea I'll be happy to hear.
Regards,
Shai
>--Original Message--
>You can simply omit the filespec to create the database
in the default
>location. The other file properties (size, growth,
etc.) will be determined
>my the model database. For example:
> CREATE DATABASE MyDatabase
>If you allow the user to override the defaults during
the install, you can
>change the CREATE DATABASE statement within your custom
action according to
>the items entered by the user in your installer dialogs:
>CREATE DATABASE MyDatabase
>ON(
> NAME='MyDatabase',
> FILENAME='C:\Program
Files\MyCompany\MyProduct\SqlData\MyDatabase.mdf',
> SIZE=10MB,
> FILEGROWTH=1MB,
> MAXSIZE=UNLIMITED
> )
>LOG ON(
> NAME='MyDatabase_Log',
> FILENAME='C:\Program
Files\MyCompany\MyProduct\SqlLog\MyDatabase.mdf',
> SIZE=5MB,
> FILEGROWTH=10%,
> MAXSIZE=UNLIMITED
> )
>FWIW, we create the database in our installer only if
the database doesn't
>exist. Example below. This allows the DBA to create an
empty database
>before the install and have full control over all
database properties and we
>don't need to duplicate Enterprise Manager GUI
functionality in the
>installer. However, if your install is done on a client
machine by
>non-technical users, the above approach is probably
better.
> IF DB_ID('MyDatabase') IS NULL
> CREATE DATABASE MyDatabase
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>|||I assume your installation is run on the database server so you need to know
at least the instance name before you can determine the default file
locations from the registry.
Personally, I would allow the user to specify the desired folders for the
data and log files (perhaps defaulting to your install folder) and ignore
the default database file locations altogether. This will provide all the
flexibility needed for file placement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Shai Goldberg" <gshai@.shamir.co.il> wrote in message
news:062b01c3bcd8$14437970$a301280a@.phx.gbl...
> Dan,
> I'm, also, using WfWI and I'm using Professional edition
> with version 5.1, but I have a database which is filled
> with data and is of size of about 90MB so I don't just
> create it because the SQL script files are two large.
> Now to install the database I can do one of two things:
> 1. Restore the database from the installation
> 2. Install the database files into the computer and then
> attache them as a database.
> Both methods require that I'll know the location of
> database file, at least as along as I know.
> What I'm currently doing is installing the database files
> and then attaching them into the SQL Server.
> Currently I'm installing the database files in the
> installation path and what I wanted was to let the
> installation to insert the database file into the default
> location of the SQL Server, which is why I was looking
> for the registry key that gived the default location of
> the database files.
> This idea is not good anyhow due to the fact that the
> user gives the Server Information only after he chooses
> to install the database and before knowing the Server and
> Instance name (if exist) I can't know the default
> database paths but the user can select the directory only
> with selecting to install the databases and therefore I
> can't get the default databse paths location.
> If you have any idea I'll be happy to hear.
> Regards,
> Shai
> >--Original Message--
> >You can simply omit the filespec to create the database
> in the default
> >location. The other file properties (size, growth,
> etc.) will be determined
> >my the model database. For example:
> >
> > CREATE DATABASE MyDatabase
> >
> >If you allow the user to override the defaults during
> the install, you can
> >change the CREATE DATABASE statement within your custom
> action according to
> >the items entered by the user in your installer dialogs:
> >
> >CREATE DATABASE MyDatabase
> >ON(
> > NAME='MyDatabase',
> > FILENAME='C:\Program
> Files\MyCompany\MyProduct\SqlData\MyDatabase.mdf',
> > SIZE=10MB,
> > FILEGROWTH=1MB,
> > MAXSIZE=UNLIMITED
> > )
> >LOG ON(
> > NAME='MyDatabase_Log',
> > FILENAME='C:\Program
> Files\MyCompany\MyProduct\SqlLog\MyDatabase.mdf',
> > SIZE=5MB,
> > FILEGROWTH=10%,
> > MAXSIZE=UNLIMITED
> > )
> >
> >FWIW, we create the database in our installer only if
> the database doesn't
> >exist. Example below. This allows the DBA to create an
> empty database
> >before the install and have full control over all
> database properties and we
> >don't need to duplicate Enterprise Manager GUI
> functionality in the
> >installer. However, if your install is done on a client
> machine by
> >non-technical users, the above approach is probably
> better.
> >
> > IF DB_ID('MyDatabase') IS NULL
> > CREATE DATABASE MyDatabase
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >
>|||Dan,
Currently I'm not giving the user any option on where to
install the databses, the files will be installed under
the installation path, which has a default location but
the user can change.
Maybe, I should give the user the ability to chnage the
location of the database files but I didn't do it.
Anyhow, thanks for your replies and your time.
Shai.
>--Original Message--
>I assume your installation is run on the database server
so you need to know
>at least the instance name before you can determine the
default file
>locations from the registry.
>Personally, I would allow the user to specify the
desired folders for the
>data and log files (perhaps defaulting to your install
folder) and ignore
>the default database file locations altogether. This
will provide all the
>flexibility needed for file placement.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>|||That is a bad choice in many cases, there are a great many of us that only
keep the system DBs in the original default path and most do this on the OS
volume with the SQL binaries
Then all other database and logs go on other storage arrays
The OS partition is often quite small compared to other provided storage
areas
most people will leave the defalut at C:\......., which is often a bad
place to put your app DB and logs
--
By keeping the OS and SQL binaries and system tables on the OS partition --
SQL will still start if you have issues with other storage and need to move
of rebuild to alternate areas from backups, etc.
There is often considerable hours of work in the jobs, DTS packages stored
in the system table that also needs to have some consideration for recovery,
upgrade of new equipment etc..
"Shai Goldberg" <gshai@.shamir.co.il> wrote in message
news:031f01c3bcde$b5f0bac0$a001280a@.phx.gbl...
> Dan,
> Currently I'm not giving the user any option on where to
> install the databses, the files will be installed under
> the installation path, which has a default location but
> the user can change.
> Maybe, I should give the user the ability to chnage the
> location of the database files but I didn't do it.
> Anyhow, thanks for your replies and your time.
> Shai.
> >--Original Message--
> >I assume your installation is run on the database server
> so you need to know
> >at least the instance name before you can determine the
> default file
> >locations from the registry.
> >
> >Personally, I would allow the user to specify the
> desired folders for the
> >data and log files (perhaps defaulting to your install
> folder) and ignore
> >the default database file locations altogether. This
> will provide all the
> >flexibility needed for file placement.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
>|||Hi, Shai
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
As my understanding of your question, you want the user to install other
instances on your computer and during the installation process, you want to
import one exist database into the new instances automatically. You do not
want the user to restore from the backup or attach the exist database to
new instance after the installation and looking for some way to do this
during the installation, right? If I misunderstood, feel free to let me
know.
From my experience, this is possible to do it in installation when the
location of the existed database is fixed. You can change some setup script
to do some additional work during the installation. However, you will
encounter high risk in doing so. I do not recommend you to do that. It is
not supported by Microsoft Corporation to modify any setup script even if
some one recommend you to do so.
You can know the directory the new instance is installed only after the
installation. So, I think the proper way to do it is just as you mentioned.
The existed database should be place in a specific directory, a detached
file or a backup file or a database in the default instance. Then when a
new instance is installed, users can choose the directory they want their
instances to install in. By execute "sp_helpdb master", you can get the
install location of that instance, then you can import the exist database
into the new instance database. All this work can be written in a script
and after a new instance is installed, execute this script to import.
I hope I have answered your question and if you still have questions,
please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi, Baisong
I'm sorry to say that you didn't understand my question.
The database and system that we are installing is large
enough that MSDE won't fit for it and therefore we are
not installing MSDE during the installation. One of our
pre-requirements is that the databse will be installed
onto a server that contains already SQL Server.
I'm getting the server name, username and password from
the user, where server name may be an instance of SQL
Server and not only a default SQL server instance. But
I'm getting this information from the user only if he
decides to install the database and I can let him choose
a directory to install the databse into only when he is
choosing to install the databse. Therefore, I can't give
the user the default location of the SQL Server, if I
don't know which server will he use...
What I'm curretly doing is installing the database files
under the installation directory and attach them as a
database into the selected SQL Server.
By saying that I change a script in the installation I
mean that I'm creating the attach script during the
installation and I can change it.
What I'm thinking now is that I can get the information
from the user (server name, user name and password) and
then get the default database directories (Data and Log)
from the user and then restore files into the default
database location instead of installing the files into
the installation directory and aqttaching them to the SQL
Server.
Regards,
>--Original Message--
>Hi, Shai
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>As my understanding of your question, you want the user
to install other
>instances on your computer and during the installation
process, you want to
>import one exist database into the new instances
automatically. You do not
>want the user to restore from the backup or attach the
exist database to
>new instance after the installation and looking for some
way to do this
>during the installation, right? If I misunderstood, feel
free to let me
>know.
>From my experience, this is possible to do it in
installation when the
>location of the existed database is fixed. You can
change some setup script
>to do some additional work during the installation.
However, you will
>encounter high risk in doing so. I do not recommend you
to do that. It is
>not supported by Microsoft Corporation to modify any
setup script even if
>some one recommend you to do so.
>You can know the directory the new instance is installed
only after the
>installation. So, I think the proper way to do it is
just as you mentioned.
>The existed database should be place in a specific
directory, a detached
>file or a backup file or a database in the default
instance. Then when a
>new instance is installed, users can choose the
directory they want their
>instances to install in. By execute "sp_helpdb master",
you can get the
>install location of that instance, then you can import
the exist database
>into the new instance database. All this work can be
written in a script
>and after a new instance is installed, execute this
script to import.
>I hope I have answered your question and if you still
have questions,
>please feel free to post new message here and I am ready
to help!
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi, Shai
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Sorry for misunderstanding in the first time. For what you mentioned in the
reply: "I can get the information from the user (server name, user name and
password) and then get the default database directories (Data and Log) from
the user and then restore files into the default database location", I
think that is possible. Since you have the server name, user name and
password, you can access to that database. Then XP_instance_regread could
be use to get the default database location.
For the data:
Xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
tData'
For the log:
Xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
tLog'
Here I will give you some sample code to get the information of the
directories of log and data, you could run it in the Query Analyzer to
check or OSQL utility:
--Data directory : dbo.fn_SQLServerDataDir()
--Log directory : dbo.fn_SQLServerLogDir()
--Install directory : dbo.fn_SQLServerInstallDir()
create function dbo.fn_SQLServerDataDir()
returns nvarchar(4000)
as
begin
declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
tData',
@.dir output, 'no_output'
if (@.dir is null)
begin
exec @.rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot'
, @.dir output, 'no_output'
select @.dir = @.dir + N'Data'
end
return @.dir
end
go
select dbo.fn_SQLServerDataDir()
go
create function dbo.fn_SQLServerLogDir()
returns nvarchar(4000)
as
begin
declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
tLog',
@.dir output, 'no_output'
if (@.dir is null)
begin
exec @.rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot'
, @.dir output, 'no_output'
select @.dir = @.dir + N'Data'
end
return @.dir
end
go
select dbo.fn_SQLServerLogDir()
go
create function dbo.fn_SQLServerInstallDir()
returns nvarchar(4000)
as
begin
declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath',
@.dir output, 'no_output' return @.dir
end
go
select dbo.fn_SQLServerInstallDir()
go
From the code, you can get the directory information. Then you can write a
simple restore script to restore files into the default database location.
Some reference would be helpful.
http://support.microsoft.com/?id=272705
INF: Default Data and Log Directory Display Blank by Default After You
Install SQL Server 2000
I hope my answer would be helpful in solving your problem. If you still
have questions, please feel free to post new message here and I am ready to
help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Wei,
I want to thank you for the script you have provided me. What nice with this
code is that this code is good for both default and instances of SQL Server.
Regards,
Shai
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:f9PPnLjvDHA.3532@.cpmsftngxa07.phx.gbl...
> Hi, Shai
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> Sorry for misunderstanding in the first time. For what you mentioned in
the
> reply: "I can get the information from the user (server name, user name
and
> password) and then get the default database directories (Data and Log)
from
> the user and then restore files into the default database location", I
> think that is possible. Since you have the server name, user name and
> password, you can access to that database. Then XP_instance_regread could
> be use to get the default database location.
> For the data:
> Xp_instance_regread
>
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
> tData'
> For the log:
> Xp_instance_regread
>
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
> tLog'
> Here I will give you some sample code to get the information of the
> directories of log and data, you could run it in the Query Analyzer to
> check or OSQL utility:
> --Data directory : dbo.fn_SQLServerDataDir()
> --Log directory : dbo.fn_SQLServerLogDir()
> --Install directory : dbo.fn_SQLServerInstallDir()
> create function dbo.fn_SQLServerDataDir()
> returns nvarchar(4000)
> as
> begin
> declare @.rc int,
> @.dir nvarchar(4000)
> exec @.rc = master.dbo.xp_instance_regread
>
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
> tData',
> @.dir output, 'no_output'
> if (@.dir is null)
> begin
> exec @.rc = master.dbo.xp_instance_regread
>
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot'
> , @.dir output, 'no_output'
> select @.dir = @.dir + N'Data'
> end
> return @.dir
> end
> go
> select dbo.fn_SQLServerDataDir()
> go
> create function dbo.fn_SQLServerLogDir()
> returns nvarchar(4000)
> as
> begin
> declare @.rc int,
> @.dir nvarchar(4000)
> exec @.rc = master.dbo.xp_instance_regread
>
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'Defaul
> tLog',
> @.dir output, 'no_output'
> if (@.dir is null)
> begin
> exec @.rc = master.dbo.xp_instance_regread
>
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot'
> , @.dir output, 'no_output'
> select @.dir = @.dir + N'Data'
> end
> return @.dir
> end
> go
> select dbo.fn_SQLServerLogDir()
> go
> create function dbo.fn_SQLServerInstallDir()
> returns nvarchar(4000)
> as
> begin
> declare @.rc int,
> @.dir nvarchar(4000)
> exec @.rc = master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath',
> @.dir output, 'no_output' return @.dir
> end
> go
> select dbo.fn_SQLServerInstallDir()
> go
> From the code, you can get the directory information. Then you can write a
> simple restore script to restore files into the default database location.
>
> Some reference would be helpful.
> http://support.microsoft.com/?id=272705
> INF: Default Data and Log Directory Display Blank by Default After You
> Install SQL Server 2000
> I hope my answer would be helpful in solving your problem. If you still
> have questions, please feel free to post new message here and I am ready
to
> help.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>sql