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;