Attach error

I plan to move my database file to another folder, I detach it first (successfull), but I can't move (Cut&Paste) my database to another folder (Access is denied), And then I go back object explorer but error when I try to Attach

"Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum)...... CREATE FILE encountered operating system error 5 (Access is denied)"

please tell me how to attach my database, I really need the data..

thx..

Hi,

you will have to logon with a user who has access to the file first. If you don′t have any, you can logon on SSMS with a SQL Server login (if enabled) and attach the database. The SQL Server Account will be used then for attaching the database whic apparantly had the access to the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Attach e Detach database

Maybe this a stupid question, but is there any differences beetwen detach
and attach a database from Enterprise Manager (All Tasks-Detach/Attach) or
with the system stored procedures ?
e.g.
if I look at KB 224071
http://support.microsoft.com/support/kb/articles/q224/0/71.asp (Moving SQL
Server Databases to a New Location with Detach/Attach) the articole said to
detach the database as follows :
use master
go
sp_detach_db 'mydb'
go
and to re-attach the database as follows :
use master
go
sp_attach_db 'mydb','...','...'
go
I think it's more simple to detach and attach the database from Enterprise
Manager than to write the stored procedure ...
Thanks,
Michele L.EA justs acts as a nice GUI. In the backgroud it actually
runs the sp_detach_db, sp_attach_db.
So your answer is - no not really ;)
J
>--Original Message--
>Maybe this a stupid question, but is there any
differences beetwen detach
>and attach a database from Enterprise Manager (All Tasks-
Detach/Attach) or
>with the system stored procedures ?
>e.g.
>if I look at KB 224071
>http://support.microsoft.com/support/kb/articles/q224/0/71
.asp (Moving SQL
>Server Databases to a New Location with Detach/Attach)
the articole said to
>detach the database as follows :
>use master
>go
>sp_detach_db 'mydb'
>go
>and to re-attach the database as follows :
>use master
>go
>sp_attach_db 'mydb','...','...'
>go
>I think it's more simple to detach and attach the
database from Enterprise
>Manager than to write the stored procedure ...
>Thanks,
>Michele L.
>
>
>
>
>.
>

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.
You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.

attach detach db

Is it possible detaching one db coping under/ different names /master and lof
files and reattaching db again to cause login problems later. if yes why?
Not sure I understand the question but the answer is most likely in one of
these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>
|||Hi,
If you are attaching the database with different name in same server, you
will never get any Login issue. If you are attaching into
a different server then you have syncronize the Logins and users using the
system procedure SP_CHANGE_USERS_LOGIN (see Books online)
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>
|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Hari Prasad" wrote:

> Hi,
> If you are attaching the database with different name in same server, you
> will never get any Login issue. If you are attaching into
> a different server then you have syncronize the Logins and users using the
> system procedure SP_CHANGE_USERS_LOGIN (see Books online)
> Thanks
> Hari
> SQL Server MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>
>
|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Andrew J. Kelly" wrote:

> Not sure I understand the question but the answer is most likely in one of
> these:
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.databasejournal.com/featu...le.php/3379901 Moving
> system DB's
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>
>
|||Then you should look at the links I posted. In particular this first one
should be most helpful:
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...[vbcol=seagreen]
> Hi Hary,
> thanks for your reply. Let me try to explain the situation I am in. I had
> to
> make copy for training purposes of a production db. I detached prod db and
> copied mdf and ldf in the same data directory but with names test.mdf and
> test.ldf. Then I attached our prod db and new test db. During attaching I
> had
> to choose dbowner (domain admin). It seemed to me that i saved time using
> this teqniche. But later users reported that they couldnt log in prod and
> test db via the application (its in citrix). After short phone talk with
> vendors they explained that detaching and attaching caused dbo alias
> deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> 'dbo' and everithing looks fine with prod db. But its impossible to use
> test
> db. Users that are in sys admin group dont have problem but ordinary users
> that connect to applications db via app user cannot login at all. I wonder
> whats the problem? all ordinary users are stored in users table in prod db
> and have encrypted passwords. app user password is encrypted and stored in
> different db.
> i will be dismissed : )
>
> "Andrew J. Kelly" wrote:
|||Thanx : ) I hope this will help. Just curios if EE could have the same login
problems. May be will be better to upgrade to EE
"Andrew J. Kelly" wrote:

> Then you should look at the links I posted. In particular this first one
> should be most helpful:
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
>
>
|||It would be the same for any edition.
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:B7FD797D-F1DB-41E4-B6D4-0BDA27B27153@.microsoft.com...[vbcol=seagreen]
> Thanx : ) I hope this will help. Just curios if EE could have the same
> login
> problems. May be will be better to upgrade to EE
> "Andrew J. Kelly" wrote:

attach detach db

Is it possible detaching one db coping under/ different names /master and lo
f
files and reattaching db again to cause login problems later. if yes why?Not sure I understand the question but the answer is most likely in one of
these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi,
If you are attaching the database with different name in same server, you
will never get any Login issue. If you are attaching into
a different server then you have syncronize the Logins and users using the
system procedure SP_CHANGE_USERS_LOGIN (see Books online)
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I ha
d
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Hari Prasad" wrote:

> Hi,
> If you are attaching the database with different name in same server, you
> will never get any Login issue. If you are attaching into
> a different server then you have syncronize the Logins and users using the
> system procedure SP_CHANGE_USERS_LOGIN (see Books online)
> Thanks
> Hari
> SQL Server MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I ha
d
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Andrew J. Kelly" wrote:

> Not sure I understand the question but the answer is most likely in one of
> these:
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.databasejournal.com/feat...cle.php/3379901 Movin
g
> system DB's
> http://www.support.microsoft.com/?id=314546 Moving DB's between Server
s
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Database
s
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>
>|||Then you should look at the links I posted. In particular this first one
should be most helpful:
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...[vbcol=seagreen]
> Hi Hary,
> thanks for your reply. Let me try to explain the situation I am in. I had
> to
> make copy for training purposes of a production db. I detached prod db and
> copied mdf and ldf in the same data directory but with names test.mdf and
> test.ldf. Then I attached our prod db and new test db. During attaching I
> had
> to choose dbowner (domain admin). It seemed to me that i saved time using
> this teqniche. But later users reported that they couldnt log in prod and
> test db via the application (its in citrix). After short phone talk with
> vendors they explained that detaching and attaching caused dbo alias
> deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> 'dbo' and everithing looks fine with prod db. But its impossible to use
> test
> db. Users that are in sys admin group dont have problem but ordinary users
> that connect to applications db via app user cannot login at all. I wonder
> whats the problem? all ordinary users are stored in users table in prod db
> and have encrypted passwords. app user password is encrypted and stored in
> different db.
> i will be dismissed : )
>
> "Andrew J. Kelly" wrote:
>|||Thanx : ) I hope this will help. Just curios if EE could have the same login
problems. May be will be better to upgrade to EE
"Andrew J. Kelly" wrote:

> Then you should look at the links I posted. In particular this first one
> should be most helpful:
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
>
>|||It would be the same for any edition.
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:B7FD797D-F1DB-41E4-B6D4-0BDA27B27153@.microsoft.com...[vbcol=seagreen]
> Thanx : ) I hope this will help. Just curios if EE could have the same
> login
> problems. May be will be better to upgrade to EE
> "Andrew J. Kelly" wrote:
>

attach detach db

Is it possible detaching one db coping under/ different names /master and lof
files and reattaching db again to cause login problems later. if yes why?Not sure I understand the question but the answer is most likely in one of
these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi,
If you are attaching the database with different name in same server, you
will never get any Login issue. If you are attaching into
a different server then you have syncronize the Logins and users using the
system procedure SP_CHANGE_USERS_LOGIN (see Books online)
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Hari Prasad" wrote:
> Hi,
> If you are attaching the database with different name in same server, you
> will never get any Login issue. If you are attaching into
> a different server then you have syncronize the Logins and users using the
> system procedure SP_CHANGE_USERS_LOGIN (see Books online)
> Thanks
> Hari
> SQL Server MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Andrew J. Kelly" wrote:
> Not sure I understand the question but the answer is most likely in one of
> these:
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Then you should look at the links I posted. In particular this first one
should be most helpful:
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> Hi Hary,
> thanks for your reply. Let me try to explain the situation I am in. I had
> to
> make copy for training purposes of a production db. I detached prod db and
> copied mdf and ldf in the same data directory but with names test.mdf and
> test.ldf. Then I attached our prod db and new test db. During attaching I
> had
> to choose dbowner (domain admin). It seemed to me that i saved time using
> this teqniche. But later users reported that they couldnt log in prod and
> test db via the application (its in citrix). After short phone talk with
> vendors they explained that detaching and attaching caused dbo alias
> deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> 'dbo' and everithing looks fine with prod db. But its impossible to use
> test
> db. Users that are in sys admin group dont have problem but ordinary users
> that connect to applications db via app user cannot login at all. I wonder
> whats the problem? all ordinary users are stored in users table in prod db
> and have encrypted passwords. app user password is encrypted and stored in
> different db.
> i will be dismissed : )
>
> "Andrew J. Kelly" wrote:
>> Not sure I understand the question but the answer is most likely in one
>> of
>> these:
>>
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://www.databasejournal.com/features/mssql/article.php/3379901
>> Moving
>> system DB's
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> > Is it possible detaching one db coping under/ different names /master
>> > and
>> > lof
>> > files and reattaching db again to cause login problems later. if yes
>> > why?
>> >
>>|||Thanx : ) I hope this will help. Just curios if EE could have the same login
problems. May be will be better to upgrade to EE
"Andrew J. Kelly" wrote:
> Then you should look at the links I posted. In particular this first one
> should be most helpful:
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> > Hi Hary,
> > thanks for your reply. Let me try to explain the situation I am in. I had
> > to
> > make copy for training purposes of a production db. I detached prod db and
> > copied mdf and ldf in the same data directory but with names test.mdf and
> > test.ldf. Then I attached our prod db and new test db. During attaching I
> > had
> > to choose dbowner (domain admin). It seemed to me that i saved time using
> > this teqniche. But later users reported that they couldnt log in prod and
> > test db via the application (its in citrix). After short phone talk with
> > vendors they explained that detaching and attaching caused dbo alias
> > deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> > 'dbo' and everithing looks fine with prod db. But its impossible to use
> > test
> > db. Users that are in sys admin group dont have problem but ordinary users
> > that connect to applications db via app user cannot login at all. I wonder
> > whats the problem? all ordinary users are stored in users table in prod db
> > and have encrypted passwords. app user password is encrypted and stored in
> > different db.
> >
> > i will be dismissed : )
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Not sure I understand the question but the answer is most likely in one
> >> of
> >> these:
> >>
> >>
> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> >> http://www.databasejournal.com/features/mssql/article.php/3379901
> >> Moving
> >> system DB's
> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
> >> Servers
> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> >> Databases
> >> to a New Location with Detach/Attach
> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> >> Restore
> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
> >> and
> >> Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> >> after a
> >> Restore
> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> >> users
> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
> >> Permission
> >> Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> >> Issues When a Database Is Moved Between SQL Servers
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >> Restoring a .mdf
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> >> > Is it possible detaching one db coping under/ different names /master
> >> > and
> >> > lof
> >> > files and reattaching db again to cause login problems later. if yes
> >> > why?
> >> >
> >>
> >>
> >>
>
>|||It would be the same for any edition.
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:B7FD797D-F1DB-41E4-B6D4-0BDA27B27153@.microsoft.com...
> Thanx : ) I hope this will help. Just curios if EE could have the same
> login
> problems. May be will be better to upgrade to EE
> "Andrew J. Kelly" wrote:
>> Then you should look at the links I posted. In particular this first one
>> should be most helpful:
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
>> > Hi Hary,
>> > thanks for your reply. Let me try to explain the situation I am in. I
>> > had
>> > to
>> > make copy for training purposes of a production db. I detached prod db
>> > and
>> > copied mdf and ldf in the same data directory but with names test.mdf
>> > and
>> > test.ldf. Then I attached our prod db and new test db. During attaching
>> > I
>> > had
>> > to choose dbowner (domain admin). It seemed to me that i saved time
>> > using
>> > this teqniche. But later users reported that they couldnt log in prod
>> > and
>> > test db via the application (its in citrix). After short phone talk
>> > with
>> > vendors they explained that detaching and attaching caused dbo alias
>> > deleting. They have app login that use dbo alias. I ran sp_addalias
>> > 'app,
>> > 'dbo' and everithing looks fine with prod db. But its impossible to use
>> > test
>> > db. Users that are in sys admin group dont have problem but ordinary
>> > users
>> > that connect to applications db via app user cannot login at all. I
>> > wonder
>> > whats the problem? all ordinary users are stored in users table in prod
>> > db
>> > and have encrypted passwords. app user password is encrypted and stored
>> > in
>> > different db.
>> >
>> > i will be dismissed : )
>> >
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Not sure I understand the question but the answer is most likely in
>> >> one
>> >> of
>> >> these:
>> >>
>> >>
>> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> >> http://www.databasejournal.com/features/mssql/article.php/3379901
>> >> Moving
>> >> system DB's
>> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> >> Servers
>> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> >> Databases
>> >> to a New Location with Detach/Attach
>> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in
>> >> a
>> >> Restore
>> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> >> and
>> >> Passwords Between SQL Servers
>> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> >> after a
>> >> Restore
>> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins
>> >> to
>> >> users
>> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> >> Permission
>> >> Errors After Restoring Dump
>> >> http://www.support.microsoft.com/?id=240872 How to Resolve
>> >> Permission
>> >> Issues When a Database Is Moved Between SQL Servers
>> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> >> Restoring a .mdf
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> >> > Is it possible detaching one db coping under/ different names
>> >> > /master
>> >> > and
>> >> > lof
>> >> > files and reattaching db again to cause login problems later. if yes
>> >> > why?
>> >> >
>> >>
>> >>
>> >>
>>sql

attach db sql in a previous sql server version

Hi,
anyone can tell me if is possible to detach a db from a sql 2000 server and
attach it again into a sql 7.0?
thanks
VTNo, you can not move from 2000 to 7.0 using detach/attach. You can't
use backuo/restore either. As far as I know you have to do it all the
hard way, meaning scripts for the objects and moving the data through
DTS, bulk copy, attached server SELECT/INSERTs, etc..
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 06:06:02 -0700, VT <VT@.discussions.microsoft.com>
wrote:

>Hi,
>anyone can tell me if is possible to detach a db from a sql 2000 server and
>attach it again into a sql 7.0?
>thanks
>VT|||No, not possible to 'just' detach from SQL 2000 and reattach to SQL v7.
If you want to move the tables and data, you might look into DTS to transfer
objects and data.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"VT" <VT@.discussions.microsoft.com> wrote in message
news:C627B49C-91C1-4B17-A0D4-45D3810F6892@.microsoft.com...
> Hi,
> anyone can tell me if is possible to detach a db from a sql 2000 server
> and
> attach it again into a sql 7.0?
> thanks
> VT

attach db sql in a previous sql server version

Hi,
anyone can tell me if is possible to detach a db from a sql 2000 server and
attach it again into a sql 7.0?
thanks
VTNo, you can not move from 2000 to 7.0 using detach/attach. You can't
use backuo/restore either. As far as I know you have to do it all the
hard way, meaning scripts for the objects and moving the data through
DTS, bulk copy, attached server SELECT/INSERTs, etc..
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 06:06:02 -0700, VT <VT@.discussions.microsoft.com>
wrote:
>Hi,
>anyone can tell me if is possible to detach a db from a sql 2000 server and
>attach it again into a sql 7.0?
>thanks
>VT|||No, not possible to 'just' detach from SQL 2000 and reattach to SQL v7.
If you want to move the tables and data, you might look into DTS to transfer
objects and data.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"VT" <VT@.discussions.microsoft.com> wrote in message
news:C627B49C-91C1-4B17-A0D4-45D3810F6892@.microsoft.com...
> Hi,
> anyone can tell me if is possible to detach a db from a sql 2000 server
> and
> attach it again into a sql 7.0?
> thanks
> VT

Attach And Detach Replication

I would like to attach and detach my database due to change the log file
since that logfile already contain too large data.
But the problem occur, It's said that must drop the replication before do
any attach on that data .But i can't drop the replication since i still need
replicate the data from the other server back to local server.
so, anyone can give me any suggestion to do the attach withour drop the
replication?
For a supported solution, you'll need to disable replication before the
detach and attach
(http://msdn2.microsoft.com/en-us/library/ms188769.aspx). Ideally script out
the publication and do a nosync initialization if you can prevent any data
access.
For an unsupported solution that doesn't involve dropping the publication,
search this newsgroup for Ponnu...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
"sam" wrote:

> I would like to attach and detach my database due to change the log file
> since that logfile already contain too large data.
> But the problem occur, It's said that must drop the replication before do
> any attach on that data .But i can't drop the replication since i still need
> replicate the data from the other server back to local server.
> so, anyone can give me any suggestion to do the attach withour drop the
> replication?
>
sql

Attach and Detach DB

Hello,

MSSQLSERVER VER : 2000
Using - Attach and Detach Database to move the database from one Server to another server.
I did detached the database from Production and attached my test server, everything OK

Questions about - syslogins & sysdevices data dictionary tables
1. When I select * from sysdevices on production it shows all the database name size,phyname etc. After attaching to my test server I cannot see these things.. I know because it different master database but, how do I fix this.
2. syslogins - is there way to move all the syslogins after attaching to test server.

Please let me know if you guys have done anything on this.

Thanks.

Regards,
K.You can use the DTS Transfer Logins task to transfer logins from one server to another (an option allows you to narrow the selection of users to be moved to one or more databases).

You will still have to use sp_change_users_login (see BOL) to "link" the database user to the new login (the proc links the SID of the user to the SID of the login since the new login will have a different SID than the one on the old server).

There is also a white paper from MS on SQL Log shipping that specifies how to use BCP to transfer the syslogins table, but I don't think you need that.

Regards,

hmscott|||Thanks, I will try let you know.

Regards,
K.

attach / detach replicated databases

Hi,
How do we attach and detach databases participating in
replication?
regards
bharathBy removing replication from it first.
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:e35d01c40fbb$e6ef4d00$a601280a@.phx.gbl...
> Hi,
> How do we attach and detach databases participating in
> replication?
> regards
> bharathsql

attach / detach newbie question

I've looked at all the microsoft articles relating to moving a database from
one location to another using the attach/detach commands and I have one
question. Are those commands typed from a command prompt or do I need to run
these from SQL manager?
Hi,
You can execute those commands from
1. Selecting Query Analyzer from SQL Program groups. (issue the user name
and password to login)
Once you go inside the Query analyzer you could execute all TSQL
commands which includes the detach and attach db commands.
2. FROM command prompt execute OSQL
OSQL -Usa -Ppassword -S <SQL Servername> (enter)
This will go to a SQL prompt; there execute those sp_detach_db and
sp_attach_db commands.
Thanks
Hari
SQL Server MVP
"JL Adamson" <JLAdamson@.discussions.microsoft.com> wrote in message
news:8886FE74-93B2-4FD9-997F-25884FA0F953@.microsoft.com...
> I've looked at all the microsoft articles relating to moving a database
> from
> one location to another using the attach/detach commands and I have one
> question. Are those commands typed from a command prompt or do I need to
> run
> these from SQL manager?

attach / detach newbie question

I've looked at all the microsoft articles relating to moving a database from
one location to another using the attach/detach commands and I have one
question. Are those commands typed from a command prompt or do I need to run
these from SQL manager?Hi,
You can execute those commands from
1. Selecting Query Analyzer from SQL Program groups. (issue the user name
and password to login)
Once you go inside the Query analyzer you could execute all TSQL
commands which includes the detach and attach db commands.
2. FROM command prompt execute OSQL
OSQL -Usa -Ppassword -S <SQL Servername> (enter)
This will go to a SQL prompt; there execute those sp_detach_db and
sp_attach_db commands.
Thanks
Hari
SQL Server MVP
"JL Adamson" <JLAdamson@.discussions.microsoft.com> wrote in message
news:8886FE74-93B2-4FD9-997F-25884FA0F953@.microsoft.com...
> I've looked at all the microsoft articles relating to moving a database
> from
> one location to another using the attach/detach commands and I have one
> question. Are those commands typed from a command prompt or do I need to
> run
> these from SQL manager?

attach / detach newbie question

I've looked at all the microsoft articles relating to moving a database from
one location to another using the attach/detach commands and I have one
question. Are those commands typed from a command prompt or do I need to run
these from SQL manager?Hi,
You can execute those commands from
1. Selecting Query Analyzer from SQL Program groups. (issue the user name
and password to login)
Once you go inside the Query analyzer you could execute all TSQL
commands which includes the detach and attach db commands.
2. FROM command prompt execute OSQL
OSQL -Usa -Ppassword -S <SQL Servername> (enter)
This will go to a SQL prompt; there execute those sp_detach_db and
sp_attach_db commands.
Thanks
Hari
SQL Server MVP
"JL Adamson" <JLAdamson@.discussions.microsoft.com> wrote in message
news:8886FE74-93B2-4FD9-997F-25884FA0F953@.microsoft.com...
> I've looked at all the microsoft articles relating to moving a database
> from
> one location to another using the attach/detach commands and I have one
> question. Are those commands typed from a command prompt or do I need to
> run
> these from SQL manager?

Attach & Detach Multiple Databases

SQL Server 2000 Standard
I need to move multiple databases (about 20) from the C partition to the E
partition. I'm familiar with sp_detach_db and sp_attach_db scripts and is OK
for a handful of databases but tedious with 10 or more databases. My manager
suggested "looping" or something like that. In other words he wants a method
to move these databases in an efficient script. I looked into using SSEUTIL
but that gives me a "mssql$sqlexpress is not installed" error message,
looking for SQL Server Express 2005. I'm sure what I'm asking isn't something
new.check out master.dbo.sysaltfiles for file paths. then you can dynamically
create the detach/attach scripts using sysdatabases and sysaltfiles.
think like this:
select 'sp_detach ... ' + name + '
go'
from sysdatabases
I use such constructs all the time to create scripts to execute.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"JosephV" <JosephV@.discussions.microsoft.com> wrote in message
news:AEF01BE7-44DF-4DEC-90B1-285AEDA95069@.microsoft.com...
> SQL Server 2000 Standard
> I need to move multiple databases (about 20) from the C partition to the E
> partition. I'm familiar with sp_detach_db and sp_attach_db scripts and is
> OK
> for a handful of databases but tedious with 10 or more databases. My
> manager
> suggested "looping" or something like that. In other words he wants a
> method
> to move these databases in an efficient script. I looked into using
> SSEUTIL
> but that gives me a "mssql$sqlexpress is not installed" error message,
> looking for SQL Server Express 2005. I'm sure what I'm asking isn't
> something
> new.|||Thanks. I'll research your suggestion.
"TheSQLGuru" wrote:
> check out master.dbo.sysaltfiles for file paths. then you can dynamically
> create the detach/attach scripts using sysdatabases and sysaltfiles.
> think like this:
> select 'sp_detach ... ' + name + '
> go'
> from sysdatabases
> I use such constructs all the time to create scripts to execute.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "JosephV" <JosephV@.discussions.microsoft.com> wrote in message
> news:AEF01BE7-44DF-4DEC-90B1-285AEDA95069@.microsoft.com...
> > SQL Server 2000 Standard
> >
> > I need to move multiple databases (about 20) from the C partition to the E
> > partition. I'm familiar with sp_detach_db and sp_attach_db scripts and is
> > OK
> > for a handful of databases but tedious with 10 or more databases. My
> > manager
> > suggested "looping" or something like that. In other words he wants a
> > method
> > to move these databases in an efficient script. I looked into using
> > SSEUTIL
> > but that gives me a "mssql$sqlexpress is not installed" error message,
> > looking for SQL Server Express 2005. I'm sure what I'm asking isn't
> > something
> > new.
>
>|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
"JosephV" <JosephV@.discussions.microsoft.com> wrote in message
news:A173C0D1-AB94-4BEA-AC8A-F81E88744A6F@.microsoft.com...
> Thanks. I'll research your suggestion.
> "TheSQLGuru" wrote:
>> check out master.dbo.sysaltfiles for file paths. then you can
>> dynamically
>> create the detach/attach scripts using sysdatabases and sysaltfiles.
>> think like this:
>> select 'sp_detach ... ' + name + '
>> go'
>> from sysdatabases
>> I use such constructs all the time to create scripts to execute.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "JosephV" <JosephV@.discussions.microsoft.com> wrote in message
>> news:AEF01BE7-44DF-4DEC-90B1-285AEDA95069@.microsoft.com...
>> > SQL Server 2000 Standard
>> >
>> > I need to move multiple databases (about 20) from the C partition to
>> > the E
>> > partition. I'm familiar with sp_detach_db and sp_attach_db scripts and
>> > is
>> > OK
>> > for a handful of databases but tedious with 10 or more databases. My
>> > manager
>> > suggested "looping" or something like that. In other words he wants a
>> > method
>> > to move these databases in an efficient script. I looked into using
>> > SSEUTIL
>> > but that gives me a "mssql$sqlexpress is not installed" error message,
>> > looking for SQL Server Express 2005. I'm sure what I'm asking isn't
>> > something
>> > new.
>>

Attach & Detach Multiple Databases

SQL Server 2000 Standard
I need to move multiple databases (about 20) from the C partition to the E
partition. I'm familiar with sp_detach_db and sp_attach_db scripts and is OK
for a handful of databases but tedious with 10 or more databases. My manager
suggested "looping" or something like that. In other words he wants a method
to move these databases in an efficient script. I looked into using SSEUTIL
but that gives me a "mssql$sqlexpress is not installed" error message,
looking for SQL Server Express 2005. I'm sure what I'm asking isn't something
new.
check out master.dbo.sysaltfiles for file paths. then you can dynamically
create the detach/attach scripts using sysdatabases and sysaltfiles.
think like this:
select 'sp_detach ... ' + name + '
go'
from sysdatabases
I use such constructs all the time to create scripts to execute.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"JosephV" <JosephV@.discussions.microsoft.com> wrote in message
news:AEF01BE7-44DF-4DEC-90B1-285AEDA95069@.microsoft.com...
> SQL Server 2000 Standard
> I need to move multiple databases (about 20) from the C partition to the E
> partition. I'm familiar with sp_detach_db and sp_attach_db scripts and is
> OK
> for a handful of databases but tedious with 10 or more databases. My
> manager
> suggested "looping" or something like that. In other words he wants a
> method
> to move these databases in an efficient script. I looked into using
> SSEUTIL
> but that gives me a "mssql$sqlexpress is not installed" error message,
> looking for SQL Server Express 2005. I'm sure what I'm asking isn't
> something
> new.
|||Thanks. I'll research your suggestion.
"TheSQLGuru" wrote:

> check out master.dbo.sysaltfiles for file paths. then you can dynamically
> create the detach/attach scripts using sysdatabases and sysaltfiles.
> think like this:
> select 'sp_detach ... ' + name + '
> go'
> from sysdatabases
> I use such constructs all the time to create scripts to execute.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "JosephV" <JosephV@.discussions.microsoft.com> wrote in message
> news:AEF01BE7-44DF-4DEC-90B1-285AEDA95069@.microsoft.com...
>
>
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
"JosephV" <JosephV@.discussions.microsoft.com> wrote in message
news:A173C0D1-AB94-4BEA-AC8A-F81E88744A6F@.microsoft.com...[vbcol=seagreen]
> Thanks. I'll research your suggestion.
> "TheSQLGuru" wrote:
sql