Detaching & Attaching Database

Hello Group.
I have the following problem using SQL Server 2000 on WinXP machine.
I detached a SQL database and then performed a windows copy of the database
to another folder on the system.
I then renamed the database and tried to attach the renamed database to SQL.
SQL sees the old name and says there is already a database with that name
attached.
How can I solve this problem?
How can I get SQL to see the new name given to the MDF and LDF files?
Thanks,
Terry"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>
Renaming the MDF / LDF files has nothing to do with renaming the database.
The name for the database when it is attached is specified by the first
parameter of sp_attach_db. Example:
EXEC sp_attach_db 'new_db_name',
'd:\mssql\data\db_file_name.mdf',
'd:\mssql\data\db_file_name.ldf'
David Portas
SQL Server MVP
--|||Hi Terry
The name of the database does not have to have anything to do with the names
of the database files. So when you say you renamed 'the database' do you
mean you renamed the files you moved, or renamed the database on the server?
The original name of the database is stored with the files, however, even if
you change the name of the files. If you want to attach some database files
and give the database contained on those files another name than the one it
originally had, you have to specify that when you do the attach.
I suggest you use the command sp_attach_db, following the examples in the
Books Online. This will allow you to specify the name of the database you
are attaching, and the physical names of all the files you want to attach.
If you still are unsuccessful, you can then post the exact command you are
using and the exact error message you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>|||Okay, but how do I accomplish this with enterprise manager?
In the dialog box that appears when you go to attach the database, it gives
you the orginal database name.
In the box "attach as" I give it the new name and get the error that it
already exits.
What am I missing here?
Thanks, for your quick response.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
> Renaming the MDF / LDF files has nothing to do with renaming the database.
> The name for the database when it is attached is specified by the first
> parameter of sp_attach_db. Example:
> EXEC sp_attach_db 'new_db_name',
> 'd:\mssql\data\db_file_name.mdf',
> 'd:\mssql\data\db_file_name.ldf'
> --
> David Portas
> SQL Server MVP
> --
>|||Okay, your statement
"The original name of the database is stored with the files" explains what I
was missing.
Now I understand. I did not know this.
Thanks,
Kalen
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Edin%23KFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hi Terry
> The name of the database does not have to have anything to do with the
> names of the database files. So when you say you renamed 'the database' do
> you mean you renamed the files you moved, or renamed the database on the
> server?
> The original name of the database is stored with the files, however, even
> if you change the name of the files. If you want to attach some database
> files and give the database contained on those files another name than the
> one it originally had, you have to specify that when you do the attach.
> I suggest you use the command sp_attach_db, following the examples in the
> Books Online. This will allow you to specify the name of the database you
> are attaching, and the physical names of all the files you want to attach.
> If you still are unsuccessful, you can then post the exact command you are
> using and the exact error message you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
>
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
I don't know about accomplishing this in Enterprise Manager because I rarely
use EM that way. In my opinion it would be much easier to type the
sp_attach_db command in Query Analyzer.
David Portas
SQL Server MVP
--|||Okay,
Thanks for your help.
Much appreciated.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eEqu3XLFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> I don't know about accomplishing this in Enterprise Manager because I
> rarely use EM that way. In my opinion it would be much easier to type the
> sp_attach_db command in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>