Hot backups copy an online database while users work in it and offline copies capture its files while the database is closed. SQL Server takes online backups with the BACKUP DATABASE statement. Offline copies need the database detached or the service stopped. Three methods handle both options and the rescue case. Online backups suit daily protection and offline copies suit planned moves. Recovery software opens a damaged MDF file from a cold copy and exports its tables.
Your manager wants a backup of DepotDB before the weekend upgrade and the sales team still works in it. Hot backups avoid downtime while a clean file copy needs the database offline.
Search results explain both names and stop there. Few show the settings that make an online backup safe or the steps that make an offline copy usable.
This guide explains the difference between a hot backup and a cold backup, including the FORMAT option that makes older backups useless.
Microsoft states in the BACKUP reference that SQL Server uses an online backup process so a database backup works while the database is in use.
Hot vs Cold: Which Backup Fits Your Server?
Hot and cold are common trade names. Microsoft names the hot method an online backup process.
| Feature | Hot | Cold |
|---|---|---|
| Database state | Online | Offline |
| User access | Open during the backup | Closed during the copy |
| Output | A BAK file | The MDF and LDF files |
| Best for | Daily protection | Planned moves and upgrades |
| Tools | BACKUP DATABASE | Detach and copy or a service stop |
Use the table to match your situation to a method below.
3 Methods for Hot Backup and Cold Backup in SQL Server
Method #1 and Method #2 cost nothing. Method #3 starts from a damaged copy.
| Feature | Online Copy | Offline Copy | Recovery Software |
|---|---|---|---|
| Price | No fee | No fee | Scan free and pay for export |
| Best for | Systems that cannot stop | Planned downtime | Damaged MDF copies |
| Needs | Backup permission | db_owner membership | Windows PC and an MDF copy |
| Main limit | Some operations wait | Users lose access | Trial export limit |
Method #1: Take a Hot Backup With BACKUP DATABASE (Free, Needs Backup Permission)
Microsoft documents the SSMS path in its full database backup guide. Right click the database and select Tasks and then Back Up. Per Microsoft Full is the default backup type.
Scripts suit scheduled hot backups. Add a page check and compression with this statement.
BACKUP DATABASE DepotDB
TO DISK = N'E:\Backups\DepotDB_Hot.bak'
WITH INIT, CHECKSUM, COMPRESSION;Per Microsoft INSERT and UPDATE and DELETE statements work during a backup. File management and shrink operations cannot. BACKUP is also not allowed inside a transaction.
Default holders of the permission are sysadmin and db_owner and db_backupoperator members. Backup folders need access for the SQL Server startup account.
Microsoft states that the FORMAT option makes the existing contents of the volume invalid. Use INIT when you only want to overwrite the backup sets.
Method #2: Take a Cold Backup by Detaching the Files (Free, Needs Owner Rights)
Cold backups use files that no session opens. Detach affects one database and the rest of the server remains online. List every file first.
USE DepotDB;
GO
SELECT file_id, name, physical_name
FROM sys.database_files;Microsoft calls for all files of the database before you detach and move. Detach next.
USE master;
GO
EXEC sp_detach_db N'DepotDB';Per the detach a database guide detach deletes no files and needs db_owner membership. Active connections stop it. Copy the MDF file and the LDF file to a separate storage device and attach the database again.
CREATE DATABASE DepotDB
ON (FILENAME = N'D:\SQLData\DepotDB.mdf'),
(FILENAME = N'D:\SQLData\DepotDB_log.ldf')
FOR ATTACH;Stopping the service also frees the files but it takes every database offline. Microsoft documents the stop steps in SQL Server Configuration Manager and with net stop.
Microsoft recommends placing backups on a separate device from the database files. Cold copies beside the original protect nothing when that disk fails.
Method #3: Recover a Damaged Cold Copy With EmailDoctor (Free Scan, Paid Export)
Cold backups fail the attach when the MDF file is damaged. EmailDoctor SQL Database Recovery scans a duplicate of the file and sends its tables into a fresh database.
Work on a duplicate and never the original. Vendor notes put the trial limit at five objects and a 25 row sample from one table.
Scan the Cold Copy
- Install the recovery software and start it.
- Click Open and locate the copied DepotDB.mdf file.
- Use Quick Scan for a copy taken after a clean detach and Advance Scan for a copy from a crashed server.
- Check the detected SQL Server release in the list.
- Study the scan report and browse the tables in the left tree.
Rebuild the Database
- Click Export on the toolbar.
- Select the SQL Server Database option.
- Supply the Server Name and sign in with Windows Authentication.
- Click Create New Database and type DepotDB_Recovered in the Database Name box.
- Tick the tables and views you need.
- Select With Schema & Data for rows and structure and click Export.
- Check the Export Completed window and refresh Databases in SSMS.
How to Verify Both Backups?
Microsoft advises that you test every backup because no restore strategy exists until you do. Add CHECKSUM to the backup statement and verify the file.
RESTORE VERIFYONLY
FROM DISK = N'E:\Backups\DepotDB_Hot.bak'
WITH CHECKSUM;Per Microsoft restore the backup somewhere safe and execute DBCC CHECKDB against it. Attach the cold copy on a test server and execute the same check.
DBCC CHECKDB (N'DepotDB') WITH NO_INFOMSGS;Which Problems Stop a Backup?
Five problems stop most backups:
| Problem | Reason | Fix |
|---|---|---|
| Backup fails during file work | Another session adds a file or shrinks one | Wait for that operation to finish |
| Backup is refused in a script | The script opens a transaction | Execute BACKUP outside any transaction |
| Access to the folder is denied | The startup account lacks folder access | Give the startup account folder access |
| Detach is refused | Active connections exist | Use Drop Connections in the dialog |
| Older backups became useless | FORMAT overwrote the media header | Use INIT instead of FORMAT |
Switch to Method #3 on a duplicate when the cold copy refuses to attach.
Frequently Asked Questions
Does a Hot Backup Need Downtime?
No. Microsoft states that SQL Server backs up a database while it is still in use.
Can Users Insert Data During an Online Backup?
Yes. Microsoft states that INSERT and UPDATE and DELETE statements work during a backup.
Can BACKUP Execute Inside a Transaction?
No. Microsoft rejects BACKUP inside both explicit and implicit transactions.
Does FORMAT Preserve Older Backups?
No. Microsoft states that FORMAT makes the existing contents of the volume invalid.
Does CHECKSUM Verify Backup Pages?
Yes. Per Microsoft the backup verifies pages for checksum and generates a checksum for the backup.
Does BACKUP Need Special Permission?
Yes. Three roles have it by default per Microsoft. They are sysadmin and db_owner and db_backupoperator.
Does Detach Delete the Database Files?
No. Detach deletes no files per Microsoft.
Does a Cold Backup Need the Database Offline?
Yes. Copy the files only after you detach the database or stop the service.
Can Recovery Software Open a Damaged Cold Copy?
Yes. Recovery software scans a duplicate of the MDF file and sends its tables into a fresh database.
Does a Trial Copy Export Everything?
No. Vendor notes put the trial limit at five objects and a 25 row sample from one table.
Author’s Verdict
Hot backups protect a busy database every day while offline copies suit planned moves. Choose by how much downtime you can accept.
Add CHECKSUM to every online backup and test one restore. Recovery software matters when a cold copy will not attach.
Store both backups away from the database disk.
- Hot backups copy an online database with BACKUP DATABASE while users work.
- Offline copies use detached files and take the database offline.
- FORMAT makes older backups useless so use INIT.
- CHECKSUM and a test restore prove a backup works.
- Recovery software exports a damaged MDF copy into a fresh database.
- Test every step on a duplicate first.
Next Read: RTO and RPO in SQL Server Explained
Also Read: Types of Backups in SQL Server | Restore a SQL Database From a BAK File
