I am new to replication. Please forgive me if this sounds silly.
I have one publisher and several subscriber, all in different locations. In
case of emergency and I have to shut down the publisher, I want to use the
one of the subscribers to process our business, and I want to turn this
subscriber to a new publisher. Is this possible? What kind of replication
should I use?
Its not really possible in SQL 2000, but it looks like the peer-to-peer
replication model in 2005.
You have to look at how the data flows. If it flows only from the publisher
to the subscribers it might be possible to convert a subscriber to a
publisher by applying the publication creation script on one of the
subscribers you wish to promote the new publisher. Then make all other
subscribers subscribers of this new publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> I am new to replication. Please forgive me if this sounds silly.
> I have one publisher and several subscriber, all in different locations.
In
> case of emergency and I have to shut down the publisher, I want to use the
> one of the subscribers to process our business, and I want to turn this
> subscriber to a new publisher. Is this possible? What kind of replication
> should I use?
|||Thanks a lot for your reply.
Do you mean that if I am in SQL 2000, and the subscribers only read data,
not write, I should be able to turn one subscriber to a publisher? Actually,
what I want to do is, when the publisher has to be shut down (or damaged), I
will still have the full database for business continuity in another
location. This can be a kind of emergency backup system.
"Hilary Cotter" wrote:
> Its not really possible in SQL 2000, but it looks like the peer-to-peer
> replication model in 2005.
> You have to look at how the data flows. If it flows only from the publisher
> to the subscribers it might be possible to convert a subscriber to a
> publisher by applying the publication creation script on one of the
> subscribers you wish to promote the new publisher. Then make all other
> subscribers subscribers of this new publisher.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Wind" <Wind@.discussions.microsoft.com> wrote in message
> news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> In
>
>
|||Yes you can do this, but its difficult. Basically you have to prep your
database on the subscriber.
IIRC you have to do the following
1) backup the publisher
2) restore it on the subscriber
3) change all identity columns to NFR
4) change all triggers to NFR
5) change all constraints to NFR
You may need to run DBCC checkident ('tableName', reseed,X) on some of the
subscriber tables to fix the identity property on the restored databases,
don't use the set identity_insert 'tableName', off in the insert procs
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:346FA462-5CD1-4156-BC06-E6B0662E4BFF@.microsoft.com...[vbcol=seagreen]
> Thanks a lot for your reply.
> Do you mean that if I am in SQL 2000, and the subscribers only read data,
> not write, I should be able to turn one subscriber to a publisher?
> Actually,
> what I want to do is, when the publisher has to be shut down (or damaged),
> I
> will still have the full database for business continuity in another
> location. This can be a kind of emergency backup system.
> "Hilary Cotter" wrote:
|||What about the alternate synchronization partner?
Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23zmkjND0FHA.2960@.tk2msftngp13.phx.gbl...
> Its not really possible in SQL 2000, but it looks like the peer-to-peer
> replication model in 2005.
> You have to look at how the data flows. If it flows only from the
publisher[vbcol=seagreen]
> to the subscribers it might be possible to convert a subscriber to a
> publisher by applying the publication creation script on one of the
> subscribers you wish to promote the new publisher. Then make all other
> subscribers subscribers of this new publisher.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Wind" <Wind@.discussions.microsoft.com> wrote in message
> news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> In
the[vbcol=seagreen]
replication
>
|||Plain merge and queued updating subscribers would also come into this
section, although none of these techniques would really allow the subscriber
to easily become the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label locations. Show all posts
Showing posts with label locations. Show all posts
Friday, March 30, 2012
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)