DR for your DR Solution – Backing up your Veeam Backup & Replication Database

Last week I published an article based around moving your Veeam Backup & Replication Database to a new server.  In that article I used a backup and restore method to move the Veeam DB somewhere else.  Well, the feedback I received from that article was mostly based around how to setup a scheduled job to backup the Veeam database, so, without further ado, here's how I would recommend doing so.

Connect to your Veeam DB server

First off, we need to initiate a connection to the SQL Server hosting your Veeam DB.  Honestly, it's probably easier just to RDP or console in to the  SQL Server hosting the database and then connect to it.  In my examples below I will be using SQL Server Management Studio to do so.  Just a note, if you are running the default installation of Veeam with SQL Server Express, you can connect to the server using 'SERVERNAME\VEEAM' as your server name and use Windows Authentication.  Otherwise, just connect to your SQL instance as you normally would.

Create the Maintenance Plan

This is a pretty easy step.  Once you are connected just create a new maintenance plan by expanding Management -> Maintenance Plans.  Right click on Maintenance Plans and select New Maintenance Plan.  You could also select to go through the wizard, but again, for this example I chose to create it from scratch.

Create the Backup Database and Maintenance Cleanup tasks

You should now be in what is called Design Mode of the maintenance plan.  In the bottom left hand corner of your screen you should see all of the Design Tasks that we are able to just click and drag over to our Design Surface in the centre.  Here is where we need to setup our first task which is to do the actual backups of our databases.  Simply select the Backup Database task and drag it over into our Design Surface.  Do the same with the Maintenance Cleanup task as well.

Configure the Backup Database Task

Ok, we are now getting into the meat of this job.  Double click the Backup Database task that we just moved over.  Here is where we will setup all of our parameters evolving around our backups.  First off we need to select our connection.  In most cases this can just be left at 'Local Server Connection', however depending on how you are setup you may need to enter in the actual hostname or IP of the server.  Leave the backup type set to Full.  Under the database(s) section is where you can select which databases you would like to backup with this job.  You can just select the Veeam DB if you want, but I chose to select 'All Databases'.  Back up to – Select your target, usually disk nowadays.  I left the expiration of the backup sets at the default, which is to not expire.  Check the create a backup for every database, and in this example I've also checked the subdirectory for each database as well.  I just find this to be a bit more polished when browsing through the backups 🙂  You can use whatever you want for the backup extension, but the standard for SQL is 'bak'.  Select a location to store the backups, click 'OK' and your done.

Configure the Maintenance Cleanup Task

This step can be skipped if you want but you will have to watch your free space.  In this step we will set the job to 'clean up after itself' by deleting backups that are a certain number of days or weeks old.  Again setup your connection the same as you did in the previous step.  You will also want to select to 'Delete Backup Files'  Select 'Search folder and delete files based on an extension' and setup the folder the same as above. Select to include first-level subfolders.  Enter in our file extension the same as above (bak).   Select your desired retention policy under the 'File Age' section.  I usually chose somewhere along the lines of a couple of weeks depending on the importance as well as the free space on the backup targets.  After all this is setup click 'OK' to save your cleanup task.

Connect the two tasks together

Here's an easy one for you.  If you single click on the Backup Database Task you will see a green arrow appear underneath it.  Simply drag that arrow over to the Maintenance Cleanup Task. You could also take the vice versa action on this one.  You could have the single click the Cleanup Task and drag arrow to the Backup Task.  Either way, whichever one task runs first you should end up with the same results.

Schedule the plan

Click the schedule button in the Maintenance Plan toolbar.  In the job schedule dialog that opens select the required times and frequency that you wish to run this job and click 'OK' to save the schedule.  The schedules are very flexible and allow for virtually every scheduling combination that you can think of.

This is all the modification that we need to do so just save your maintenance plan by going to File -> Save Selected Item or by right-clicking the tab at the top and selecting 'Save'.

Check the Agent and Test.

If you now have a look under the your SQL Agent by going to SQL Server Agent -> Jobs you should see that a new agent job has been created with a name similar to that of your maintenance plan.  You can right click on this job and select properties.  You should see your schedule as well as the SSIS call to run your maintenance plan.  If you would like to test your backup job now, simply right click your job and select 'Start Job At Step'.  This should popup a dialog showing you the status of the job.  Once completed you should see some backups in the target backup locations you inputted earlier.

So there you have it!  DR for your DR Solution.