Confused Copying a Database on Same Server

Question

Confused Copying a Database on Same Server

JC-3113

SSCrazy Eights

Points: 8371
More actions
August 20, 2009 at 2:24 pm

#203989

Hi Folks
I made a copy of the Northwind database yesterday and called it comvault_test
I checked in SSMS and it was there
I ran a two scripts to check the name
One showed it there and the other did not
I then noticed that the database_id was 7 for the new database so i ran the scripts again
That is when I noticed that the new database mdf and ldf files had the copied database mdf and ldf file names
but on the server, they had the correct names of comvault_test.mdf and comvault_test.ldf
so I am confused
==================
use MASTER;
select
CAST(name as varchar(30)) logical_name,
database_id
from
master.sys.master_files
where
name like 'com%';
go
output:
logical_name database_id
------------------------------ -----------
(0 rows affected)
select
CAST(name as varchar(30)) logical_name,
database_id
from
sys.databases
where
name like 'com%';
go
output:
logical_name database_id
------------------------------ -----------
Comvault_Test 7
(1 rows affected) 7
=============
select
CAST(name as varchar(30)) logical_name,
database_id
from
master.sys.master_files
where
database_id=7
go
output:
logical_name database_id
------------------------------ -----------
Northwind 7
Northwind_log 7
(2 rows affected)
select
CAST(name as varchar(30)) logical_name,
database_id
from
sys.databases
where
database_id=7
go
output:
logical_name database_id
------------------------------ -----------
Comvault_Test 7
(1 rows affected) 7
can someone explain to me why the copied database shows the filenames from Northwind in the database but on the server they are correct ?
thanks
jim

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

What method did you use to copy the database?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

HI GSquared

I uses SSMS Copy Database Wizard

Databases>Northwind>Tasks>Copy Database

Jim

GSquared SSC Guru Points: 260824 More actions · Answer 3

In that case, it's just the logical names for the files, not the physical names, so it doesn't matter.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Hi GSquared

is there a way to change the logical names to reflect the physical name ?

Thanks

jim

GSquared SSC Guru Points: 260824 More actions · Answer 5

ALTER DATABASE dbname MODIFY FILE ( NAME = currentname, NEWNAME = newname )

All that kind of stuff comes under Alter Database in Books Online.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

JC-3113

SSCrazy Eights

Points: 8371

August 26, 2009 at 9:24 am

#1044957

Thanks GSquared

I will give it a try

Jim