• Why backup?

Restore Database:

RESTORE DATABASE [AdventureWorks2014] FROM
DISK=N'Directory/to/your/file.bak'
WITH FILE = 1, MOVE N'Name'
TO N'Directory/to/data.mdf',
MOVE N'log' TO N'Directory/to/log.ldf',
NOUNLOAD,
STATS = 5

• Explanation

Backing up a Database:

BACKUP DATABASE [AdventureWorks2014] TO DISK =
N'Directory/To/Your/File'
WITH NOFORMAT, NOINIT,
NAME = N'Name-of-your-backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 1

• Explanation
• Recovery Model: SIMPLE, FULL, BULK LOGGED
• Different Backup Models: FULL BACKUP, DIFFERENTIAL BACKUP, TRANSACTION LOG BACKUP
• Which Backup Cannot Come Right Before a Transaction Log Backup?

RECOVERY vs NORECOVERY

• NORECOVERY: Keeps the database in a restoring state, allowing more backups to be applied. Use it for intermediate steps in a restore sequence.
• RECOVERY: Completes the restore, brings the database online, and makes it accessible. Use it as the final step.

In summary, NORECOVERY and RECOVERY give you flexibility in how you restore a database. Use NORECOVERY to chain multiple restore operations together, and use RECOVERY when you’re done to make the database operational again.

NORECOVERY

RECOVERY

How They Work Together

Using NORECOVERY and RECOVERY in SQL Server Restores (New Information)

What Happens if You Use RECOVERY Too Early?

• If you apply RECOVERY before restoring all intended backups, the database becomes fully recovered and accessible, but you cannot apply additional backups (e.g., transaction logs) afterward. This requires restarting the entire restore process from the beginning if further restores are needed.