Follow the below to restore an MS SQL database backup (.bak) as a different name, changing the paths to the .bak, .mdf and .ldf files as appropriate in your environment.
The only caveat with this is that the Logical Names of the database will remain the same as the database it was restored from, though this will not affect functionality of the database under the new name.
1. Run the script below using, using the path to the .bak file to restore
FROM DISK = ‘C:\SQLBackups\My_DB.bak’
From the results of this, you need to note down the LogicalName of the mdf and ldf files. In my example below the mdf logical name is AdventureWorks2008R2_Data and the ldf name is AdventureWorks2008R2_Log
2. Now run the following on the SQL server to restore the database as a new name, replace MyNewDatabaseName with what you want to call your database, and use the logical names from above in the MOVE statements
RESTORE DATABASE [MyNewDatabaseName]
FROM DISK = ‘C:\SQLBackups\AdventureWorks2008R2.bak’
WITH FILE = 1,
STATS = 10,
MOVE ‘AdventureWorks2008R2_Data’ TO ‘C:\SQLData\MyNewDatabaseName_Data.mdf’,
MOVE ‘AdventureWorks2008R2_Log’ TO ‘G:\BAProject\MyNewDatabaseName_Log.ldf’