This is the opposite to the hot database backup that is the regular backup performed while the database is in use. In SQL Server it is usually done with BACKUP DATABASE command.
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 taken 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 rollback 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 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 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. 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 shutdown!
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:
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 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:dataNW_data.mdf’, ‘e:dataNW_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:dataNW_data.mdf’
Of course, you can safely copy your data files and transaction logs when the entire SQL Server instance is offline.
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.