MSSQL – Restore a database backup to new database (new name) from TSQL

1. make sure you have exclusive access to the database, means the database is not being used or there are pending transaction
USE master
GO

ALTER DATABASE mynewdb
SET SINGLE_USER
--This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

2. get the logical name and logic name of your database and log file.

restore filelistonly from disk='c:\db\mydb-20200306.bak'
say the sql returns
mydb
mydb_log
3.  return the backup
restore database mynewdb fromdisk='c:\db\mydb-20200306.bak'
with replace,
move 'mydb' to 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\mynewdb.mdf',
move'mydb_log'to'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\mynewdb_log.LDF'

4. set the DB back to multi-user mode

ALTER DATABASE mynewdb
SET MULTI_USER;