Tuesday, February 21, 2012

Restore - Change Logical Name

I am trying to restore a database and change the database logical name.
I the following error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Logical file 'TestOAISys' is not part of database 'TestOAISys'. Use RESTORE
FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.
--
Basically, the logical filename was POAIAPP for our TestOAISys database
(which is also the Logical Name of our equivalent Production Database). I
want to change the logical name of the TEST database to TestOAIApp and I wan
t
to be able to put the Test and the Production databases on the same drives.
Can anyone help?
Thanks,
LeslieYou want to use the WITH MOVE option:
RESTORE DATABASE TestOAIApp
FROM DISK = 'POAIAPP.bak'
WITH MOVE 'POAIApp.mdf' = 'C:\...\TestOAIapp.mdf',
'POAIApp.ldf' = 'C:\...\TestOAIapp.ldf'
HTH,
Stu|||Thanks Stu,
I will give this a try. Will this give the new database the logical name of
TestOAIApp?
Leslie
"Stu" wrote:

> You want to use the WITH MOVE option:
> RESTORE DATABASE TestOAIApp
> FROM DISK = 'POAIAPP.bak'
> WITH MOVE 'POAIApp.mdf' = 'C:\...\TestOAIapp.mdf',
> 'POAIApp.ldf' = 'C:\...\TestOAIapp.ldf'
>
> HTH,
> Stu
>|||Hi Leslie
Databases don't have logical names. They just have names. The files the
databases are created on have both logical and physical names.
By default, if you create a database named test without specifying any file
names, the logical filenames will be test and test_log, and the physical
names will be test.mdf and test_log.ldf.
To change the database name, you can use sp_renamedb. That will not change
either the logical or physical file names.
To change the physical file name, perhaps also including the directory, you
need to either detach or backup the db, and then attach or restore with the
new file names explicitly specified.
To change the logical file names, you can use ALTER DATABASE in SQL Server
2000 or 2005. It is not possible to change the logical filenames in SQL
Server 7
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:4623E802-EE8C-4736-A9C9-E35D42C78241@.microsoft.com...
>I am trying to restore a database and change the database logical name.
> I the following error:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Logical file 'TestOAISys' is not part of database 'TestOAISys'. Use
> RESTORE
> FILELISTONLY to list the logical file names.
> RESTORE DATABASE is terminating abnormally.
> --
> Basically, the logical filename was POAIAPP for our TestOAISys database
> (which is also the Logical Name of our equivalent Production Database). I
> want to change the logical name of the TEST database to TestOAIApp and I
> want
> to be able to put the Test and the Production databases on the same
> drives.
> Can anyone help?
> Thanks,
> Leslie
>|||Karen,
Thank you for your response. It helped clarify some of the nomenclature.
In your last paragraph, you note it is possible to alter the database logica
l
file name in SQL2000 and SQL2005. We are running SQL 2000 version 8.00.760
.
Can you tell me the syntax for changing the logical file name using ALTER
DATABASE?
DB Names DB Log. FileName DB Physical Names
OAIApp POAIAPP POAIAPP.mdf
TestOAIApp POAIAPP TestOAIApp.mdf
Thanks,
Leslie
"Kalen Delaney" wrote:
> To change the logical file names, you can use ALTER DATABASE in SQL Server
> 2000 or 2005. It is not possible to change the logical filenames in SQL
> Server 7
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com|||Try:
alter database TestOAIApp
modify file
(
name = POAIAPP
, newname = MyNewPOAIAPP
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:046EE9E1-91BC-41F1-8F0C-3899B1F71299@.microsoft.com...
> Karen,
> Thank you for your response. It helped clarify some of the nomenclature.
> In your last paragraph, you note it is possible to alter the database
> logical
> file name in SQL2000 and SQL2005. We are running SQL 2000 version
> 8.00.760.
> Can you tell me the syntax for changing the logical file name using ALTER
> DATABASE?
> DB Names DB Log. FileName DB Physical Names
> OAIApp POAIAPP POAIAPP.mdf
> TestOAIApp POAIAPP TestOAIApp.mdf
> Thanks,
> Leslie
> "Kalen Delaney" wrote:
>|||Tom,
This did it! I ran the SELECT FILE_NAME() command for the .mdf and the .ldf
exact logical filenames before running the modify. THANK YOU very much for
the syntax!
Leslie
"Tom Moreau" wrote:

> Try:
> alter database TestOAIApp
> modify file
> (
> name = POAIAPP
> , newname = MyNewPOAIAPP
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:046EE9E1-91BC-41F1-8F0C-3899B1F71299@.microsoft.com...
>
>|||Hi Leslie
Please check the Books Online for the complete ALTER DATABASE syntax. There
are LOTS of things you can do with this command.
HTH
Kalen (not Karen) Delaney, SQL Server MVP
www.solidqualitylearning.com
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:DEF3D2B2-DD05-4E0F-BEE1-E0A0CB1744E8@.microsoft.com...
> Tom,
> This did it! I ran the SELECT FILE_NAME() command for the .mdf and the
> .ldf
> exact logical filenames before running the modify. THANK YOU very much
> for
> the syntax!
> Leslie
> "Tom Moreau" wrote:
>
>|||Kalen (with an "L"),
Sorry for calling you Karen (my sister's name), I mis-read your name when I
responded originally. Apologies.
Yes, the ALTER DATABASE command worked fine as Tom Moreau noted in his
response. I actually did look up the ALTER DATABASE command, but was
on how the exact syntax was supposed to be written, and Tom gave a
good working example.
Thanks again for your responses.
Cheers,
Leslie

> Please check the Books Online for the complete ALTER DATABASE syntax. Ther
e
> are LOTS of things you can do with this command.
> HTH
> Kalen (not Karen) Delaney, SQL Server MVP

No comments:

Post a Comment