Hi I am new to SQL Server and have a few general
questions.
1. Can anyone suggest a good book to learn how ti use SQL
Server... I am proficient with relational databases and
SQL (Not transact)
2. I would like to create a database using SQL server as
the backend and Access as the front end. If I house all
of my Queries in the Access front end and simply join the
tables to my SQL Server tables will I still be able to
take adavantage of the improvements in processing speed
and size of the SQL Server database or will I need to
change all of my Access queries to SQL views to be able to
do this?
Thanks for your help,
ChadTo get a good understanding of all aspects of SQL Server, try "Teach
yourself Microsoft SQL Server 2000 in 21 days".
For a deeper understanding of SQL Server architecture, go for "Inside
Microsoft SQL Server 2000":
http://vyaskn.tripod.com/inside_microsoft_sql_server_2000.htm
Links to these books and others are available at:
http://vyaskn.tripod.com/sqlbooks.htm
Regarding your other question, if your data is stored in SQL Server, and
accessed via queries from Access, you should be able to take advantage of
SQL Servers speed and robustness.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Chad" <ChadLucy@.msn.com> wrote in message
news:021501c38dca$91af4980$a401280a@.phx.gbl...
Hi I am new to SQL Server and have a few general
questions.
1. Can anyone suggest a good book to learn how ti use SQL
Server... I am proficient with relational databases and
SQL (Not transact)
2. I would like to create a database using SQL server as
the backend and Access as the front end. If I house all
of my Queries in the Access front end and simply join the
tables to my SQL Server tables will I still be able to
take adavantage of the improvements in processing speed
and size of the SQL Server database or will I need to
change all of my Access queries to SQL views to be able to
do this?
Thanks for your help,
Chad|||To get the best throughput from MSAccess you need to set up the MSAccess
project correctly as a SQL Server project. If you use the older project
style and "attached" tables, JET is used behind the scenes and performance
will be much worse. (I did MSAccess work a long time ago, and when they
introduced SQL projects it was a very new concept. Perhaps my advice today
is already common knowedge.) Anyway, the new style projects pass SQL
commands directly to SQL Server, and you can access database views as well
as base tables. I believe there is even a way to "wire up" the MSAccess
forms so that insert/update/delete commands can call stored procedures on
the server; a huge feature.
James|||Hello,
I have found two books to be extremely helpful -
particularly with Transact Sql:
-SQL Server 2k Stored Procedure Programming (Sunderic &
Woodhead)
-Adv T-SQL for SQL Server 2k (Ben-Gan and Moreau)
>--Original Message--
>Hi I am new to SQL Server and have a few general
>questions.
>1. Can anyone suggest a good book to learn how ti use
SQL
>Server... I am proficient with relational databases and
>SQL (Not transact)
>2. I would like to create a database using SQL server as
>the backend and Access as the front end. If I house all
>of my Queries in the Access front end and simply join the
>tables to my SQL Server tables will I still be able to
>take adavantage of the improvements in processing speed
>and size of the SQL Server database or will I need to
>change all of my Access queries to SQL views to be able
to
>do this?
>Thanks for your help,
>Chad
>.
>sql
Showing posts with label suggest. Show all posts
Showing posts with label suggest. Show all posts
Friday, March 30, 2012
Saturday, February 25, 2012
new features added in MSRS 2005?
Hi,
Please can any one suggest me that, wht are the new features added in MSRS 2005?
The biggies are the Report Builder and ReportViewer controls. More here.Monday, February 20, 2012
New database file locations
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.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/columnists/vIacoboni/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 ___
=?Utf-8?B?WWFIb3puYQ==?= <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:Xns98D4A3986E7CDveranoesthemutforsse@.207.46.248.16...
> 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/columnists/vIacoboni/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 ___
> =?Utf-8?B?WWFIb3puYQ==?= <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.|||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 they
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 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.|||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 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.
>
>
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/columnists/vIacoboni/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 ___
=?Utf-8?B?WWFIb3puYQ==?= <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:Xns98D4A3986E7CDveranoesthemutforsse@.207.46.248.16...
> 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/columnists/vIacoboni/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 ___
> =?Utf-8?B?WWFIb3puYQ==?= <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.|||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 they
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 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.|||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 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.
>
>
New database file locations
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.
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/columnists/vIacoboni/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 ___
=?Utf-8?B?WWFIb3puYQ==?= <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:Xns98D4A3986E7CDveranoesthemutforsse@.207.46.2 48.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/columnists/vIacoboni/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 ___
> =?Utf-8?B?WWFIb3puYQ==?= <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 they
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 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.
|||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 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...
>
>
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/columnists/vIacoboni/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 ___
=?Utf-8?B?WWFIb3puYQ==?= <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:Xns98D4A3986E7CDveranoesthemutforsse@.207.46.2 48.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/columnists/vIacoboni/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 ___
> =?Utf-8?B?WWFIb3puYQ==?= <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 they
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 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.
|||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 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...
>
>
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...
>
>
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...
>
>
Subscribe to:
Posts (Atom)