changing logical filename

I have a database called test. it has 2 logical files : test_data,
test_log. they are in the following physical files: test_data.mdf,
test_log.ldf.
I make a backup of the database test.
I wish to make a copy of the test database with a different name on teh same
machine. I want to end up with a databse called test_new which has
test_new_data and test_new_log as logical files. It ahsould then have
test_new_data.mdf and test_new_log.ldf for physical files. I want to do
this using a dump fro the test database.
Does anyone know how to do this ? The reason for doing it this way is that
on one of my servers i created the copy of est databse called test_new. On
that test_new databse i had logical files of test_data and test_log. but
the physical files were called test_new_data.mdf and test_new_log.ldf.
since teh logical file names were the same there seems to be a problem in
which in order for me to access the test_new database, i must give access to
the test database as well as the test_new database to an id. otherwise i
get an access violation.
Any insight into this problem or my proprsed solution would be greatly
appreciated.
AronCreate a new database test_new, then
when you will restore data from backup over this database
either use MOVE optin in RESTORE DATABASE command in QA
or choose Device names and paths on Options tab in EM
How to restore a database with a new name (Transact-SQL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
Regards.
"Aron E. Tekulsky" wrote:
> I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is that
> on one of my servers i created the copy of est databse called test_new. On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>
>|||One way to deal with it is to restore the db into a different directory than
the original. The other is to use the WITH MOVE option of RESTORE to get a
new physical name. You can rename the Logical names with ALTER DATABASE
after you restore it.
--
Andrew J. Kelly SQL MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:uaQkgQiYFHA.2768@.tk2msftngp13.phx.gbl...
>I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh
> same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is
> that
> on one of my servers i created the copy of est databse called test_new.
> On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access
> to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>|||Thanks for the information. But that does not give the desired result. I
will still have logical file names that are the same as teh original
database. I am looking for a way to have diffrent logical file names as
well.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> Create a new database test_new, then
> when you will restore data from backup over this database
> either use MOVE optin in RESTORE DATABASE command in QA
> or choose Device names and paths on Options tab in EM
> How to restore a database with a new name (Transact-SQL)
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
>
> Regards.
> "Aron E. Tekulsky" wrote:
> > I have a database called test. it has 2 logical files : test_data,
> > test_log. they are in the following physical files: test_data.mdf,
> > test_log.ldf.
> > I make a backup of the database test.
> >
> > I wish to make a copy of the test database with a different name on teh
same
> > machine. I want to end up with a databse called test_new which has
> > test_new_data and test_new_log as logical files. It ahsould then have
> > test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> > this using a dump fro the test database.
> >
> > Does anyone know how to do this ? The reason for doing it this way is
that
> > on one of my servers i created the copy of est databse called test_new.
On
> > that test_new databse i had logical files of test_data and test_log.
but
> > the physical files were called test_new_data.mdf and test_new_log.ldf.
> > since teh logical file names were the same there seems to be a problem
in
> > which in order for me to access the test_new database, i must give
access to
> > the test database as well as the test_new database to an id. otherwise
i
> > get an access violation.
> >
> > Any insight into this problem or my proprsed solution would be greatly
> > appreciated.
> > Aron
> >
> >
> >|||As Andrew mentioned already you can rename logical name with ALTER DATABASE
after it will restored.
Regards.
"Aron E. Tekulsky" wrote:
> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> > Create a new database test_new, then
> > when you will restore data from backup over this database
> > either use MOVE optin in RESTORE DATABASE command in QA
> > or choose Device names and paths on Options tab in EM
> >
> > How to restore a database with a new name (Transact-SQL)
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
> >
> >
> > Regards.
> >
> > "Aron E. Tekulsky" wrote:
> >
> > > I have a database called test. it has 2 logical files : test_data,
> > > test_log. they are in the following physical files: test_data.mdf,
> > > test_log.ldf.
> > > I make a backup of the database test.
> > >
> > > I wish to make a copy of the test database with a different name on teh
> same
> > > machine. I want to end up with a databse called test_new which has
> > > test_new_data and test_new_log as logical files. It ahsould then have
> > > test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> > > this using a dump fro the test database.
> > >
> > > Does anyone know how to do this ? The reason for doing it this way is
> that
> > > on one of my servers i created the copy of est databse called test_new.
> On
> > > that test_new databse i had logical files of test_data and test_log.
> but
> > > the physical files were called test_new_data.mdf and test_new_log.ldf.
> > > since teh logical file names were the same there seems to be a problem
> in
> > > which in order for me to access the test_new database, i must give
> access to
> > > the test database as well as the test_new database to an id. otherwise
> i
> > > get an access violation.
> > >
> > > Any insight into this problem or my proprsed solution would be greatly
> > > appreciated.
> > > Aron
> > >
> > >
> > >
>
>|||Hi,
For changing the Logical file name see the below command:-
ALTER DATABASE <DBNAME> modify file (NAME = 'old_MDF_NAME', NEWNAME ='NEW_MDF_NAME')
do the same for LDF file as well.
Note:
We can not change the logical file name in SQL 7. The command will only work
in SQL 2000.
Thanks
Hari
SQL Server MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:%23BXP2giYFHA.796@.TK2MSFTNGP10.phx.gbl...
> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
>> Create a new database test_new, then
>> when you will restore data from backup over this database
>> either use MOVE optin in RESTORE DATABASE command in QA
>> or choose Device names and paths on Options tab in EM
>> How to restore a database with a new name (Transact-SQL)
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
>>
>> Regards.
>> "Aron E. Tekulsky" wrote:
>> > I have a database called test. it has 2 logical files : test_data,
>> > test_log. they are in the following physical files: test_data.mdf,
>> > test_log.ldf.
>> > I make a backup of the database test.
>> >
>> > I wish to make a copy of the test database with a different name on teh
> same
>> > machine. I want to end up with a databse called test_new which has
>> > test_new_data and test_new_log as logical files. It ahsould then have
>> > test_new_data.mdf and test_new_log.ldf for physical files. I want to
>> > do
>> > this using a dump fro the test database.
>> >
>> > Does anyone know how to do this ? The reason for doing it this way is
> that
>> > on one of my servers i created the copy of est databse called
>> > test_new.
> On
>> > that test_new databse i had logical files of test_data and test_log.
> but
>> > the physical files were called test_new_data.mdf and test_new_log.ldf.
>> > since teh logical file names were the same there seems to be a problem
> in
>> > which in order for me to access the test_new database, i must give
> access to
>> > the test database as well as the test_new database to an id. otherwise
> i
>> > get an access violation.
>> >
>> > Any insight into this problem or my proprsed solution would be greatly
>> > appreciated.
>> > Aron
>> >
>> >
>> >
>