Checkdb - Backup (full - differential and T-log) and restore commands

use [master];

GO

DBCC CHECKDB(N'adventureworks_2022')  WITH  MAXDOP = 2 , PHYSICAL_ONLY

GO

use [adventureworks_2022];

GO

DBCC CHECKDB(N'AdventureWorks2008R2')  WITH  MAXDOP = 2 , PHYSICAL_ONL

 use [master];

GO

BACKUP DATABASE [adventureworks_2022] TO  

DISK = N'G:\backup\adventureworks_2022_backup_2025_02_10_050959_1588257.bak' WITH NOFORMAT, NOINIT, 

NAME = N'adventureworks_2022_backup_2025_02_10_050959_1588257', 

SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'adventureworks_2022' 

and backup_set_id=(select max(backup_set_id) from msdb..backupset 

where database_name=N'adventureworks_2022' )

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''adventureworks_2022'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM  DISK = N'G:\backup\adventureworks_2022_backup_2025_02_10_050959_1588257.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

Restore database with override and norecovery

-----------------------------------------------------

Alter database [adventureworks_2022] set single_user with rollback immediate

go

USE [master]

RESTORE DATABASE [adventureworks_2022] FROM  DISK = N'G:\backup\adventureworks_2022_backup_2025_02_10_051446_0372005.bak' WITH  RESTRICTED_USER,  FILE = 1, 

MOVE N'AdventureWorks2019' TO N'F:\data\AdventureWorks2019.mdf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

GO

restore database [adventureworks_2022] from disk = N'G:\diff-backup\adventureworks_2022\adventureworks_2022_backup_2025_02_10_051524_5690424.bak' with recovery

Transaction log backup

--------------------------------------

Database must be in full recovery model. 

USE [master]

GO

ALTER DATABASE [adventureworks_2022] SET RECOVERY FULL WITH NO_WAIT

GO

use [master];

GO

EXECUTE master.dbo.xp_create_subdir N'G:\tlogbackup\adventureworks_2022'

GO

BACKUP LOG [adventureworks_2022] TO  

DISK = N'G:\tlogbackup\adventureworks_2022\adventureworks_2022_backup_2025_02_10_055044_8462580.trn'

WITH NOFORMAT, NOINIT,  NAME = N'adventureworks_2022_backup_2025_02_10_055044_8462580', SKIP, REWIND, NOUNLOAD,  STATS = 10

Transaction log restore should be sequential and its use case

----------------------------------------------------------------------------------

Transaction Log Restore Should Be Sequential – Use Case with Example

🛠️ Use Case: Database Recovery After Accidental Data Deletion

Imagine you are the DBA of a financial company that processes daily transactions.
Your SQL Server database is in Full Recovery Mode, and you perform:
✔ Full backups every night at 12:00 AM
✔ Transaction log backups every hour

One day at 3:45 PM, a developer accidentally runs:

DELETE FROM Transactions;

💥 All transaction records are gone!

To recover, you must restore transaction logs in the correct sequential order to avoid data corruption.

---

🔄 Step-by-Step Recovery Process

We assume:

• The database name is FinanceDB
• The last full backup was taken at 12:00 AM
• Transaction log backups exist for every hour (1 AM, 2 AM, ..., 3 PM)
• You want to restore the database to 3:30 PM (before deletion at 3:45 PM)

1️⃣ Restore the Full Backup (with NORECOVERY)

RESTORE DATABASE FinanceDB 
FROM DISK = 'D:\Backups\FinanceDB_Full_1200AM.bak' 
WITH NORECOVERY;

🔹 Why NORECOVERY?
It keeps the database in a restoring state, allowing further log restores.

---

2️⃣ Restore Transaction Log Backups Sequentially

Now, restore each transaction log backup in order (1 AM → 2 AM → 3 PM).
Each must be restored using NORECOVERY except the last one.

RESTORE LOG FinanceDB 
FROM DISK = 'D:\Backups\FinanceDB_Log_0100AM.trn' 
WITH NORECOVERY;

RESTORE LOG FinanceDB 
FROM DISK = 'D:\Backups\FinanceDB_Log_0200AM.trn' 
WITH NORECOVERY;

RESTORE LOG FinanceDB 
FROM DISK = 'D:\Backups\FinanceDB_Log_0300PM.trn' 
WITH NORECOVERY;

🔹 Why Sequential Restore?
Transaction logs depend on the previous logs. Restoring out of order will cause an error.

---

3️⃣ Restore the Last Transaction Log with STOPAT

To restore only up to 3:30 PM (before the accidental delete at 3:45 PM):

RESTORE LOG FinanceDB 
FROM DISK = 'D:\Backups\FinanceDB_Log_0300PM.trn' 
WITH STOPAT = '2025-02-10T15:30:00', RECOVERY;

🔹 Why STOPAT?
It stops replaying transactions at 3:30 PM, avoiding the accidental deletion.

---

🛠️ Final Outcome

✅ Database is fully restored to 3:30 PM.
✅ The accidental delete at 3:45 PM is avoided.
✅ No data loss except transactions after 3:30 PM.

---

💡 Key Takeaways

✔ Transaction logs must be restored in sequence—you cannot skip logs.
✔ Use NORECOVERY for every log restore except the last one.
✔ Use STOPAT to restore to a specific time before a failure.
✔ If logs are missing or out of order, recovery will fail.

Would you like a script to automate log restore with dynamic timestamps? 😊