A cold database backup is when you make a simple copy of the data and log files.  For this to work, the database should be taken offline first. 

This is the opposite of the hot database backup that is the regular backup performed while the database is in use. In SQL Server it is usually done with the BACKUP DATABASE command.

The most common scenarios where you can choose to perform cold backups instead of the regular (hot) database backups are:

INTEGRATION WITH THIRD-PARTY UTILITIES	If your storage solution supports “snapshots” capability then you can speed up backup time by taking a consistent snapshot of your database files.
DISASTER RECOVERY IMPLEMENTATIONS	A mid-size business can make use of cold backups to restore databases at remote locations in case of a core infrastructure outage.
REDUCE BACKUP TIME OPERATION	You can parallelize multiple offline databases at backup operations.

In contrast to cold backups,  regular (hot) database backups are most commonly used for:

PARTIAL PHYSICAL RECOVERY	When a disk that holds a member of a filegroup data files is damaged.
PARTIAL LOGICAL RECOVERY	When you have to roll back individual table (or tables)  data to a given point in time.
SERVICE LEVEL AGREEMENTS	When business requirements don’t allow to perform a database shutdown operation.

Why the database should be offline during cold backup?

When you backup a database, its data and log files must be in a consistent state. You cannot guarantee consistency if files are copied when a database is open as there may be running transactions recorded in the transaction log that may have not been written to the data files yet.

The following example of cold backup while the database is online demonstrates why the database should always be offline during cold backup:

10:00 am Begin of data files copy while database instance is up. The copy process takes 10 minutes to complete. 10:05 am Running transactions modify information stored in a data file which have already been copied and information in a data file that has not been copied yet.

10:10 am End of data files copy process. Now we start copying transaction log files. 10:15 am End of transaction log files copy process Note how transaction logs and data files are not in a consistent state. At the end of this sequence, you will have a backup containing transactions recorded in the log files that are not present in the data files or contain just only a part of them.

 

WARNING! A cold backup must be performed only when the database instance was properly shut down!

 

 

How to perform data copy (cold backup)

To copy data files you need to first detach the database using  the “sp_detach_db”  stored procedure like this:

sp_detach_db  ‘Northwind’

Right after this procedure is executed SQL Server will write all dirty pages to disk before it takes the database offline and detaches it from the SQL Server instance.

Now, you can copy database files and transaction logs to any backup destination.

When the copy process is finished then you can reattach the database using the “sp_attach_db” stored procedure again.

The following example will attach data files (AT_data.mdf) and transaction logs (AT_log.ldf) to “Northwind” database:

sp_attach_db ‘Northwind’, ‘e:\data\NW_data.mdf’, ‘e:\data\NW_log.ldf’

However,if your database consists of just one single file for data then you need to execute “sp_attach_single_file_db” procedure instead as follows:

sp_attach_single_file_db ‘Northwind’, ‘e:\data\NW_data.mdf’

Of course, you can safely copy your data files and transaction logs when the entire SQL Server instance is offline.

Conclusion

Cold backups (simple file backups) are the safest way to backup your databases but you have to take your databases offline during the whole file copy process. Cold backups cannot be used as starting point to restore differential or transaction log backups.