New database file locations

Hi. Could anyone suggest why, whenever a third-party application that create
s
and uses its own SQL Server database, it creates the physical files in a
location that is not the default? This doesn't seem to be an app-specific
issue as different apps have done the same thing.
As per the general advise data and log files on my system are held on
separate volumes and when I create a new database myself the physical files
are created in their default locations. However this is not the case with
third-party apps. They seem to be creating the files in the same volume. How
do I change this behavior. I had thought that the settings of the model
database would determine where those files would go?
YaHozna.Hello,
I feel that your third party application creates the database specifying the
path. In this case the database will be created based on the path specified
in CREATE DATABASE command. Incase if the path is not configurab,e in
application installlation, you could detach the database, copy the files
to required drive and folder and attach the database. See SP_DETACH_DB and
SP_ATTACH_DB in books online..
Thanks
Hari
"YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
news:A74E91B5-7CDC-4143-ADFE-EF814499DE0B@.microsoft.com...
> Hi. Could anyone suggest why, whenever a third-party application that
> creates
> and uses its own SQL Server database, it creates the physical files in a
> location that is not the default? This doesn't seem to be an app-specific
> issue as different apps have done the same thing.
> As per the general advise data and log files on my system are held on
> separate volumes and when I create a new database myself the physical
> files
> are created in their default locations. However this is not the case with
> third-party apps. They seem to be creating the files in the same volume.
> How
> do I change this behavior. I had thought that the settings of the model
> database would determine where those files would go?
> YaHozna.|||There is a server setting that determines the default location for the data
and log files, not hte master db. Check the properties of the server in
Enterprise Manager and you will find them.
Andrew J. Kelly SQL MVP
"YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
news:A74E91B5-7CDC-4143-ADFE-EF814499DE0B@.microsoft.com...
> Hi. Could anyone suggest why, whenever a third-party application that
> creates
> and uses its own SQL Server database, it creates the physical files in a
> location that is not the default? This doesn't seem to be an app-specific
> issue as different apps have done the same thing.
> As per the general advise data and log files on my system are held on
> separate volumes and when I create a new database myself the physical
> files
> are created in their default locations. However this is not the case with
> third-party apps. They seem to be creating the files in the same volume.
> How
> do I change this behavior. I had thought that the settings of the model
> database would determine where those files would go?
> YaHozna.|||Many 3rd party applications do this, unfortunately.
When CREATE DATABASE is used without options, the defaults are
taken from the model database, but it is perfectly possible to
override the defaults by using the NAME and FILENAME options with
the CREATE DATABASE statement.
You can change the location of the files after the installation.
From http://sql.veranoest.net/sql_faq.htm
Q. How can I move my database files to another disk in my SQL
Server?
A. An easy way to move user database files to a different location
is by detaching, moving and attaching the user database.
This can be done in SQL Server Management Studio so you don't have
to type any code.
Here are the steps:
1. Start SQL Server Management Studio
2. Expand the server instance, expand Databases
3. right-click the database you want to move, and choose
"Properties"
4. In the Properties window, choose "Files" and write down the
current file paths. Click "Cancel"
5. right-click the database again, and choose "Tasks - Detach..."
6. click "OK" in the next window
7. Use Windows Explorer to move the data and log files (.mdf and
.ldf) to the new location
8. right-click Databases, and choose "Attach..."
9. In the "Attach databases" window, click "Add"
10.In the "Locate database files" window, browse to the new
location and select the .mdf file. Click "OK"
11.In the details pane, verify that the new location is listed for
both the .mdf and the .ldf file. Click "OK"
12.In SQL Server Management Studio, choose "View - Refresh" and
verify that your database is listed again under Databases
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 Transact-SQL.
Details are here:
221465 - INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?kbid=221465
If you want to move the system databases, be sure to read these
articles:
Moving the SQL 2005 System Databases
http://www.sqlservercentral.com/col...coboni/2605.asp
224071 - How to move SQL Server databases to a new location by
using Detach and Attach functions in SQL Server
http://support.microsoft.com/?kbid=224071
________________________________________
_________________
Vera Noest
MCSE, CCEA, Microsoft MVP - Terminal Server
SQL troubleshooting: http://sql.veranoest.net
___ please respond in newsgroup, NOT by private email ___
examnotes <YaHozna@.discussions.microsoft.com> wrote
on 11 feb 2007 in microsoft.public.sqlserver.server:

> Hi. Could anyone suggest why, whenever a third-party application
> that creates and uses its own SQL Server database, it creates
> the physical files in a location that is not the default? This
> doesn't seem to be an app-specific issue as different apps have
> done the same thing.
> As per the general advise data and log files on my system are
> held on separate volumes and when I create a new database myself
> the physical files are created in their default locations.
> However this is not the case with third-party apps. They seem to
> be creating the files in the same volume. How do I change this
> behavior. I had thought that the settings of the model database
> would determine where those files would go?
> YaHozna.|||> When CREATE DATABASE is used without options, the defaults are
> taken from the model database, but it is perfectly possible to
> override the defaults by using the NAME and FILENAME options with
> the CREATE DATABASE statement.
Although the model database is used as a template for new databases, the
default file locations are not determined by the model database. As Andrew
mentioned, default file locations can be specified separately via server
properties.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vera Noest" <vera.noest@.remove-this.hem.utfors.se> wrote in message
news:Xns98D4A3986E7CDveranoesthemutforss
e@.207.46.248.16...[vbcol=seagreen]
> Many 3rd party applications do this, unfortunately.
> When CREATE DATABASE is used without options, the defaults are
> taken from the model database, but it is perfectly possible to
> override the defaults by using the NAME and FILENAME options with
> the CREATE DATABASE statement.
> You can change the location of the files after the installation.
> From http://sql.veranoest.net/sql_faq.htm
> Q. How can I move my database files to another disk in my SQL
> Server?
> A. An easy way to move user database files to a different location
> is by detaching, moving and attaching the user database.
> This can be done in SQL Server Management Studio so you don't have
> to type any code.
> Here are the steps:
> 1. Start SQL Server Management Studio
> 2. Expand the server instance, expand Databases
> 3. right-click the database you want to move, and choose
> "Properties"
> 4. In the Properties window, choose "Files" and write down the
> current file paths. Click "Cancel"
> 5. right-click the database again, and choose "Tasks - Detach..."
> 6. click "OK" in the next window
> 7. Use Windows Explorer to move the data and log files (.mdf and
> .ldf) to the new location
> 8. right-click Databases, and choose "Attach..."
> 9. In the "Attach databases" window, click "Add"
> 10.In the "Locate database files" window, browse to the new
> location and select the .mdf file. Click "OK"
> 11.In the details pane, verify that the new location is listed for
> both the .mdf and the .ldf file. Click "OK"
> 12.In SQL Server Management Studio, choose "View - Refresh" and
> verify that your database is listed again under Databases
> 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 Transact-SQL.
> Details are here:
> 221465 - INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/?kbid=221465
> If you want to move the system databases, be sure to read these
> articles:
> Moving the SQL 2005 System Databases
> http://www.sqlservercentral.com/col...coboni/2605.asp
> 224071 - How to move SQL Server databases to a new location by
> using Detach and Attach functions in SQL Server
> http://support.microsoft.com/?kbid=224071
> ________________________________________
_________________
> Vera Noest
> MCSE, CCEA, Microsoft MVP - Terminal Server
> SQL troubleshooting: http://sql.veranoest.net
> ___ please respond in newsgroup, NOT by private email ___
> examnotes <YaHozna@.discussions.microsoft.com> wrote
> on 11 feb 2007 in microsoft.public.sqlserver.server:
>|||Applications should never care where the database files are placed. That's a
DBA's job. Ideally, applications shouldn't be creating databases (I know the
y
do). They should use databases created for them. In cases they do create
databases in wrong locations, you should plan to move the files to the
correct locations.
But then I've seen apps that create databases on the fly as part of the
normal production process. Ouch!
Linchi
"YaHozna" wrote:

> Hi. Could anyone suggest why, whenever a third-party application that crea
tes
> and uses its own SQL Server database, it creates the physical files in a
> location that is not the default? This doesn't seem to be an app-specific
> issue as different apps have done the same thing.
> As per the general advise data and log files on my system are held on
> separate volumes and when I create a new database myself the physical file
s
> are created in their default locations. However this is not the case with
> third-party apps. They seem to be creating the files in the same volume. H
ow
> do I change this behavior. I had thought that the settings of the model
> database would determine where those files would go?
> YaHozna.|||Thanks Andrew and to the others who contributed. I did double-check the
Server settings and these are as they should be: data files are created on
volume E of a RAID array and Log files on volume D. Tempdb is also on volume
E and it was in that location - the folder in which the tempdb files exist -
that all files for new databases created by third-party apps seemed to be
being created for some reason.
No problem moving the files. Just wondered why this was happening.
Regards,
YaHozna
"Andrew J. Kelly" wrote:

> There is a server setting that determines the default location for the dat
a
> and log files, not hte master db. Check the properties of the server in
> Enterprise Manager and you will find them.
> --
> Andrew J. Kelly SQL MVP
> "YaHozna" <YaHozna@.discussions.microsoft.com> wrote in message
> news:A74E91B5-7CDC-4143-ADFE-EF814499DE0B@.microsoft.com...
>
>