Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?
You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?
|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FTP
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You need to set the TEMP/TMP environment variable settings for the user that
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
>
>
|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?
|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, you didn't mention this was a scheduled job. What is the proxy/account
> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
> owner of the job?
>
>

Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FTP
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:
> You need to set the TEMP/TMP environment variable settings for the user that
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> > Hello,
> >
> > xp_cmdshell seems to be using c:\windows\system32 as some sort of
> > "working"
> > directory. When we excute commands that create temporary files (such as
> > FTP
> > for example), they write their temporary files to c:\windows\system32. If
> > we're ftping a large file, this causes problems.
> >
> > Can this be changed? When FTP.exe is run from a normal command window, it
> > seems to respect the TEMP/TMP environment variable settings. When run
> > from
> > xp_cmdshell, it doesn't seem to respect those settings.
> >
> > Does anyone have any ideas?
>
>|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > It looks like this FTP procedure is part of a defined job that's scheduled
> > to run a couple of times a week. That step that uses xp_cmdshell to call
> > FTP
> > is set to run as "Self".
> >
> > Any other ideas?
> Oh, you didn't mention this was a scheduled job. What is the proxy/account
> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
> owner of the job?
>
>|||You can verify the account and the environment variables by
executing a job with the same job owner to execute
xp_cmdshell 'SET' . Have the job step output to a file and
then check that file.
Check the system variables for the temporary directory as
well. Control Panel -> System. Select the Advanced tab - you
can check environment variables there. Not everything
related to a profile is loaded when the account is running a
service.
-Sue
On Fri, 16 Feb 2007 15:07:03 -0800, Daniel Peterson
<pythas@.hotmail.com> wrote:
>SQL Service Account is running as the same service account as the SQL Server
>itself. If I login as that service account user, environment variables look
>like they get set properly. Looks like the server isn't configured with a
>proxy account.
>Didn't realize it was a scheduled job either, our developers left that out
>of the email they sent me, and I just noticed it when I was pawing through
>the job definition more.
>Looks like the owner of the job is sa.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>> > It looks like this FTP procedure is part of a defined job that's scheduled
>> > to run a couple of times a week. That step that uses xp_cmdshell to call
>> > FTP
>> > is set to run as "Self".
>> >
>> > Any other ideas?
>> Oh, you didn't mention this was a scheduled job. What is the proxy/account
>> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
>> owner of the job?
>>

Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FT
P
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You need to set the TEMP/TMP environment variable settings for the user th
at
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
>
>|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, you didn't mention this was a scheduled job. What is the proxy/accoun
t
> in use for the SQL Server Agent service (not SQL Server itself)? Who is t
he
> owner of the job?
>
>|||You can verify the account and the environment variables by
executing a job with the same job owner to execute
xp_cmdshell 'SET' . Have the job step output to a file and
then check that file.
Check the system variables for the temporary directory as
well. Control Panel -> System. Select the Advanced tab - you
can check environment variables there. Not everything
related to a profile is loaded when the account is running a
service.
-Sue
On Fri, 16 Feb 2007 15:07:03 -0800, Daniel Peterson
<pythas@.hotmail.com> wrote:
[vbcol=seagreen]
>SQL Service Account is running as the same service account as the SQL Serve
r
>itself. If I login as that service account user, environment variables loo
k
>like they get set properly. Looks like the server isn't configured with a
>proxy account.
>Didn't realize it was a scheduled job either, our developers left that out
>of the email they sent me, and I just noticed it when I was pawing through
>the job definition more.
>Looks like the owner of the job is sa.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>

Change Unc Path On Sql

I have a question on how to change a hyperlink path. I have an ADP that has our scanned files linked to it the path is on SQL server. The problems is that we are migrating sql server and the files to their own seperate server,
\\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\5630.pdf

The server is called \\gcfsql\ Instead of gcfs01.
does anyone know how we can change the unc path to gcfsql without

\\gcfsQL\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\5630.pdf

Any help would be greatly appreciatedNot sure I understand the question fully. Do you mean you have a bunch of UNC paths stored in a table and you want to update the server name in each path?

If so, you could do something like this:

update mytable set path = replace(path,'\\oldserver\','\\newserver\')|||See the thing is that we have about over 2000 employees who get an employee number and their file is saved in the old server as their number
and the only thing we need to change is the destination server the rest is fine but I dont want to mess anything up. Know what I mean, so I'm asking the Gurus :)|||Run Jezemine's replace statement as a select to see what it produces. Unless any of your employees have "\\" in their filenames, it should give you what you want.|||update EmployeeDocuments set EmployeeDocs = replace(\\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES,'\\gcfs01\','\\gcsql\')|||no, don't run that. it will update every record with the same value. certainly not what you want. you want this:

update EmployeeDocuments set EmployeeDocs = replace(EmployeeDocs,'\\gcfs01\','\\gcsql\')|||You realize this goes against Normalization. In a disaster recovery situation, you may be saddled with this breaking again - just when you least have time for it. Or you may want to create a test environment and wind up with someone updating the production employee records.

Things like "where we keep stuff" and "the servername we keep it on" should be in a central place and never hard coded into "each employee's record" - unless that can be different for different groups of employees.

A little late to fix, but something to file away in the brain for the next time this sort of design decision arises.|||Thats exactly what I did and it worked.

first I changed the ntext data type to nvchar(300)
and it worked .

Thank you guys worked like a charm|||its just that we are changing servers thats all and we wanted to change the path from the old server to the new server thats all.

Change TL Drive location

hi,
I have the data file and transaction log file on the same
drive, SQl Server 7.0, how can i change de TL location? i
have the files on drive e:\ for instance and i want to put
only the TL files on drive d:\, thanks a lot in advance
Regards,
CMLCINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Miguel" <cmlcorreia@.netcabo.pt> wrote in message
news:01ad01c3424b$88afd130$a101280a@.phx.gbl...
hi,
I have the data file and transaction log file on the same
drive, SQl Server 7.0, how can i change de TL location? i
have the files on drive e:\ for instance and i want to put
only the TL files on drive d:\, thanks a lot in advance
Regards,
CMLC

Change the location of the tempdb and the model databases

Is there a way to change the location of the tempdb and the model database files. I tried to do a restore with move with no luck and I cannot seem to get the syntax for an Alter database. Any suggestions?Look up Atricle Q224071 in Microsoft's knowledge base, it addresses your questions.

Change the location of the database files

Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)
Refer to following url
http://support.microsoft.com/default...en-us%3B224071
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"Gal" <W32.virus@.wanadoo.fr> wrote in message
news:uM8J5PqMEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to move my base from drive C: to drive D: .How can I do this
> with MSDE (I don't have the MMC installed on the computer where I want to
do
> this)
>
|||hi Gal,
"Gal" <W32.virus@.wanadoo.fr> ha scritto nel messaggio
news:uM8J5PqMEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to move my base from drive C: to drive D: .How can I do this
> with MSDE (I don't have the MMC installed on the computer where I want to
do
> this)
you can do it detaching the desired database, moving the database files to
the new desired position a,d reattacchin the database...
log into oSql.exe..
1>EXEC sp_detach_db db_name
2>GO
move the file(s) to new location
1>EXEC sp_attach_db @.dbname = 'db_name' ,
2>@.filename1 = 'd:\:fulldatapath_of_DataFile.Mdf' ,
3>@.filename2 = 'd:\:fulldatapath_of_LogFile.Ldf'
4>GO
for your convenienence, you can have a look at a free prj of mine, available
at the link following my sign., which provide a user interface similar to
Enterprise Manager for full MSDE administration...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.RM
[url]http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--[/url
]
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the
backup, each file need
to have the same logical name, and be at least as big as when you took the b
ackup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...[vb
col=seagreen]
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.[/vbcol]

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.
RM
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||RM
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.RM
http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.

Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containing
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!
Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:

> I'm running SQL Server 2000. I have a DB with a logical file name containing
> a hyphen. When trying to change the size of the db and log files by running
> the following SQL statement it throws an error stating the file name is not
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned them
> automatically and put the hyphens in. What is causing the problem with the
> SQL command and how can I get around this problem.
> Thanks for any help!
|||SP_HELPFILE results:
DB_NAME-DS_Data
1<drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY32896 KBUnlimited10%data only
DB_NAME-DS_Log
2<drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL102400 KBUnlimited25600 KBlog only
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:

Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containin
g
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:

> I'm running SQL Server 2000. I have a DB with a logical file name contain
ing
> a hyphen. When trying to change the size of the db and log files by runni
ng
> the following SQL statement it throws an error stating the file name is no
t
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned the
m
> automatically and put the hyphens in. What is causing the problem with th
e
> SQL command and how can I get around this problem.
> Thanks for any help!|||SP_HELPFILE results:
DB_NAME-DS_Data
1 <drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY 32896 KB Unlimited 10% data only
DB_NAME-DS_Log
2 <drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL 102400 KB Unlimited 25600 KB log only
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:
>