Steps to restore a mssql database using bak, dif and trn files

Question

I have a maintenance plan that spits out bak (at 11pm) dif (at 6am) andf trn files at 30min intervals if I wanted to restore the below files so the data base
was up to date 10am

Answer 1

--how do I use the code below to restore the .bak , then the dif then the 4 diffs in order?  
--Assume EAM is yourDB name  
  
--STEP 1: Restore most recent FULL backup with NORECOVERY  
RESTORE DATABASE [EAM] FROM DISK = N'G:\SQL_Backup\EAM_PROD\EAM_PROD_backup_2022_08_04_230002_0487834.bak' WITH NORECOVERY;  
  
--STEP 2: Restore Log most recent diff backup  
RESTORE DATABASE [EAM] FROM DISK = N'G:\SQL_Backup\EAM_PROD\EAM_PROD_backup_2022_08_05_060001_9099677.dif' WITH NORECOVERY;  
  
----STEP 3: Restore Log after most recent diff backup (may have more than one log files)  
   
RESTORE DATABASE [EAM] FROM DISK = N'G:\SQL_Backup\EAM_PROD\EAM_PROD_backup_2022_08_05_063002_0102543.trn' WITH NORECOVERY;  
RESTORE DATABASE [EAM] FROM DISK = N'G:\SQL_Backup\EAM_PROD\EAM_PROD_backup_2022_08_05_070001_5444479.trn' WITH NORECOVERY;  
RESTORE DATABASE [EAM] FROM DISK = N'G:\SQL_Backup\EAM_PROD\EAM_PROD_backup_2022_08_05_070001_5444479.trn' WITH NORECOVERY;  
RESTORE DATABASE [EAM] FROM DISK = N'G:\SQL_Backup\EAM_PROD\EAM_PROD_backup_2022_08_05_073001_4790412.trn' WITH NORECOVERY  
, STOPAT = 'Aug 05, 2022 07:20 AM'; --- a time within this log file you want to stop at.  
  
--STEP 4: Finally get back databse online  
RESTORE DATABASE [EAM] WITH RECOVERY;  
  

Answer 2

Please see:

https://www.mssqltips.com/sqlservertip/6893/restore-database-sql-server-options-examples/

Answer 3

Yo need to restore from this sequence:>>bak>dif> tran after diff until 10:10 am trn.

Leave each steps of restore with norecovery until the last trn file restore with recovery.

Answer 4

Additional see Restore a SQL Server Database to a Point in Time (Full Recovery Model)

Answer 5

hey that is great , the thing I am hung up on it " FROM AdventureWorksBackups " basically I have a folder "G:\SQL_Backup\EAM_PROD"
08/04/2022 11:01 PM 3,912,416,768 EAM_PROD_backup_2022_08_04_230002_0487834.bak
08/05/2022 06:00 AM 13,413,888 EAM_PROD_backup_2022_08_05_060002_0818453.trn
08/05/2022 06:00 AM 9,616,896 EAM_PROD_backup_2022_08_05_060001_9099677.dif
08/05/2022 06:30 AM 830,976 EAM_PROD_backup_2022_08_05_063002_0102543.trn
08/05/2022 07:00 AM 830,976 EAM_PROD_backup_2022_08_05_070001_5444479.trn
08/05/2022 07:30 AM 963,072 EAM_PROD_backup_2022_08_05_073001_4790412.trn

how do I use the code below to restore the .bak , then the dif then the 4 diffs in order?

RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks WITH RECOVERY;
GO