Backup and Restore SQL Databases Natively
Updated: Feb 14
By now you've probably heard about many exciting features in Veeam v12. While security and direct-to-object storage enhancements have been the talk of the town, I want to spend time discussing my favorite v12 enhancement. Veeam is adding SQL to their database plug-in offerings!
The power battle between DBAs and backup admins over who owns database protection is a story as old as SQL itself. Backup admins want control over backups because their job requires owning SLAs for all the workloads in the company. DBAs want control over backups because simply put, they're their databases. Neither side is wrong. If anything both sides are right. Which is why this internal struggle in organizations has gone on for decades.
Veeam is well aware of this issue which is why there are database plug-ins for Oracle and SAP already. Now, there is one for SQL. Having a database plug-in enables DBAs to drive backups and restores of their databases from the native tools they're accustomed to. In the case of SQL, this would be right from SQL Server Management Studio (SSMS) as shown below. The plug-in integrates with SQL's VDI device to backup the entire contents of the database whether it's a full, differential or transaction log rather than VSS which requires a snapshot.
This is a win-win for both the DBA and the backup admin because from the Veeam console you still have full visibility and governance capabilities. Configuration of the plug-in requires the Veeam server's DNS/IP and a Veeam repository which enables backup admins to monitor the database backups and still comply with any potential audits.
Creating a backup in Veeam's SQL Plug-in should look nearly identical to anyone who has created a SQL native backup before. As you can see below, you select which databases you want to protect and whether you want a full, differential or T-log backup.
Next, you select the retention for these backups, parallel streams and if you want compression enabled. Unlike pre-v12, retention is applied at the database rather than server level since the plug-in integrates with SQL VDI instead of VSS.
One of my favorite features is the option to, "Save as a SQL Agent Job" as shown above. This will automatically create a SQL CLI script that is saved to the Server Agent jobs sections of SSMS based on the settings applied above. Simply apply a schedule to the job and you'll be cooking with bacon.
What's the point of backing something up though if you don't plan to restore it? From the plug-in you can trigger a restore that will look almost identical to a SQL native restore. Select the database and restore point of your choosing. You can restore to the original location or a different one. For example, DBAs might want to restore a database from yesterday's backup to run a DBCC CHECKDB command which is easy to accomplish here.
Lastly, choose the preferred recovery state for the database.
If a DBA is still reading this, you might be wondering why would we even use the plug-in since this looks identical to SQL native backup and restore options? That is exactly the point though. The goal with the plug-in is to keep the process similar to what SQL DBAs do today, while also providing the backup admins visibility and monitoring capabilities to report up to management or auditors.
Here is a video walkthrough!