Hi,
I have a sql server 7 running on a machine with two disk partitions (D: and E:).
The data files xxx.mdf and xxx.ldf are stored in D:, which has very few space available. I want to copy these files to E: but I get an error saying that it is not possible to change the source file of a database. Is it possible to do it or do i have to create another data file in E: and keep the old one in D:?
Thanks in advance,
browsersp_detach_db + sp_attach_db ... with (...)|||detach the database ... copy to new location ... then attach
or
backup the database ... restore ... sp_renamedb|||thanks ms_sql_dba and Enigma, it worked properly!sql
Showing posts with label ldf. Show all posts
Showing posts with label ldf. Show all posts
Thursday, March 22, 2012
Friday, February 24, 2012
Changing Logical Names
This is something I've always wondered. I know how to change the actual nam
e
of the mdf and ldf files on disk. You just detach, change the names on the
disk and reattach with the new names. But how do you change the logical
names of the mdf and ldf files, i.e. the names that you use when you do a
restore with move'CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual n
ame
> of the mdf and ldf files on disk. You just detach, change the names on th
e
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'|||CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual n
ame
> of the mdf and ldf files on disk. You just detach, change the names on th
e
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'
e
of the mdf and ldf files on disk. You just detach, change the names on the
disk and reattach with the new names. But how do you change the logical
names of the mdf and ldf files, i.e. the names that you use when you do a
restore with move'CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual n
ame
> of the mdf and ldf files on disk. You just detach, change the names on th
e
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'|||CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual n
ame
> of the mdf and ldf files on disk. You just detach, change the names on th
e
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'
Changing Logical Names
This is something I've always wondered. I know how to change the actual name
of the mdf and ldf files on disk. You just detach, change the names on the
disk and reattach with the new names. But how do you change the logical
names of the mdf and ldf files, i.e. the names that you use when you do a
restore with move'CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual name
> of the mdf and ldf files on disk. You just detach, change the names on the
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'
of the mdf and ldf files on disk. You just detach, change the names on the
disk and reattach with the new names. But how do you change the logical
names of the mdf and ldf files, i.e. the names that you use when you do a
restore with move'CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual name
> of the mdf and ldf files on disk. You just detach, change the names on the
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'
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.
Aron
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/de...ackpc_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/de...ackpc_6ng9.asp[vbcol=seagreen]
>
> Regards.
> "Aron E. Tekulsky" wrote:
same[vbcol=seagreen]
that[vbcol=seagreen]
On[vbcol=seagreen]
but[vbcol=seagreen]
in[vbcol=seagreen]
access to[vbcol=seagreen]
i[vbcol=seagreen]
|||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...
> http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
>
|||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...
> http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
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
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/de...ackpc_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/de...ackpc_6ng9.asp[vbcol=seagreen]
>
> Regards.
> "Aron E. Tekulsky" wrote:
same[vbcol=seagreen]
that[vbcol=seagreen]
On[vbcol=seagreen]
but[vbcol=seagreen]
in[vbcol=seagreen]
access to[vbcol=seagreen]
i[vbcol=seagreen]
|||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...
> http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
>
|||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...
> http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
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
>> >
>> >
>> >
>
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
>> >
>> >
>> >
>
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)
kpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...>
kpc_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 sa
me
> 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 tha
t
> on one of my servers i created the copy of est databse called test_new. O
n
> 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)
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp[/ur
l][vbcol=seagreen]
>
> Regards.
> "Aron E. Tekulsky" wrote:
>
same[vbcol=seagreen]
that[vbcol=seagreen]
On[vbcol=seagreen]
but[vbcol=seagreen]
in[vbcol=seagreen]
access to[vbcol=seagreen]
i[vbcol=seagreen]|||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...
> ackpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
>|||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...
> ackpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
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)
kpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...>
kpc_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 sa
me
> 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 tha
t
> on one of my servers i created the copy of est databse called test_new. O
n
> 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)
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp[/ur
l][vbcol=seagreen]
>
> Regards.
> "Aron E. Tekulsky" wrote:
>
same[vbcol=seagreen]
that[vbcol=seagreen]
On[vbcol=seagreen]
but[vbcol=seagreen]
in[vbcol=seagreen]
access to[vbcol=seagreen]
i[vbcol=seagreen]|||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...
> ackpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
>|||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...
> ackpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
Subscribe to:
Posts (Atom)