SQL Server offers options to take cold back and hot backup. Below, we will discuss hot and cold backups in detail and how to take these backups. We will explain how to repair and recover the corrupted hot and cold backups.
Cold Backup in SQL Server
Cold backup is done after setting the database offline and then copying the files to another place.
To do that in SSMS, we can right-click the database and select the Tasks>Take Offline option.
This option will take the database offline.
The option will disconnect the database from the SQL Server database engine.
Note: A disconnect icon will be displayed in the database.
Once the database is offline, copy the data files and the transaction log files. The SQL Server stores the data in the data files and the information about transactions in the transaction logs.
- If you do not know the location of data files and logs, take the database online again by right-clicking the database and selecting Task > Bring Online.
- Once it is online, right-click the database and select Properties.
- Select the Files page and look for the Path. You will find the path of the log and data files.
- If the backup is online, take it offline and copy the files to a different location.
The cold backup is just a simple way to copy the files when they are offline. You can only move the log and data files when the database is offline.
Hot Backup in SQL Server
Hot backup is done when the database is online. The process is simple. To do a hot backup, simply right-click the database and select Tasks > Back Up.
- Then, select a path and configure the backup according to your needs. Press OK.
Alternatively, you can use the below T-SQL command to back up your database:
BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\data\AdventureWorks2019.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Which Backup Option is Better?
In a cold backup, you make sure that no transactions are done during the backup. This is the main advantage of this option. On the other hand, hot backup does not require you to take the database offline. Everybody can use the database during the backup process. Hot backup is the most common.
Restrict Access during Hot Backup
If you want to restrict access to the database while doing a hot backup, you can use the single-user mode or the restricted user option. To do that, right-click the database and select Properties.
- In Properties, go to the Options page and select the restrict access option.
Alternatively, you can use the following T-SQL command to set the database in single-user mode:
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
The below command will set the database to the Restricted User mode:
ALTER DATABASE AdventureWorks2012
SET RESTRICTED_USER;
WITH ROLLBACK IMMEDIATE;
GO
The single-user mode permits only one user to handle the database. The restricted mode provides access to the database only to db_owners and db_creators.
Read-Only Mode in the Database
Another way to avoid inserting and deleting transactions during the backup is to set the database in read-only mode.
- To do this, open SSMS, right-click the database and select the Options page.
- Go to the State section, set the Database Read-Only property to True, and press OK.
- If everything is fine, the database will be in the Read-Only state.
Another way to set it to read-only mode is by using the following T-SQL command:
USE [master]
GO
ALTER DATABASE [AdventureWorks2019] SET READ_ONLY WITH NO_WAIT
GO
The read-only option allows to read the database and avoid writing data (insert, update, alter, drop, and create operations). This option allows taking a hot backup without worrying about changes in the database. The main problem is that you cannot insert, update, and delete data in this mode.
Hot Backup Limitations
If you do a SQL Server hot backup with the multi-user mode and the database is not in read-only mode, you can face some limitations, such as:
- You can only back up online data.
- You can make INSERT, UPDATE, and DELETE operations during a hot backup.
- If you are removing or adding a data file to the database during the backup, it waits for the completion of process.
How to Repair Corrupted Cold Backup?
Sometimes, the data files get corrupted due to some problems and you cannot do a cold backup. To check if your data file is corrupted, you can use the DBCC CHECKDB command and verify that everything is fine.
If the data file is severely corrupted, you can use Stellar Repair for MS SQL. This software will repair your data file and recover all the data.
How to Repair Corrupted Hot Backup?
To verify if your hot backup is corrupted, you can use the RESTORE command with the verify only option.
If the hot backup is corrupted, you can use Stellar Toolkit for MS SQL to recover your database.
Conclusion
In SQL Server, it is possible to do a cold backup or a hot backup. To freeze the operations during the backup process, you could restrict access to the database by using the restricted user mode, single-user mode, or read-only mode. Usually, you take a hot backup and later take the differential and transactional backups for missing operations. You should check the backup restrictions to make sure you are not breaking them during hot backup.
Was this article helpful?
