RESTORE as New Database

Hello!
I have a question regarding the "LogicalName" when restoring a backup as an
entirely different database.
I run this:
USE master
GO
RESTORE FILELISTONLY
FROM DISK='C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
RESTORE DATABASE MyNewDatabase
FROM DISK= 'C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
WITH RECOVERY,
MOVE 'MyOriginalDatabase_data' TO 'C:\Program
Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_data.mdf',
MOVE 'MyOriginalDatabase_log' TO 'C:\Program
Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_log.ldf'
GO
And my PhysicalNames are correct for the new database but the LogicalNames
remain the same as the original database. Is this correct?
I cannot have the new database have any ties/links to the original database.
Is the TSQL incorrect above or can I "rename" the LogicalName for the new
database?
Thank you in advance for your time.Carol
Yep, you can change a logical name file of the database.
What is your concernment?
"Carol" <pooch@.hotmail.com> wrote in message
news:uV8zua2BEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a question regarding the "LogicalName" when restoring a backup as
an
> entirely different database.
> I run this:
> USE master
> GO
> RESTORE FILELISTONLY
> FROM DISK='C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
> RESTORE DATABASE MyNewDatabase
> FROM DISK= 'C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
> WITH RECOVERY,
> MOVE 'MyOriginalDatabase_data' TO 'C:\Program
> Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_data.mdf',
> MOVE 'MyOriginalDatabase_log' TO 'C:\Program
> Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_log.ldf'
> GO
> And my PhysicalNames are correct for the new database but the LogicalNames
> remain the same as the original database. Is this correct?
> I cannot have the new database have any ties/links to the original
database.
> Is the TSQL incorrect above or can I "rename" the LogicalName for the new
> database?
> Thank you in advance for your time.
>|||Hi,
1. And my PhysicalNames are correct for the new database but the
LogicalNames remain the same as the original database. Is this correct?
Yes , You can do that.
2. Is the TSQL incorrect above or can I "rename" the LogicalName for the new
database?
It is not required to rename the logical file name. Only physical name
need to be changed.
Thanks
Hari
"Carol" <pooch@.hotmail.com> wrote in message
news:uV8zua2BEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a question regarding the "LogicalName" when restoring a backup as
an
> entirely different database.
> I run this:
> USE master
> GO
> RESTORE FILELISTONLY
> FROM DISK='C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
> RESTORE DATABASE MyNewDatabase
> FROM DISK= 'C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
> WITH RECOVERY,
> MOVE 'MyOriginalDatabase_data' TO 'C:\Program
> Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_data.mdf',
> MOVE 'MyOriginalDatabase_log' TO 'C:\Program
> Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_log.ldf'
> GO
> And my PhysicalNames are correct for the new database but the LogicalNames
> remain the same as the original database. Is this correct?
> I cannot have the new database have any ties/links to the original
database.
> Is the TSQL incorrect above or can I "rename" the LogicalName for the new
> database?
> Thank you in advance for your time.
>|||Hi Uri,
This database needs to be a snapshot of data (ArchivedData) and it cannot be
altered in any way. So I wasn't sure... if it's connected to the origianl DB
somehow I would have problems. Also if I have to DROP the new DB on
occassion , I can't have the Original DB dropped too if SQL's looking for
LogicalName to DROP.
:-O
I'm not sure how to rename...I've been playing around with sp_rename
procedure but what type of "object" is the LogicalName?
I guess if the two databases aren't connected in any way, I can just leave
it as is...and keep my fingers crossed.
;)
Thanks for your quick response!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVFBDh2BEHA.1140@.TK2MSFTNGP10.phx.gbl...
> Carol
> Yep, you can change a logical name file of the database.
> What is your concernment?
> "Carol" <pooch@.hotmail.com> wrote in message
> news:uV8zua2BEHA.2600@.TK2MSFTNGP09.phx.gbl...
> an
LogicalNames
> database.
new
>|||Hi Hari!
Thank you for your quick response as well.
I will just leave it as it is since the physicalnames are correct.
Thank you!

Carol
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:um%23iBm2BEHA.628@.TK2MSFTNGP10.phx.gbl...
> Hi,
> 1. And my PhysicalNames are correct for the new database but the
> LogicalNames remain the same as the original database. Is this correct?
> Yes , You can do that.
> 2. Is the TSQL incorrect above or can I "rename" the LogicalName for the
new
> database?
> It is not required to rename the logical file name. Only physical
name
> need to be changed.
> Thanks
> Hari
>
>
>
> "Carol" <pooch@.hotmail.com> wrote in message
> news:uV8zua2BEHA.2600@.TK2MSFTNGP09.phx.gbl...
> an
LogicalNames
> database.
new
>|||Carol
-- Rename Logical name
-- Created by OJ Date: 05-24-00
---
Set quoted_identifier off set ansi_nulls on
Set nocount on
-- Allow system files to be updated
Exec sp_configure 'allow updates', 1
Reconfigure with Override
go
Declare @.dbname varchar(30),
@.newDataName varchar(64),
@.newLogName varchar(64)
Select @.dbname=rtrim(ltrim(db_name()))
Select @.newDataName=@.dbname + '_Dat',
@.newLogName=@.dbname + '_Log'
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
-- Update dataName
Update sysfiles1
set name=@.newDataName
where fileid=1
-- Update LogName
Update sysfiles1
set name=@.newLogName
where fileid=2
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
set nocount off
go
"Carol" <pooch@.hotmail.com> wrote in message
news:eYKkYo2BEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Hi Uri,
> This database needs to be a snapshot of data (ArchivedData) and it cannot
be
> altered in any way. So I wasn't sure... if it's connected to the origianl
DB
> somehow I would have problems. Also if I have to DROP the new DB on
> occassion , I can't have the Original DB dropped too if SQL's looking for
> LogicalName to DROP.
> :-O
> I'm not sure how to rename...I've been playing around with sp_rename
> procedure but what type of "object" is the LogicalName?
> I guess if the two databases aren't connected in any way, I can just leave
> it as is...and keep my fingers crossed.
> ;)
> Thanks for your quick response!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVFBDh2BEHA.1140@.TK2MSFTNGP10.phx.gbl...
as
> LogicalNames
> new
>|||If you are using SQL 2000, you can change the logical file names after the
restore with the NEWNAME parameter on ALTER DATABASE ... MODIFY FILE. For
example:
ALTER DATABASE MyNewDatabase
MODIFY FILE
(NAME='MyOriginalDatabase_Data', NEWNAME='MyNewDatabase_Data')
GO
As previously mentioned in this thread, the scope of the logical name is
local to the database so the logical name is cosmetic.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carol" <pooch@.hotmail.com> wrote in message
news:uV8zua2BEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a question regarding the "LogicalName" when restoring a backup as
an
> entirely different database.
> I run this:
> USE master
> GO
> RESTORE FILELISTONLY
> FROM DISK='C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
> RESTORE DATABASE MyNewDatabase
> FROM DISK= 'C:\Program Files\MyApp\Archives\Archive_20000311.BAK'
> WITH RECOVERY,
> MOVE 'MyOriginalDatabase_data' TO 'C:\Program
> Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_data.mdf',
> MOVE 'MyOriginalDatabase_log' TO 'C:\Program
> Files\MyApp\MSSQL$MYAPP\Data\MyNewDataba
se_log.ldf'
> GO
> And my PhysicalNames are correct for the new database but the LogicalNames
> remain the same as the original database. Is this correct?
> I cannot have the new database have any ties/links to the original
database.
> Is the TSQL incorrect above or can I "rename" the LogicalName for the new
> database?
> Thank you in advance for your time.
>|||Thanks, Dan!

(This newsgroup ROCKS!)

Carol
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uBX3G32BEHA.712@.tk2msftngp13.phx.gbl...
> If you are using SQL 2000, you can change the logical file names after the
> restore with the NEWNAME parameter on ALTER DATABASE ... MODIFY FILE. For
> example:
> ALTER DATABASE MyNewDatabase
> MODIFY FILE
> (NAME='MyOriginalDatabase_Data', NEWNAME='MyNewDatabase_Data')
> GO
> As previously mentioned in this thread, the scope of the logical name is
> local to the database so the logical name is cosmetic.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carol" <pooch@.hotmail.com> wrote in message
> news:uV8zua2BEHA.2600@.TK2MSFTNGP09.phx.gbl...
> an
LogicalNames
> database.
new
>|||I do not recommend hacking system tables, especially as there is a supported
way to change the logical file names in SQL2K: ALTER DATABASE.
(Not only isn't it supported, but in SQL2K, the database file information is
mirrored in master..sysaltfiles!)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oj8kK22BEHA.2380@.TK2MSFTNGP10.phx.gbl...
> Carol
> -- Rename Logical name
> -- Created by OJ Date: 05-24-00
> ---
> Set quoted_identifier off set ansi_nulls on
> Set nocount on
> -- Allow system files to be updated
> Exec sp_configure 'allow updates', 1
> Reconfigure with Override
> go
> Declare @.dbname varchar(30),
> @.newDataName varchar(64),
> @.newLogName varchar(64)
> Select @.dbname=rtrim(ltrim(db_name()))
> Select @.newDataName=@.dbname + '_Dat',
> @.newLogName=@.dbname + '_Log'
> Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
> sysfiles1 where fileid=1
> Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
> sysfiles1 where fileid=2
> -- Update dataName
> Update sysfiles1
> set name=@.newDataName
> where fileid=1
> -- Update LogName
> Update sysfiles1
> set name=@.newLogName
> where fileid=2
> Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
> sysfiles1 where fileid=1
> Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
> sysfiles1 where fileid=2
>
> set nocount off
> go
> "Carol" <pooch@.hotmail.com> wrote in message
> news:eYKkYo2BEHA.2888@.TK2MSFTNGP09.phx.gbl...
cannot
> be
origianl
> DB
for
leave
backup
> as
the
>