Disk permission to move SQL 2005 databases too...

I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
to use the copy wizard to move databases from SQL2000 to SQL2005, when I do
this and to try to change the drive letters to my D: drive for the logs and
E: drive for the database, it goes through the process and fails. So I have
tried it again this time to the default location that SQL 2005 wants, which
is on the C: drive - of course this is not acceptable, but it works that
way. I figure it is a NTFS / permissions issue. But what permissions do I
need to setup on the other drive D: and E: to get this to work properly,
anyone please help me.
Thanks,
Andres
andres.perales@.mclaneat.com
Hi
Take a look at RESTORE command WITH MOVE option in the BOL
"Andres Perales" <andres@.andresperales.com> wrote in message
news:eu%23YtAyIHHA.1248@.TK2MSFTNGP03.phx.gbl...
>I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
>to use the copy wizard to move databases from SQL2000 to SQL2005, when I do
>this and to try to change the drive letters to my D: drive for the logs and
>E: drive for the database, it goes through the process and fails. So I
>have tried it again this time to the default location that SQL 2005 wants,
>which is on the C: drive - of course this is not acceptable, but it works
>that way. I figure it is a NTFS / permissions issue. But what permissions
>do I need to setup on the other drive D: and E: to get this to work
>properly, anyone please help me.
> Thanks,
> Andres
> andres.perales@.mclaneat.com
>
>

Disk permission to move SQL 2005 databases too...

I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
to use the copy wizard to move databases from SQL2000 to SQL2005, when I do
this and to try to change the drive letters to my D: drive for the logs and
E: drive for the database, it goes through the process and fails. So I have
tried it again this time to the default location that SQL 2005 wants, which
is on the C: drive - of course this is not acceptable, but it works that
way. I figure it is a NTFS / permissions issue. But what permissions do I
need to setup on the other drive D: and E: to get this to work properly,
anyone please help me.
Thanks,
Andres
andres.perales@.mclaneat.comHi
Take a look at RESTORE command WITH MOVE option in the BOL
"Andres Perales" <andres@.andresperales.com> wrote in message
news:eu%23YtAyIHHA.1248@.TK2MSFTNGP03.phx.gbl...
>I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
>to use the copy wizard to move databases from SQL2000 to SQL2005, when I do
>this and to try to change the drive letters to my D: drive for the logs and
>E: drive for the database, it goes through the process and fails. So I
>have tried it again this time to the default location that SQL 2005 wants,
>which is on the C: drive - of course this is not acceptable, but it works
>that way. I figure it is a NTFS / permissions issue. But what permissions
>do I need to setup on the other drive D: and E: to get this to work
>properly, anyone please help me.
> Thanks,
> Andres
> andres.perales@.mclaneat.com
>
>|||Assuming the RESTORE command is correct (you have used the MOVE options corr
ectly):
The account used by the SQL Server service need create file and write permis
sions on the directory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andres Perales" <andres@.andresperales.com> wrote in message
news:eu%23YtAyIHHA.1248@.TK2MSFTNGP03.phx.gbl...
>I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
to use the copy wizard
>to move databases from SQL2000 to SQL2005, when I do this and to try to cha
nge the drive letters to
>my D: drive for the logs and E: drive for the database, it goes through the
process and fails. So
>I have tried it again this time to the default location that SQL 2005 wants
, which is on the C:
>drive - of course this is not acceptable, but it works that way. I figure
it is a NTFS /
>permissions issue. But what permissions do I need to setup on the other dri
ve D: and E: to get this
>to work properly, anyone please help me.
> Thanks,
> Andres
> andres.perales@.mclaneat.com
>
>

Disk permission to move SQL 2005 databases too...

I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
to use the copy wizard to move databases from SQL2000 to SQL2005, when I do
this and to try to change the drive letters to my D: drive for the logs and
E: drive for the database, it goes through the process and fails. So I have
tried it again this time to the default location that SQL 2005 wants, which
is on the C: drive - of course this is not acceptable, but it works that
way. I figure it is a NTFS / permissions issue. But what permissions do I
need to setup on the other drive D: and E: to get this to work properly,
anyone please help me.
Thanks,
Andres
andres.perales@.mclaneat.comHi
Take a look at RESTORE command WITH MOVE option in the BOL
"Andres Perales" <andres@.andresperales.com> wrote in message
news:eu%23YtAyIHHA.1248@.TK2MSFTNGP03.phx.gbl...
>I have a server with SQL 2000 and a name instance of SQL 2005, I am trying
>to use the copy wizard to move databases from SQL2000 to SQL2005, when I do
>this and to try to change the drive letters to my D: drive for the logs and
>E: drive for the database, it goes through the process and fails. So I
>have tried it again this time to the default location that SQL 2005 wants,
>which is on the C: drive - of course this is not acceptable, but it works
>that way. I figure it is a NTFS / permissions issue. But what permissions
>do I need to setup on the other drive D: and E: to get this to work
>properly, anyone please help me.
> Thanks,
> Andres
> andres.perales@.mclaneat.com
>
>|||Assuming the RESTORE command is correct (you have used the MOVE options correctly):
The account used by the SQL Server service need create file and write permissions on the directory.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andres Perales" <andres@.andresperales.com> wrote in message
news:eu%23YtAyIHHA.1248@.TK2MSFTNGP03.phx.gbl...
>I have a server with SQL 2000 and a name instance of SQL 2005, I am trying to use the copy wizard
>to move databases from SQL2000 to SQL2005, when I do this and to try to change the drive letters to
>my D: drive for the logs and E: drive for the database, it goes through the process and fails. So
>I have tried it again this time to the default location that SQL 2005 wants, which is on the C:
>drive - of course this is not acceptable, but it works that way. I figure it is a NTFS /
>permissions issue. But what permissions do I need to setup on the other drive D: and E: to get this
>to work properly, anyone please help me.
> Thanks,
> Andres
> andres.perales@.mclaneat.com
>
>sql

Disconnect all connections

Hi All,
I trying to write a script that will detach a DB copy the physical file to a new location then remount the original DB.
the Problem that i am having it detaching the DB as i get error telling me that the DB is in use.
There is an Access front end open with and ODBC connection to the SQL DB and this needed to be open to as it is where the command to create the Copy is coming from.

I have tried the following code
USE master ALTER DATABASE EclipseSQL SET SINGLE_USER with no_wait
exec sp_detach_db 'EclipseSQL'
exec xp_cmdshell 'MKDIR "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices" '

exec xp_cmdshell 'ECHO Y| cacls "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" /p everyone:f'

exec xp_cmdshell 'xCopy "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices\" '

exec sp_Attach_db 'EclipseSQL', 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf'

i get the following output from this

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'EclipseSQL'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'EclipseSQL' because it is currently in use.

(2 row(s) affected)

(2 row(s) affected)

(3 row(s) affected)
Msg 1801, Level 16, State 3, Line 1
Database 'EclipseSQL' already exists. Choose a different database name.

any know how i get disconnect all users no questions asked?

Try:

Code Snippet

alterdatabase EclipseSQL setread_onlywithrollback immediate

go

alterdatabase EclipseSQL setread_writewithrollback immediate

go

|||That did the trick thanks