Before executing the restore command, an empty DB with the new DB name, db_bbb in this example, have to be created under the SQL Server instance you like to move to.
Restore Database db_bbb -- the New DB Name
from disk = 'D:\backup\db_aaa.bak' -- Original DB Backup with the logical name db_aaa
with replace,
move 'db_aaa' to 'D:\data\db_bbb.mdf', -- Move the logical DB to the physical path with the new DB name
move 'db_aaa_log' to 'D:\data\db_bbb_log.ldf' -- Move the Logical Log to the physical path with the new "DB name_log"
--Set Single user Mode before restore:
ALTER DATABASE [db_bbb] SET SINGLE_USER
--Set Multi user Mode after restore:
ALTER DATABASE [db_bbb] SET MULTI_USER
--Show Logical name of DB
RESTORE FILELISTONLY
FROM DISK = 'D:\backup\db_aaa.bak'
use db_bbb
select FILE_ID, name as [logical_file_name], physical_name
from sys.database_files
Alter database [db_bbb] modify file ( name = db_aaa, newname = db_bbb )
Alter database [db_bbb] modify file ( name = db_aaa_log, newname = ab_bbb_log )
