Restoring SQL server

https://www.mssqltips.com/sqlservertip/4110/solve-common-sql-server-restore-issues/

https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

1. Restore latest full backup with NORECOVERY(Backup file from USERDATABASES folder).
2. Restore latest Diff backup with NORECOVERY. (Backup file from Differential  folder).
3. Restore Tlog files with NORECOVERY and the last Tlog file with RECOVERY (Tlog files from Transactional  folder).

RESTORE DATABASE aate73 FROM DISK = 'D:\Restore\Backup\UserDatabases\ATE73\WC_cate73_1_20180111THU010000.BAK'

WITH NORECOVERY, REPLACE,

MOVE N'prdadvocate72' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\advocate73.mdf',  

MOVE N'prdadvocate72_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\logs\advocate73_log.ldf'

RESTORE DATABASE advocate73 FROM DISK = 'D:\Restore\Backup\Differential\ADVOCATE73\WC_advocate73_1_20180111THU120001.BAK'

WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU120000.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU121501.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU123000.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU124500.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU130000.TRN' WITH RECOVERY

Restore or backup status

SELECT r.session_id AS [Session_Id] ,r.command AS [command] ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete] ,GETDATE() AS [Current Time] ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time] ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min] ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours] ,CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE WHEN r.statement_end_offset = - 1 THEN 1000 ELSE (r.statement_end_offset - r.statement_start_offset) / 2 END) 'Statement text' FROM sys.dm_exec_sql_text(sql_handle) )) FROM sys.dm_exec_requests r WHERE command like 'RESTORE%' or command like 'BACKUP%'

SELECT Req.percent_complete AS PercentComplete

,CONVERT(NUMERIC(6,2),Req.estimated_completion_time/1000.0/60.0) AS MinutesUntilFinish

,DB_NAME(Req.database_id) AS DbName,

Req.session_id AS SPID, Txt.text AS Query,

Req.command AS SubQuery,

Req.start_time AS StartTime

,(CASE WHEN Req.estimated_completion_time < 1

THEN NULL

ELSE DATEADD(SECOND, Req.estimated_completion_time / 1000, GETDATE())

END) AS EstimatedFinishDate

,Req.[status] AS QueryState, Req.wait_type AS BlockingType,

Req.blocking_session_id AS BlockingSPID

FROM sys.dm_exec_requests AS Req

CROSS APPLY sys.dm_exec_sql_text(Req.[sql_handle]) AS Txt

WHERE Req.command IN ('BACKUP DATABASE','RESTORE DATABASE') OR Req.command LIKE 'DBCC%';