Default Data/Log File Directory

#1

Running MS SQL Server Personal. In the properties for the local server group,
Database Settings, I have D:\MS SQL Server\Data\ set for the data and log
files. If I create new db in EM, the files go to the above directory. If I do
a create db in QA, the files go to the above directory. However, if I use DTS
to copy one db (located anywhere) into "new" on this server, the files always
end up in D:\Program Files\Microsoft SQL Server\MSSQL\Data. That directory
contains the master, model, tempdb, etc non-movable system dbs. I have
searched a bunch, but have not found how to fix this. My workaround is to
first create new db in EM, then copy the db using DTS to the existing db.

Can anyone point me towards a fix?

--
Thanks, Mike.

#2

That is because when DTS does it then what it says is

"This guy wants to create a DB"
"He wants to name it X"
"I have not shown him where to put it, just that he sould give me a name"
"Let me get the location of the master database and I shall concatenate that
path onto the name of his DB"

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"Mike Vanecek" <MikeV99 (AT) privacy (DOT) net> wrote

Quote:

Running MS SQL Server Personal. In the properties for the local server
group,
Database Settings, I have D:\MS SQL Server\Data\ set for the data and log
files. If I create new db in EM, the files go to the above directory. If I
do
a create db in QA, the files go to the above directory. However, if I use
DTS
to copy one db (located anywhere) into "new" on this server, the files
always
end up in D:\Program Files\Microsoft SQL Server\MSSQL\Data. That directory
contains the master, model, tempdb, etc non-movable system dbs. I have
searched a bunch, but have not found how to fix this. My workaround is to
first create new db in EM, then copy the db using DTS to the existing db.

Can anyone point me towards a fix?

--
Thanks, Mike.

#3

On Wed, 8 Sep 2004 21:22:13 +0100, Allan Mitchell wrote:

Quote:

That is because when DTS does it then what it says is

"This guy wants to create a DB"
"He wants to name it X"
"I have not shown him where to put it, just that he sould give me a name"
"Let me get the location of the master database and I shall concatenate that
path onto the name of his DB"

OK, we have a feature and not a bug or operator problem. Since you did not say
anything about a config change that would change that behavior, one must not
exist. Just create the db first, and then use DTS.

Is it correct to say that to move the system dbs (master, tempdb, etc) one
would need to uninstall and reinstall the server?

--
Thanks, Mike.

#4

It would be incorrect to say that.

http://www.databasejournal.com/featu...le.php/3379901

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"Mike Vanecek" <MikeV99 (AT) privacy (DOT) net> wrote

Quote:

On Wed, 8 Sep 2004 21:22:13 +0100, Allan Mitchell wrote:
That is because when DTS does it then what it says is

"This guy wants to create a DB"
"He wants to name it X"
"I have not shown him where to put it, just that he sould give me a
name"
"Let me get the location of the master database and I shall concatenate
that
path onto the name of his DB"

OK, we have a feature and not a bug or operator problem. Since you did not
say
anything about a config change that would change that behavior, one must
not
exist. Just create the db first, and then use DTS.

Is it correct to say that to move the system dbs (master, tempdb, etc) one
would need to uninstall and reinstall the server?

--
Thanks, Mike.

#5

On Thu, 9 Sep 2004 05:59:46 +0100, Allan Mitchell wrote:

Quote:

It would be incorrect to say that.

http://www.databasejournal.com/featu...le.php/3379901

It is nice to be incorrect and still receive a benefit. Thank you.

--
Thanks, Mike.

#6

On Fri, 10 Sep 2004 08:03:02 -0700, Richard Wells wrote:

Quote:

After moving the Master, Model, MSDB and Tempdb databases I still found that
a new DB created through DTS was still placed in the old location.

I found that the Master database location within the (Master) Sysdatabases
table was for the old path. Correcting the location resolved my problem.

(Direct modification to system tables isn?t allowed unless you start SQL
using the -m option. BOL Search: Allow Updates Option / Administering SQL
Server)

Hope this helps!!

Perfect, absolutely perfect!

I used the

USE master
EXEC sp_configure 'show advanced option', '1'

RECONFIGURE
EXEC sp_configure

USE master
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE

approach found in the BOL source you indicated above. That little piece of
advice really needs to be in

http://www.databasejournal.com/featu...le.php/3379901

and

http://www.support.microsoft.com/?id=224071

Finally ...

--
Thanks, Mike.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Default Data/Log File Directory

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Default Data/Log File Directory - 09-08-2004 , 07:43 AM

Re: Default Data/Log File Directory - 09-08-2004 , 03:22 PM

Re: Default Data/Log File Directory - 09-08-2004 , 09:02 PM

Re: Default Data/Log File Directory - 09-08-2004 , 11:59 PM

Re: Default Data/Log File Directory - 09-09-2004 , 03:07 PM

Re: Default Data/Log File Directory - 09-11-2004 , 09:27 AM