Restore to a different database
-
I have a backup file origined from database with name of DB1. I need to create a testing database at another server exactly same as the DB1.
I restore the backup to a new database called DB1_Test. I prefer to use Enteprise Manager to restore. I open the restore dialog box. I select "Device" at the General tab. Then I select device - the backup file. After that, I click Option tab. I check the option of "Force restore over the existing database".
I make changes at the "Move to physical file name" to the physical path of my new database - DB1_Test (different from the original path of DB1). When I try to change the "Logical File Name" from DB1_Data to DB1_Test_Data, I hit error message "Logical file 'DB1_Test_Data' is not part of database DB1_Test. Use RESTORE FILELISTONLY to list the logical file name. RESTORE DATABASE is terminating abnormally."
Please advise.....
Regards,
kokyan 
-
You can't change the logical file name during the restore process. Just restore the database first, then change the logical file name later using the ALTER DATABASE MODIFY FILE ... syntax.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
May I know what is logical file name? Is it used to point to the physical file path as alias.
Regards, kokyan -
You could say that. From Books Online:
- logical_file_name is a name used to refer to the file in all Transact-SQL statements.
The logical file name must conform to the rules for SQL Server identifiers and must be unique to the database.
- os_file_name is the name of the physical file.
It must follow the rules for Microsoft Windows NT® or Microsoft Windows® 98, and Microsoft Windows 95 file names.
Thus, when you run T-SQL commands related to database files, you typically use the logical file names e.g.
DBCC SHRINKFILE (<logical filename>, 8)
In an active database, just run sp_helpdb <database name> to see the associated logical file names with physical file names. In a backup set, use RESTORE FILELISTONLY .. syntax to view this information.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. - logical_file_name is a name used to refer to the file in all Transact-SQL statements.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply