Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical fil
e
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restore
d to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
--
Senior SQL Server DBAHi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countles
s
> times before with no problems)
> The problem I am having is after I restore the database using the followin
g
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical f
ile
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ld
f'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be resto
red to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. U
se
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it shou
ld
> happen.
> --
> Senior SQL Server DBA|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
--
Senior SQL Server DBA
"doller" wrote:

> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
>|||Seems like SQL Server cannot create the database file names you have specifi
ed. Perhaps the service
account don't have permissions, or that the file names are already in use by
some other database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countles
s
> times before with no problems)
> The problem I am having is after I restore the database using the followin
g
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical f
ile
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ld
f'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be resto
red to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. U
se
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it shou
ld
> happen.
> --
> Senior SQL Server DBA

Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databasename.STAN DBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical file
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
Senior SQL Server DBA
Hi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STAN DBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBA
|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller
|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
Senior SQL Server DBA
"doller" wrote:

> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
>
|||Seems like SQL Server cannot create the database file names you have specified. Perhaps the service
account don't have permissions, or that the file names are already in use by some other database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STAN DBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBA

Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical file
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
--
Senior SQL Server DBAHi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBA|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
--
Senior SQL Server DBA
"doller" wrote:
> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
> > I have a database i am m oving to another server, during the process I am
> > moving the data and log files to another drive.(Which I have done countless
> > times before with no problems)
> > The problem I am having is after I restore the database using the following
> > statement :
> > RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> > WITH STANDBY = 'D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
> > ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\Databasename_Data.mdf'
> > ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\Databasename_Log.ndf'
> >
> > I get these errors when trying to restore transaction logs :
> >
> > [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> > name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> > may be incorrect.
> > [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> > 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> > WITH MOVE to identify a valid location for the file.
> >
> > Has anyone encountered the same problem? This has me stumped, although
> > restoring the tlog with move, and standby works...this is not how it should
> > happen.
> > --
> > Senior SQL Server DBA
>|||Seems like SQL Server cannot create the database file names you have specified. Perhaps the service
account don't have permissions, or that the file names are already in use by some other database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBAsql

Problem creating a secondary datafile on drive E:\

Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
programming. I created a database, tsqldb, on C:\, where the ms sql server
files reside. Now, I used the alter database command to add a secondary
datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
want to put this secondary datafile file, E:\. the alter database command
goes like this:
alter database tsqldb
add file
( name = tsqldb_data2,
filename = 'E:\tsqldb_data2.ndf',
size = 50mb,
maxsize = 150mb,
filegrowth = 25%
)
and this is the error I get:
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
Server: Msg 5009, Level 16, State 1, Line 1
ALTER DATABASE failed. Some disk names listed in the statement were not
found. Check that the names exist and are spelled correctly before rerunning
the statement.
Extending database by 50.00 MB on disk 'tsqldb_data2'.
Can someone help me? Thanks.
DominickCheck whether the service account that runs the SQL Server instance has the
permission to create files in E:\.
Linchi
"Dominick D." wrote:
> Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> programming. I created a database, tsqldb, on C:\, where the ms sql server
> files reside. Now, I used the alter database command to add a secondary
> datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> want to put this secondary datafile file, E:\. the alter database command
> goes like this:
> alter database tsqldb
> add file
> ( name = tsqldb_data2,
> filename = 'E:\tsqldb_data2.ndf',
> size = 50mb,
> maxsize = 150mb,
> filegrowth = 25%
> )
> and this is the error I get:
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> Server: Msg 5009, Level 16, State 1, Line 1
> ALTER DATABASE failed. Some disk names listed in the statement were not
> found. Check that the names exist and are spelled correctly before rerunning
> the statement.
> Extending database by 50.00 MB on disk 'tsqldb_data2'.
> Can someone help me? Thanks.
> Dominick|||How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
> Check whether the service account that runs the SQL Server instance has the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> > programming. I created a database, tsqldb, on C:\, where the ms sql server
> > files reside. Now, I used the alter database command to add a secondary
> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> > want to put this secondary datafile file, E:\. the alter database command
> > goes like this:
> >
> > alter database tsqldb
> > add file
> > ( name = tsqldb_data2,
> > filename = 'E:\tsqldb_data2.ndf',
> > size = 50mb,
> > maxsize = 150mb,
> > filegrowth = 25%
> > )
> >
> > and this is the error I get:
> >
> > Server: Msg 5123, Level 16, State 1, Line 1
> > CREATE FILE encountered operating system error 5(Access is denied.) while
> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > Server: Msg 5009, Level 16, State 1, Line 1
> >
> > ALTER DATABASE failed. Some disk names listed in the statement were not
> > found. Check that the names exist and are spelled correctly before rerunning
> > the statement.
> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> >
> > Can someone help me? Thanks.
> >
> > Dominick|||Using Windows Explorer, right click on the drive or folder and bring up its
properties. It's under the Security tab.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
How do I check that? I"m a newbie to SQL, file permissions, so I'd
appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
> Check whether the service account that runs the SQL Server instance has
> the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin
> > and
> > programming. I created a database, tsqldb, on C:\, where the ms sql
> > server
> > files reside. Now, I used the alter database command to add a secondary
> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where
> > I
> > want to put this secondary datafile file, E:\. the alter database
> > command
> > goes like this:
> >
> > alter database tsqldb
> > add file
> > ( name = tsqldb_data2,
> > filename = 'E:\tsqldb_data2.ndf',
> > size = 50mb,
> > maxsize = 150mb,
> > filegrowth = 25%
> > )
> >
> > and this is the error I get:
> >
> > Server: Msg 5123, Level 16, State 1, Line 1
> > CREATE FILE encountered operating system error 5(Access is denied.)
> > while
> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > Server: Msg 5009, Level 16, State 1, Line 1
> >
> > ALTER DATABASE failed. Some disk names listed in the statement were not
> > found. Check that the names exist and are spelled correctly before
> > rerunning
> > the statement.
> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> >
> > Can someone help me? Thanks.
> >
> > Dominick|||Talk to the person who administers the machine in question. They should be able to check what
service account the service is using and see whether that account has permissions to create files in
the folder in question.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>> Check whether the service account that runs the SQL Server instance has the
>> permission to create files in E:\.
>> Linchi
>> "Dominick D." wrote:
>> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
>> > programming. I created a database, tsqldb, on C:\, where the ms sql server
>> > files reside. Now, I used the alter database command to add a secondary
>> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
>> > want to put this secondary datafile file, E:\. the alter database command
>> > goes like this:
>> >
>> > alter database tsqldb
>> > add file
>> > ( name = tsqldb_data2,
>> > filename = 'E:\tsqldb_data2.ndf',
>> > size = 50mb,
>> > maxsize = 150mb,
>> > filegrowth = 25%
>> > )
>> >
>> > and this is the error I get:
>> >
>> > Server: Msg 5123, Level 16, State 1, Line 1
>> > CREATE FILE encountered operating system error 5(Access is denied.) while
>> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
>> > Server: Msg 5009, Level 16, State 1, Line 1
>> >
>> > ALTER DATABASE failed. Some disk names listed in the statement were not
>> > found. Check that the names exist and are spelled correctly before rerunning
>> > the statement.
>> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
>> >
>> > Can someone help me? Thanks.
>> >
>> > Dominick|||OK, I managed to do a little research on E:\ and I included the service
account that I set up to have full control over E:\ and it did the trick.
"Tom Moreau" wrote:
> Using Windows Explorer, right click on the drive or folder and bring up its
> properties. It's under the Security tab.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
> news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd
> appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
> > Check whether the service account that runs the SQL Server instance has
> > the
> > permission to create files in E:\.
> >
> > Linchi
> >
> > "Dominick D." wrote:
> >
> > > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin
> > > and
> > > programming. I created a database, tsqldb, on C:\, where the ms sql
> > > server
> > > files reside. Now, I used the alter database command to add a secondary
> > > datafile, tsqldb_data2 to drive E:\ that I've created and that is where
> > > I
> > > want to put this secondary datafile file, E:\. the alter database
> > > command
> > > goes like this:
> > >
> > > alter database tsqldb
> > > add file
> > > ( name = tsqldb_data2,
> > > filename = 'E:\tsqldb_data2.ndf',
> > > size = 50mb,
> > > maxsize = 150mb,
> > > filegrowth = 25%
> > > )
> > >
> > > and this is the error I get:
> > >
> > > Server: Msg 5123, Level 16, State 1, Line 1
> > > CREATE FILE encountered operating system error 5(Access is denied.)
> > > while
> > > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > > Server: Msg 5009, Level 16, State 1, Line 1
> > >
> > > ALTER DATABASE failed. Some disk names listed in the statement were not
> > > found. Check that the names exist and are spelled correctly before
> > > rerunning
> > > the statement.
> > > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> > >
> > > Can someone help me? Thanks.
> > >
> > > Dominick
>

Problem creating a secondary datafile on drive E:\

Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
programming. I created a database, tsqldb, on C:\, where the ms sql server
files reside. Now, I used the alter database command to add a secondary
datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
want to put this secondary datafile file, E:\. the alter database command
goes like this:
alter database tsqldb
add file
( name = tsqldb_data2,
filename = 'E:\tsqldb_data2.ndf',
size = 50mb,
maxsize = 150mb,
filegrowth = 25%
)
and this is the error I get:
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
Server: Msg 5009, Level 16, State 1, Line 1
ALTER DATABASE failed. Some disk names listed in the statement were not
found. Check that the names exist and are spelled correctly before rerunning
the statement.
Extending database by 50.00 MB on disk 'tsqldb_data2'.
Can someone help me? Thanks.
DominickCheck whether the service account that runs the SQL Server instance has the
permission to create files in E:\.
Linchi
"Dominick D." wrote:

> Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> programming. I created a database, tsqldb, on C:\, where the ms sql server
> files reside. Now, I used the alter database command to add a secondary
> datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> want to put this secondary datafile file, E:\. the alter database command
> goes like this:
> alter database tsqldb
> add file
> ( name = tsqldb_data2,
> filename = 'E:\tsqldb_data2.ndf',
> size = 50mb,
> maxsize = 150mb,
> filegrowth = 25%
> )
> and this is the error I get:
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> Server: Msg 5009, Level 16, State 1, Line 1
> ALTER DATABASE failed. Some disk names listed in the statement were not
> found. Check that the names exist and are spelled correctly before rerunni
ng
> the statement.
> Extending database by 50.00 MB on disk 'tsqldb_data2'.
> Can someone help me? Thanks.
> Dominick|||How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciat
e
your assistance. Thanks.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Check whether the service account that runs the SQL Server instance has th
e
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
>|||Using Windows Explorer, right click on the drive or folder and bring up its
properties. It's under the Security tab.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
How do I check that? I"m a newbie to SQL, file permissions, so I'd
appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Check whether the service account that runs the SQL Server instance has
> the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
>|||Talk to the person who administers the machine in question. They should be a
ble to check what
service account the service is using and see whether that account has permis
sions to create files in
the folder in question.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...[vbcol=seagreen]
> How do I check that? I"m a newbie to SQL, file permissions, so I'd appreci
ate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>|||OK, I managed to do a little research on E:\ and I included the service
account that I set up to have full control over E:\ and it did the trick.
"Tom Moreau" wrote:

> Using Windows Explorer, right click on the drive or folder and bring up it
s
> properties. It's under the Security tab.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
> news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd
> appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>
>

Problem Connecting to Sql Server 2005 locally by Name

Configuration:
Sql server running on a test machine with mirrored drive for OS and SAN for
data.
Problem:
A week ago a mirrored drive went down. We reconfigured the drive and
updated the drivers on the drive. About the same time the backups stopped
running. The sqlagent reported errors like this one:
2007-08-09 19:03:16 - ! [382] Logon to server 'DatabaseName' failed
(ConnAttemptCachableOp)
2007-08-09 19:03:46 - ! [298] SQLServer Error: 87, SQL Network Interfaces:
Connection string is not valid [87]. [SQLSTATE 08001]
Also note that when trying to connect to the database via the Sql Server
Management Studio using the name on the local machine an error is thrown
stating that a connection couldn't be made; however, when using the IP there
is no problem.
Note that there is no problem connecting to the machine from remote machines
and that the problem is only when trying to connect to the db using the name
from the machine. Reporting services can no longer connect nor the sql agent.
Hmmm. When connecting locally the SQL Server Native Client will attempt to
connect using the Shared Memory protocol. If you connect locally using the
IP address, the SQL Server Native Client will attempt to connect using TCP.
So I'm guessing the problem is with shared memory. Is it still enabled on
both the server and for client connections on the server? You can use SQL
Server Configuration Manager to check both.
Did the server forget it's name? What is the result of SELECT name FROM
sys.servers ?
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:D0BA4742-F782-44AC-A7A8-86E4C8E7ACEA@.microsoft.com...
> Configuration:
> Sql server running on a test machine with mirrored drive for OS and SAN
> for
> data.
> Problem:
> A week ago a mirrored drive went down. We reconfigured the drive and
> updated the drivers on the drive. About the same time the backups stopped
> running. The sqlagent reported errors like this one:
> 2007-08-09 19:03:16 - ! [382] Logon to server 'DatabaseName' failed
> (ConnAttemptCachableOp)
> 2007-08-09 19:03:46 - ! [298] SQLServer Error: 87, SQL Network Interfaces:
> Connection string is not valid [87]. [SQLSTATE 08001]
> Also note that when trying to connect to the database via the Sql Server
> Management Studio using the name on the local machine an error is thrown
> stating that a connection couldn't be made; however, when using the IP
> there
> is no problem.
> Note that there is no problem connecting to the machine from remote
> machines
> and that the problem is only when trying to connect to the db using the
> name
> from the machine. Reporting services can no longer connect nor the sql
> agent.
|||Rick,
Thx for the reply.
1. Q:Did the server forget it's name?
-- Running the supplied query returns the correct name of the server. So,
this is okay.
2. Q:Is it still enabled on both the server and for client connections on
the server?
-- Yes. I disabled Shared Memory for both the client & server and tried
again to connect and still recieve the message " Cannot connect to {database
name}. An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 25 - Connection string is not
valid) (Microsoft SQL Server, Error: 87)"
*note: I have no problem connecting remotely. However - I do have a
problem connection locally and the services all throw similar errors when
trying to connect to the database. Also, I cannot apply any service packs
because I can not authenticate locally.
"Rick Byham, (MSFT)" wrote:

> Hmmm. When connecting locally the SQL Server Native Client will attempt to
> connect using the Shared Memory protocol. If you connect locally using the
> IP address, the SQL Server Native Client will attempt to connect using TCP.
> So I'm guessing the problem is with shared memory. Is it still enabled on
> both the server and for client connections on the server? You can use SQL
> Server Configuration Manager to check both.
> Did the server forget it's name? What is the result of SELECT name FROM
> sys.servers ?
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:D0BA4742-F782-44AC-A7A8-86E4C8E7ACEA@.microsoft.com...
>
|||I can’t explain it (yet) and it may just be coincidence but I was testing
connections to the database in the ODBC Data Source Administrator and created
a connection using the sqlsrv33 driver and then the sqlncli driver and now
all my local connections work and the processes run fine. One thing is the
default value for the pipe name came up incorrectly. I corrected the pipe
there and the test connection worked. It doesn’t make sense to me that this
value would be persisted anywhere and that this fixed the problem. Anyway it
is working now and we’ll see for how long. If anyone has a valid explanation
on if this is just a coincidence or if it actually corrected the issue,
please share. Thx.
"Wes" wrote:
[vbcol=seagreen]
> Rick,
> Thx for the reply.
> 1. Q:Did the server forget it's name?
> -- Running the supplied query returns the correct name of the server. So,
> this is okay.
> 2. Q:Is it still enabled on both the server and for client connections on
> the server?
> -- Yes. I disabled Shared Memory for both the client & server and tried
> again to connect and still recieve the message " Cannot connect to {database
> name}. An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 25 - Connection string is not
> valid) (Microsoft SQL Server, Error: 87)"
> *note: I have no problem connecting remotely. However - I do have a
> problem connection locally and the services all throw similar errors when
> trying to connect to the database. Also, I cannot apply any service packs
> because I can not authenticate locally.
> "Rick Byham, (MSFT)" wrote:
|||When you connect locally by name, SQL Server Native Client uses shared
memory. And shared memory is a local pipe. So when you fixed something in
named pipes, it does make sense that it might have fixed shared memory.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:4D86C15C-5B9D-4DEB-B61B-FF392985D5B1@.microsoft.com...[vbcol=seagreen]
>I can’t explain it (yet) and it may just be coincidence but I was testing
> connections to the database in the ODBC Data Source Administrator and
> created
> a connection using the sqlsrv33 driver and then the sqlncli driver and now
> all my local connections work and the processes run fine. One thing is
> the
> default value for the pipe name came up incorrectly. I corrected the pipe
> there and the test connection worked. It doesn’t make sense to me that
> this
> value would be persisted anywhere and that this fixed the problem. Anyway
> it
> is working now and we’ll see for how long. If anyone has a valid
> explanation
> on if this is just a coincidence or if it actually corrected the issue,
> please share. Thx.
> "Wes" wrote:

Problem Connecting to Sql Server 2005 locally by Name

Configuration:
Sql server running on a test machine with mirrored drive for OS and SAN for
data.
Problem:
A week ago a mirrored drive went down. We reconfigured the drive and
updated the drivers on the drive. About the same time the backups stopped
running. The sqlagent reported errors like this one:
2007-08-09 19:03:16 - ! [382] Logon to server 'DatabaseName' failed
(ConnAttemptCachableOp)
2007-08-09 19:03:46 - ! [298] SQLServer Error: 87, SQL Network Interface
s:
Connection string is not valid [87]. [SQLSTATE 08001]
Also note that when trying to connect to the database via the Sql Server
Management Studio using the name on the local machine an error is thrown
stating that a connection couldn't be made; however, when using the IP there
is no problem.
Note that there is no problem connecting to the machine from remote machines
and that the problem is only when trying to connect to the db using the name
from the machine. Reporting services can no longer connect nor the sql agen
t.Hmmm. When connecting locally the SQL Server Native Client will attempt to
connect using the Shared Memory protocol. If you connect locally using the
IP address, the SQL Server Native Client will attempt to connect using TCP.
So I'm guessing the problem is with shared memory. Is it still enabled on
both the server and for client connections on the server? You can use SQL
Server Configuration Manager to check both.
Did the server forget it's name? What is the result of SELECT name FROM
sys.servers ?
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:D0BA4742-F782-44AC-A7A8-86E4C8E7ACEA@.microsoft.com...
> Configuration:
> Sql server running on a test machine with mirrored drive for OS and SAN
> for
> data.
> Problem:
> A week ago a mirrored drive went down. We reconfigured the drive and
> updated the drivers on the drive. About the same time the backups stopped
> running. The sqlagent reported errors like this one:
> 2007-08-09 19:03:16 - ! [382] Logon to server 'DatabaseName' failed
> (ConnAttemptCachableOp)
> 2007-08-09 19:03:46 - ! [298] SQLServer Error: 87, SQL Network Interfa
ces:
> Connection string is not valid [87]. [SQLSTATE 08001]
> Also note that when trying to connect to the database via the Sql Server
> Management Studio using the name on the local machine an error is thrown
> stating that a connection couldn't be made; however, when using the IP
> there
> is no problem.
> Note that there is no problem connecting to the machine from remote
> machines
> and that the problem is only when trying to connect to the db using the
> name
> from the machine. Reporting services can no longer connect nor the sql
> agent.|||Rick,
Thx for the reply.
1. Q:Did the server forget it's name?
-- Running the supplied query returns the correct name of the server. So,
this is okay.
2. Q:Is it still enabled on both the server and for client connections on
the server?
-- Yes. I disabled Shared Memory for both the client & server and tried
again to connect and still recieve the message " Cannot connect to {da
tabase
name}. An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections
.
(provider: SQL Network Interfaces, error: 25 - Connection string is not
valid) (Microsoft SQL Server, Error: 87)"
*note: I have no problem connecting remotely. However - I do have a
problem connection locally and the services all throw similar errors when
trying to connect to the database. Also, I cannot apply any service packs
because I can not authenticate locally.
"Rick Byham, (MSFT)" wrote:

> Hmmm. When connecting locally the SQL Server Native Client will attempt to
> connect using the Shared Memory protocol. If you connect locally using the
> IP address, the SQL Server Native Client will attempt to connect using TCP
.
> So I'm guessing the problem is with shared memory. Is it still enabled on
> both the server and for client connections on the server? You can use SQL
> Server Configuration Manager to check both.
> Did the server forget it's name? What is the result of SELECT name FROM
> sys.servers ?
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:D0BA4742-F782-44AC-A7A8-86E4C8E7ACEA@.microsoft.com...
>|||I can’t explain it (yet) and it may just be coincidence but I was testing
connections to the database in the ODBC Data Source Administrator and create
d
a connection using the sqlsrv33 driver and then the sqlncli driver and now
all my local connections work and the processes run fine. One thing is the
default value for the pipe name came up incorrectly. I corrected the pipe
there and the test connection worked. It doesn’t make sense to me that th
is
value would be persisted anywhere and that this fixed the problem. Anyway i
t
is working now and we’ll see for how long. If anyone has a valid explanat
ion
on if this is just a coincidence or if it actually corrected the issue,
please share. Thx.
"Wes" wrote:
[vbcol=seagreen]
> Rick,
> Thx for the reply.
> 1. Q:Did the server forget it's name?
> -- Running the supplied query returns the correct name of the server. So
,
> this is okay.
> 2. Q:Is it still enabled on both the server and for client connections on
> the server?
> -- Yes. I disabled Shared Memory for both the client & server and tried
> again to connect and still recieve the message " Cannot connect to {
database
> name}. An error has occurred while establishing a connection to the serve
r.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote connectio
ns.
> (provider: SQL Network Interfaces, error: 25 - Connection string is not
> valid) (Microsoft SQL Server, Error: 87)"
> *note: I have no problem connecting remotely. However - I do have a
> problem connection locally and the services all throw similar errors when
> trying to connect to the database. Also, I cannot apply any service packs
> because I can not authenticate locally.
> "Rick Byham, (MSFT)" wrote:
>|||When you connect locally by name, SQL Server Native Client uses shared
memory. And shared memory is a local pipe. So when you fixed something in
named pipes, it does make sense that it might have fixed shared memory.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:4D86C15C-5B9D-4DEB-B61B-FF392985D5B1@.microsoft.com...[vbcol=seagreen]
>I can’t explain it (yet) and it may just be coincidence but I was testing
> connections to the database in the ODBC Data Source Administrator and
> created
> a connection using the sqlsrv33 driver and then the sqlncli driver and now
> all my local connections work and the processes run fine. One thing is
> the
> default value for the pipe name came up incorrectly. I corrected the pipe
> there and the test connection worked. It doesn’t make sense to me that
> this
> value would be persisted anywhere and that this fixed the problem. Anyway
> it
> is working now and we’ll see for how long. If anyone has a valid
> explanation
> on if this is just a coincidence or if it actually corrected the issue,
> please share. Thx.
> "Wes" wrote:
>