Change Logical Name in Script

Giganews Newsgroups
Subject: Change Logical Name in Script
Posted by:  Ryan (ryanoffo…@hotmail.com)
Date: 9 Aug 2006

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

Replies