Believe it or not, I used to work a lot with Microsoft SQL Server. While I did not call myself a database administrator (DBA), I did know my way around a database or two. Since I’ve been at Veeam, I have always enjoyed telling the Veeam story around using SQL Server as a critical application that needs the best Availability options.
That’s why I took particular interest in Veeam Explorer for Microsoft SQL Server that came in Veeam Backup & Replication. Veeam Explorer for Microsoft SQL Server allows application-specific restores of SQL databases, and also contents of tables, objects such as stored procedures, views and more. Additionally, you can also restore the databases to a specific transaction.
This is a great combination of functionality from the established application-aware image processing with a dedicated tool for database restores in Veeam Explorer for Microsoft SQL Server. Additionally, Veeam Backup & Replication and the Veeam Agent for Microsoft Windows also provide an image backup of the entire system.
For those who are not a DBA, sometimes dealing with low-level SQL Server topics can be a bit overwhelming. To help this process, I created a few scripts to help individuals learn this type of interaction with SQL Server. I put three (and a deleted script) up on the Veeam Github site. To use this script, only an S:\ drive is needed (the path can be changed) to create the sample database and put in a SQL Server Agent job to automatically run a few stored procedures that will insert and delete random data. This creates a database called SQLGREENDREAM.
After running the three scripts to create the database, implement the random number function and set the schedule to create the random data (2 records) and delete 1 record. The SQL Server Transaction Log Backup will show the new database being backed up after the next incremental backup:
Once the interval of the SQL Server Agent job runs (12 minutes in the GitHub script) and the Veeam Backup Job interval passes, the most selective restore point option can be selected in Veeam Explorer for Microsoft SQL Server. This selective option, to restore to a specific transaction, is shown in the figure below:
Once the interval of the SQL Server Agent job runs (12 minutes in the GitHub script) and the Veeam Backup copy interval process through a time when the test data has been run, the restore to a specific transaction option can be visible to the controlled scripting for the SQLGREENDREAM database in the GitHub repository. Then you can see the records in question being just as scripted, 2 records added then one record deleted. Those entries are done by the SQL Server Agent:
From there, the restores can be done with confidence to see how the SQL databases are restored with Veeam. With the sample scripts in the GitHub repository, one can become more comfortable with these restore situations when venturing out of normal comfort zones! If you are using Veeam Backup Free Edition and the SQL Server is a VM being backed up, you can still use Veeam Explorer for Microsoft SQL Server to restore the database to the time of the image-based backup; just no transaction rollback. You can use the NFR program for a fully functional installation also.
Have you used Veeam Explorer for Microsoft SQL Server to restore to a transaction? How did it go? Share your comments below.
