Change Logical Name in Script

[Ryan]

I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.

So, when I use the script below... I've had to work out the Logical
name for the data and the log file and alter the script accordingly.

Creation
------------

Create Database MyDatabase ON (NAME = MyDatabase_data , FileName =
'D:\Database\MS SQL\Data\MyData base.mdf') LOG ON (NAME = MyDatabase_log,
FileName = 'D:\Database\MS SQL\Data\MyData base.ldf') COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS

Restore
-----------

RESTORE FILELISTONLY
from disk =
'D:\Database\MS SQL\BACKUP\2006-08-07\MyDatabase_d b_200608072100. BAK'

restore database MyDatabase
from disk =
'D:\Database\MS SQL\BACKUP\2006-08-07\MyDatabase_d b_200608072100. BAK'
with REPLACE,
MOVE 'SomeOtherRubbi sh_Data' TO
'D:\Database\MS SQL\Data\MyData base.mdf',
MOVE 'SomeOtherRubbi sh_Log' TO
'D:\Database\MS SQL\Data\MyData base.ldf'
go

When I then look at the properties of the database, it shows the old
convention which I don't want.

So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?

Oh, Yes I know I added the collation when creating the database, but
that's another thing that we need to address at some point.


Thanks in advance

[Erland Sommarskog]

Re: Change Logical Name in Script

Ryan (ryanofford@hot mail.com) writes:

I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.

If I understand this correctly, you first run CREATE DATABASE for a
database, and then RESTORE for the same database for you. I've bad news
for you: the script for CREATE DATABASE was in vein. RESTORE will create
the database if it does not exist. And if it exists, it will throw the
old one away.

So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?

ALTER DATBASE db MODIFY FILE oldname NAME = oldname, NEWNAME = newname


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ryan]

Re: Change Logical Name in Script

Thanks Erland. I'll give it a try. No major issue on not using the
create script, but thanks for the advice on that.

Ryan

Erland Sommarskog wrote:

Ryan (ryanofford@hot mail.com) writes:

I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.

>
If I understand this correctly, you first run CREATE DATABASE for a
database, and then RESTORE for the same database for you. I've bad news
for you: the script for CREATE DATABASE was in vein. RESTORE will create
the database if it does not exist. And if it exists, it will throw the
old one away.
>

So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?

>
ALTER DATBASE db MODIFY FILE oldname NAME = oldname, NEWNAME = newname
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx