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\MSSQL\Data\MyDatabase.mdf') LOG ON (NAME = MyDatabase_log,
FileName = 'D:\Database\MSSQL\Data\MyDatabase.ldf') COLLATE
SQL_Latin1_General_CP1_CI_AS

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

RESTORE FILELISTONLY
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'

restore database MyDatabase
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'
with REPLACE,
MOVE 'SomeOtherRubbish_Data' TO
'D:\Database\MSSQL\Data\MyDatabase.mdf',
MOVE 'SomeOtherRubbish_Log' TO
'D:\Database\MSSQL\Data\MyDatabase.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]

Ryan (ry********@hotmail.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, es****@sommarskog.se

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

[Ryan]

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 (ry********@hotmail.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, es****@sommarskog.se

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