Backup and Restore Database with Move

Database Backup

USE TestBackup;  
GO  
BACKUP DATABASE TestBackup  
TO DISK = 'D:\SQLServerBackups\TestBackup.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'TestBackup_01',  
      NAME = 'Full Backup of TestBackup';  
GO  

Database Info

Use the following script to get information about the database backup file.

RESTORE FILELISTONLY  
   FROM DISK = 'D:\SQLServerBackups\TestBackup.Bak' 

Database Restore

Note: Folder must be created first.

RESTORE DATABASE TestBackup01 FROM DISK = 'D:\SQLServerBackups\TestBackup.Bak'
WITH FILE = 1, replace, maxtransfersize = 4194302, buffercount = 96, stats = 5,
   MOVE 'TestBackup' TO 'D:\MSSQL\DATA\TestBackup\TestBackup.mdf',   
   MOVE 'TestBackup_log' TO 'D:\MSSQL\DATA\TestBackup\TestBackup_log.ldf'

Check Progress

select
r.session_id,
r.blocking_session_id,
db_name(database_id) as [DatabaseName],
r.command,
[SQL_QUERY_TEXT] = Substring(Query.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(Query.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1),
                [SP_Name] =Coalesce(Quotename(Db_name(Query.dbid)) + N'.' + Quotename(Object_schema_name(Query.objectid, Query.dbid)) + N'.' +
     Quotename(Object_name(Query.objectid, Query.dbid)), ''),
r.percent_complete,
start_time,
CONVERT(VARCHAR(20), DATEADD(ms, [estimated_completion_time],
GETDATE()), 20) AS [ETA_COMPLETION_TIME],
CONVERT(NUMERIC(6, 2), r.[total_elapsed_time] / 1000.0 / 60.0) AS [Elapsed_MIN],
CONVERT(NUMERIC(6, 2), r.[estimated_completion_time] / 1000.0 / 60.0) AS [Remaning_ETA_MIN],
CONVERT(NUMERIC(6, 2), r.[estimated_completion_time] / 1000.0 / 60.0/ 60.0) AS [ETA_Hours],
wait_type,
wait_time/1000 as Wait_Time_Sec,
wait_resource
from sys.dm_exec_requests r
cross apply sys.fn_get_sql(r.sql_handle) as Query where r.session_id>50 and command IN ('RESTORE DATABASE','BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-to-a-new-location-sql-server?view=sql-server-2017