Hi Team,
Could someone kindly explain the SQL backup process in detail? I'd like to understand what happens during a backup operation.
Your assistance would be greatly appreciated.
Thank you in advance!
Solved
SQL Backup process
Best answer by Wasim
Hi
The SQL backup process in Commvault involves several key steps to ensure data integrity and recoverability:
1. Pre-Backup Phase: Commvault prepares the SQL database for backup. This includes creating a snapshot of the database using the SQL Server VSS Writer (Volume Shadow Copy Service), which ensures a consistent state of the database at the point of backup.
2. Backup Phase: Data from the SQL database is transferred from the client (where the database resides) to the backup media. This data transfer is managed by the MediaAgent, which handles the movement of data to the designated storage.
3. Post-Backup Phase: After the backup is completed, Commvault updates the backup metadata in the CommServe database. This metadata includes information about the backup job, such as time, size, and status, which is crucial for future restore operations.
4. Verification Phase: Optionally, a verification process can be run to ensure that the backup data is complete and consistent. This step is crucial for validating the integrity of the backup.
5. Cleanup Phase: Any temporary snapshots or logs created during the backup process are cleaned up to free up resources.
https://documentation.commvault.com/2024e/essential/microsoft_sql_server.html
Regards,
Wasim
- Vaulter
- 86 replies
- Answer
Hi
The SQL backup process in Commvault involves several key steps to ensure data integrity and recoverability:
1. Pre-Backup Phase: Commvault prepares the SQL database for backup. This includes creating a snapshot of the database using the SQL Server VSS Writer (Volume Shadow Copy Service), which ensures a consistent state of the database at the point of backup.
2. Backup Phase: Data from the SQL database is transferred from the client (where the database resides) to the backup media. This data transfer is managed by the MediaAgent, which handles the movement of data to the designated storage.
3. Post-Backup Phase: After the backup is completed, Commvault updates the backup metadata in the CommServe database. This metadata includes information about the backup job, such as time, size, and status, which is crucial for future restore operations.
4. Verification Phase: Optionally, a verification process can be run to ensure that the backup data is complete and consistent. This step is crucial for validating the integrity of the backup.
5. Cleanup Phase: Any temporary snapshots or logs created during the backup process are cleaned up to free up resources.
https://documentation.commvault.com/2024e/essential/microsoft_sql_server.html
Regards,
Wasim
+4- Vaulter
- 10 replies
In the default / normal configuration (not app-aware VM backup / IntelliSnap) the agent will create Virtual Devices using the VDI specification. The number of devices created is going to be equal to the number of streams selected in the subclient.
Commvault will then issue a BACKUP DATABASE (or BACKUP LOG) command with the argument TO VIRTUAL DEVICE.
This is also why the agent requires SA privileges and Windows administrator privileges.
From a SQL perspective, this is a “normal” backup. But instead of dumping to FILE (which SQL admins use in maintanance plans), it dumps to a virtual device. It will also be recorded in the msdb backup history.
The Commvault agent will then perform compression and (for database backups, not for log backups) deduplication and send the data to the MediaAgent.
On restore, the same thing happens - a number of VDI devices are created, and a RESTORE DATABASE call is issued. Commvault will automatically issue multiple RESTORE commands if required (for example, when restoring to point in time, it will do a full restore, then differential restore, then log restore).
This also means that restoring your database can be significantly slower compared to backing it up, as multiple RESTORE commands may need to be issued.
1 person likes this
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.