rename logical file name during restore

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

rename logical file name during restore

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-09-04 : 14:07:33

Hi,

Could some one help me out please?

For instance, I restore a production db into TestDB.

RESTORE DATABASE TestDB
FROM disk = '\\filepath\AdventureWorksBackups.bak'
WITH ...
GO
'AdventureWorks_Data.mdf' became '...\testdb.mdf',
'AdventureWorks_Log.ldf' became '...\testdb.ldf';

But I have to rename the logical files in the database properties, files page manually using ssms.

How do you use script to rename them from AdventureWorks to testDB, either within the restore command or a seperate one?

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-09-04 : 14:52:54

I found the answer as in this next Kristen's post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

-- Rename logical names (only needed if restoring from a backup for a Different database):
ALTER DATABASE MyDatabase
MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabase_data')
GO
ALTER DATABASE MyDatabase
MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabase_log')
GO

Thanks!

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-04 : 15:00:25

Here is some code I use in sql 2008r2:

RESTORE DATABASE [MyDatabase] 
FROM DISK = N'G:\SQLBackup\MyDatabase_Backup.bak' 
WITH FILE = 1,  REPLACE, 
MOVE N'MyDatabase' TO N'E:\SQLData\MyDatabase.mdf',  
MOVE N'MyDatabase_log' TO N'F:\SQLLog\MyDatabase.LDF' 
GO

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources