DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.
Welcome to No.6 of our SQL Server series. Now, we discuss the ultimate safety net: Backups.
In the real world, “I have a backup” is not enough. You need to be able to answer: “Can I restore it to 10:00 AM yesterday before the error happened?” In this comprehensive guide, we move beyond simple backups and master the art of Point-in-Time Recovery, understanding the “Why” behind every step.
Table of Contents
STEP 1. Overview & Recovery Models
1.1 Why Backup? (RPO and RTO)
Backups are not just about copying files; they are about meeting business requirements.
- RPO (Recovery Point Objective): How much data can you afford to lose? (e.g., “Max 15 minutes”)
- RTO (Recovery Time Objective): How fast must the system be back online? (e.g., “Within 1 hour”)
To meet a strict RPO (zero data loss), you must understand Recovery Models.
1.2 The 3 Recovery Models: Simple vs Full
The “Recovery Model” is a database property that controls how transaction logs are maintained.
| Model | Behavior | Pros / Cons |
|---|---|---|
| Simple | SQL Server automatically truncates (deletes) the log after each Checkpoint. Analogy: A surveillance camera that overwrites yesterday’s footage. | ✅ Pro: Low maintenance. The log file stays small. ❌ Con:Zero Point-in-Time recovery. You can only restore to the last Full Backup. |
| Full | SQL Server keeps ALL logs until you back them up. Analogy: A camera that keeps recording forever until you swap the SD card. | ✅ Pro: You can restore to any second in the past. ❌ Con: Requires regular Log Backups. If you forget, the log file will fill up the disk. |
| Bulk-Logged | Hybrid. Minimizes logging for bulk operations (like massive imports). | Specialized use case for performance tuning. |
1.3 Setup: Preparing sampleDB
For this tutorial, we need the FULL recovery model to demonstrate advanced restoration.
USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'sampleDB')
CREATE DATABASE sampleDB;
GO
-- Crucial Step: Switch to FULL model
ALTER DATABASE sampleDB SET RECOVERY FULL;
GOSTEP 2. Database Migration (Detach / Attach)
This is often confused with backup, but it’s actually a method for moving a database to a new server/drive.
2.1 Moving .mdf files (The “Offline” Method)
SQL Server locks database files (.mdf/.ldf) while running. To move them, you must “Detach” (unlock) them first.
-- 1. Disconnect all users and Detach
USE master;
GO
ALTER DATABASE sampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'sampleDB';
GO
-- [At this stage, you manually move .mdf / .ldf files to a new location using File Explorer]
-- 2. Attach (Re-register the files)
CREATE DATABASE sampleDB
ON (FILENAME = 'C:\NewLocation\sampleDB.mdf'),
(FILENAME = 'C:\NewLocation\sampleDB_log.ldf')
FOR ATTACH;
GO2.2 When to use this?
- Use when: You are upgrading hardware or moving a massive database (TB class) where backing up and restoring would take too long.
- Don’t use for: Daily backups. It requires downtime.
STEP 3. Basic Online Backup (Full)
3.1 Taking a Full Backup
A Full Backup is a complete snapshot of the database. It can be taken while users are working (Online).
BACKUP DATABASE sampleDB
TO DISK = 'C:\Backup\sampleDB_Full.bak'
WITH INIT, -- Overwrites the file if it exists
STATS = 10; -- Shows progress every 10%
GO3.2 Security: Compression & Encryption
In modern environments, raw backups are a security risk. Always encrypt them.
- Compression: Increases CPU usage slightly but drastically reduces file size and I/O time.
- Encryption: Protects data if the backup file is stolen. Requires a Certificate.
-- One-time Setup: Create Certificate
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword!';
CREATE CERTIFICATE BackupCert WITH SUBJECT = 'My Backup Cert';
GO
-- Secure Backup
BACKUP DATABASE sampleDB
TO DISK = 'C:\Backup\sampleDB_Secure.bak'
WITH COMPRESSION,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert),
INIT;
GO3.3 Automation options
Never rely on manual scripts. Use SQL Server Agent.
- Maintenance Plans: GUI-based, easy to set up (Drag & Drop). Good for beginners.
- T-SQL Jobs: Flexible and powerful. Preferred by pros.
3.4 Cloud: Backup to URL (Azure Blob)
Backing up to local disk is risky (if the server burns, the backup burns). Backing up to Cloud is safer.
-- 1. Create a Credential (using SAS Token from Azure Portal)
CREATE CREDENTIAL [~~~]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2020... (Your SAS Token)';
-- 2. Backup directly to Blob Storage
BACKUP DATABASE sampleDB
TO URL = '~~~/sampleDB.bak'
WITH COMPRESSION;STEP 4. The Backup Chain (Diff & Log)
Taking a Full Backup of a 10TB database every hour is impossible. That’s why we use the “Chain”.
4.1 Differential Backups: Saving Space
Captures “Everything that changed since the last Full Backup”.
- Size: Small (at first) -> Grows over time.
- Frequency: Usually once a day.
BACKUP DATABASE sampleDB
TO DISK = 'C:\Backup\sampleDB_Diff.bak'
WITH DIFFERENTIAL;4.2 Transaction Log Backups: The Time Machine
Captures “All transactions since the last Log Backup”. Crucial: In FULL model, this command also clears (truncates) the inactive log, freeing up space.
- Frequency: Every 15 mins or even 5 mins.
-- 10:00 AM
BACKUP LOG sampleDB TO DISK = 'C:\Backup\sampleDB_Log1.trn';
-- 10:15 AM
BACKUP LOG sampleDB TO DISK = 'C:\Backup\sampleDB_Log2.trn';4.3 Understanding the Restore Chain
Restoring is like building a Lego tower. You must stack them in order.
[Full Backup] + [Latest Diff] + [Log 1] + [Log 2] + [Log 3]...If you lose “Log 2”, you cannot restore “Log 3”. The chain is broken.
STEP 5. Disaster Recovery (Point-in-Time)
The Scenario: At 14:00, a junior developer accidentally ran DROP TABLE Sales. You need to get the data back as it was at 13:59.
5.1 The “Tail-Log” Backup (Don’t lose the latest data!)
The database is currently broken, but the Transaction Log might still contain data from 13:45 to 14:00 (since the last scheduled log backup). Before doing anything else, capture the Tail (the currently active log).
-- Use NO_TRUNCATE because the DB might be damaged
BACKUP LOG sampleDB
TO DISK = 'C:\Backup\sampleDB_Tail.trn'
WITH NO_TRUNCATE;5.2 The Restore Sequence (NORECOVERY)
Here is the golden rule: “Use NORECOVERY until the very last file.”
- NORECOVERY: Tells SQL Server “I have more files to apply. Don’t open the database yet.”
- RECOVERY: Tells SQL Server “I’m done. Roll back uncommitted transactions and open the DB.”
USE master;
GO
-- 1. Restore Full (NORECOVERY)
RESTORE DATABASE sampleDB FROM DISK = 'C:\Backup\sampleDB_Full.bak' WITH NORECOVERY;
-- 2. Restore Diff (NORECOVERY)
RESTORE DATABASE sampleDB FROM DISK = 'C:\Backup\sampleDB_Diff.bak' WITH NORECOVERY;
-- 3. Restore Previous Logs (NORECOVERY)
RESTORE LOG sampleDB FROM DISK = 'C:\Backup\sampleDB_Log1.trn' WITH NORECOVERY;5.3 Point-in-Time Recovery with STOPAT
Finally, we apply the Tail Log, but we tell SQL Server to stop replay exactly at 13:59:00.
-- 4. Restore Tail Log with STOPAT
RESTORE LOG sampleDB
FROM DISK = 'C:\Backup\sampleDB_Tail.trn'
WITH STOPAT = '2025-11-23 13:59:00',
RECOVERY; -- Now we are done!
GOThe database comes online. The Sales table is back, and any changes made after 13:59 are discarded.
STEP 6. System & Login Migration
A common beginner mistake: “I restored the database to a new server, but the app can’t login!”
The “Orphaned User” Problem:
- Database Users are linked to Server Logins by a hidden ID (SID).
- On a new server, the Login might have the same name, but the SID is different.
Solution:
-- Check for orphaned users
USE sampleDB;
EXEC sp_change_users_login 'Report';
-- Fix (Link the DB User to the existing Server Login)
ALTER USER AppUser WITH LOGIN = AppUserLogin;Pro Tip: Always script out your Logins (using specialized scripts like sp_help_revlogin) so you can recreate them with the exact same SID on the new server.
This concludes No.6 Backup & Recovery. You now understand not just the syntax, but the strategy required to ensure zero data loss. In the next post, we will conclude the series with Monitoring and Troubleshooting.
Continue learning: To reinforce these recovery labs with guided demonstrations, browse Udemy and search for SQL Server backup and recovery courses with exercises on full, differential, and log restores. For hands-on practice with SQL Server queries and database fundamentals, explore DataCamp.