Backup of MSSQL database

Backup a Microsoft SQL server in a Windows client using the VSS Plugin via Bweb Management Suite

In this scenario we will backup Microsoft SQL databases (all instances installed) in a Windows client from BWeb Management Suite using the VSS plugin.
Pre-requisite: Any version of a Windows system (on a physical hardware or a virtual machine) with Microsoft SQL Server (Standard or Enterprise Edition) and its databases installed on drive C:. Also a Bacula Enterprise Edition File Daemon must be installed. If this is a new client to backup, please follow scenario 5 above to install and configure the File Daemon on this new client before continuing. The scenario was designed with a small database called AdventureWorksDW in mind that you may find it here (LINK). Please restore this database into the server before continuing. It requires no special privileges.

  1. Open a web browser and download the bacula-enterprise-demo-VSS-Plugin executable file (64-bit only) from the Bacula Enterprise Trial VM.The link is: http://<IP of Trial VM>/clients/windows/bacula-enterprise-demo-VSS-Plugin.exe.
  2. Launch the installer
  3. Leave the listed plugins as they are
  4. After the VSS plugin installation is finished, you can verify that the connection between the Director and this new client is OK, and that the VSS plugin is properly installed. To check this, launch the Firefox browser and go to the address of your Trial Kickstart Enterprise Edition installation (by fully qualified name or IP) and click on the Bacula Enterprise title on the top menu. Select Clients, then Clients again. A list of File Daemons appears. Select your Windows client that you just installed the VSS plugin on by clicking its checkbox and then click on the Status button. You should have an output like below where vss-fd.dll is listed:
  5. If your connection to the client is OK, and the VSS plugin is listed, the next step is to create a FileSet to backup the MSSQL databases. Please go to Configuration/Configure Bacula from the Bweb Management Suite, then click in Fileset at the top menu and at the Add sign button on the top of the fileset list.
  6. Follow the wizard to create a new FileSet.
  7. Fill in the name of the job (1) in the field "Fileset Name" (in this example mssql-backup). Optionally add a description (2). "Enable VSS" - Mandatory (3). Click "Add Include list" (4).
  8. Click on "Edit Plugins".
  9. Select Microsoft VSS MSSQL then click on Add.
  10. If the plugin is added correctly, as seen below, click OK and then Next
  11. Now be sure the "Ignore Case" option is checked, then click Apply   If everything is OK, a box should appear right below the "Add Include List"
  12. Click on Add at the upper right-hand corner to finish.
  13. On BWeb's main configuration page click on the Add a New Backup button on the left pane. Following the wizard, enter a Job Name and select the Job template "BackupsToDedup". Click Next.
  14. In the next page, choose your windows Client Name. Click Next.
  15. In the third page, select the FileSet Name "mssql-backup". Click Next.
  16. In the fourth page Check the pool names and click Next.
  17. Finally, select Manual for the Schedule Name entry since we will launch this backup by hand. Click Next.
  18. At the end, a summary page is displayed. Please review the page and modify the default job level for this scenario to be Differential.
    N.B.: Even with the default level set to Differential, Bacula will perform a full backup since no full backup has been made prior to this scenario. If you run this job a second time it will run a Differential job based on the Full job that will be run in this step. Save the job by clicking the Save button.
  19. If autocommit is disabled all the changes we made are in the Workset but they are not committed yet. To commit them, click on Workset in the top menu. Commit the Workset by clicking on the Commit button.
  20. Once the Workset has been committed, we need tell the Director to reload its configuration which now includes our new changes. To do so, click on Directors on the same page, then in the left pane click on the button Reload Director.Now let's run our backup job.
  21. Click on the menu Jobs, then "Defined Jobs" in the drop down menu. Select the job you have just created (mssql-backup) in the "Job Name" drop list and a summary form appears. If everything is as expected, click on "Run Now".
  22. You'll be redirected to a job status page where you can see the job running (it will take time since there may be gigabytes of data). You can click on the Refresh button while the job is running to monitor the job's progress.
  23. Once the job is done you should have a lob log like this:

    2019-12-16 11:47:42 bacula-enterprise-trial-dir JobId 1: No prior or suitable Full backup found in catalog. Doing FULL backup.
    2019-12-16 11:47:44 bacula-enterprise-trial-dir JobId 1: Start Backup JobId 1, Job=mssql-backup.2019-12-16_11.47.42_17
    2019-12-16 11:47:45 bacula-enterprise-trial-dir JobId 1: Created new Volume="DedupVol-0001", Pool="Dedup_Full_30Days", MediaType="DiskVolume" in catalog.
    2019-12-16 11:47:45 bacula-enterprise-trial-dir JobId 1: Using Device "DedupAutochanger_Dev0" to write.
    2019-12-16 11:47:45 bacula-enterprise-trial-sd JobId 1: Labeled new Volume "DedupVol-0001" on File device "DedupAutochanger_Dev0" (/mnt/bacula/dedup/volumes).
    2019-12-16 11:47:45 bacula-enterprise-trial-sd JobId 1: Wrote label to prelabeled Volume "DedupVol-0001" on File device "DiskAutochanger_Dev0" (/mnt/bacula/dedup/volumes)
    2019-12-16 11:47:45 win-mssql-fd JobId 1: VSS plugin start backup Job.
    2019-12-16 11:48:11 win-mssql-fd JobId 1: Generate VSS snapshots. Driver="Win64 VSS"
    2019-12-16 11:48:11 win-mssql-fd JobId 1: Snapshot mount point: C:\
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "Task Scheduler Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "VSS Metadata Store Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "Performance Counters Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "System Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "WMI Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "Shadow Copy Optimization Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "COM+ REGDB Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "SqlServerWriter", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "ASR Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:42 win-mssql-fd JobId 1: VSS Writer (BackupComplete): "Registry Writer", State: 0x1 (VSS_WS_STABLE)
    2019-12-16 11:50:43 bacula-enterprise-trial-sd JobId 1: Elapsed time=00:02:58, Transfer rate=10.03 M Bytes/second
    2019-12-16 11:50:43 bacula-enterprise-trial-sd JobId 1: Sending spooled attrs to the Director. Despooling 287,249 bytes ...
    2019-12-16 11:50:44 win-fd JobId 1: Database backed up. Database: master, creation date(time): 2019/12/16(14:27:28), pages dumped: 1, first LSN: 1135:80:37, last LSN: 1135:104:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{99C4B6FA-CA5C-4147-BCBC-5DF9DDEBBE81}204'}). This is an informational message only. No user action is required.
    2019-12-16 11:50:44 win-fd JobId 1: Database backed up. Database: AdventureWorksDW, creation date(time): 2019/08/19(21:25:16), pages dumped: 1, first LSN: 62:293:37, last LSN: 62:309:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{99C4B6FA-CA5C-4147-BCBC-5DF9DDEBBE81}18'}). This is an informational message only. No user action is required.
    ...
    2019-12-16 11:50:45 bacula-enterprise-trial-dir JobId 1: Bacula Enterprise bacula-enterprise-trial-dir 12.0.2 (26Aug19):
    Build OS: x86_64-redhat-linux-gnu-bacula-enterprise redhat Enterprise release
    JobId: 1
    Job: mssql-backup.2019-12-16_11.47.42_17
    Backup Level: Full (upgraded from Differential)
    Client: "win-mssql-fd" 12.0.2 (21Aug19) Microsoft Windows Server 2008 R2 Enterprise Edition (build 7600), 64-bit,Cross-compile,Win64
    FileSet: "mssql-backup" 2019-12-16 11:47:42
    Pool: "Dedup_Full_30Days" (From Job FullPool override)
    Catalog: "Catalog" (From Client resource)
    Storage: "DedupAutochanger" (From Command input)
    Scheduled time: 16-Dec-2019 11:47:42
    Start time: 16-Dec-2019 11:47:45
    End time: 16-Dec-2019 11:50:45
    Elapsed time: 3 mins
    Priority: 10
    FD Files Written: 822
    SD Files Written: 822
    FD Bytes Written: 1,785,147,932 (1.785 GB)
    SD Bytes Written: 1,785,411,923 (1.785 GB)
    Rate: 9917.5 KB/s
    Software Compression: None
    Comm Line Compression: 76.8% 4.3:1
    Snapshot/VSS: yes
    Encryption: no
    Accurate: no
    Volume name(s): DedupVol-0001
    Volume Session Id: 1
    Volume Session Time: 1576502585
    Last Volume Bytes: 1,786,773,357 (1.786 GB)
    Non-fatal FD errors: 0
    SD Errors: 0
    FD termination status: OK
    SD termination status: OK
    Termination: Backup OK

 

Congratulations! Now you have a backup of your MSSQL server.

………………………………

We hope that you enjoyed using Bacula Trial Edition. You still have the opportunity to further test some of your Bacula Trial installations’ other features. Please refer to our main manual, in particular the chapters "A Brief Tutorial" and "Getting Started with Bacula".

If you have any comments or suggestions please fill out the feedback form and let us know. We'll get back to you in a timely fashion, and would like to thank you in advance.

For more information about plugins, support options, features, training and pricing, please contact the sales team via our contact form.

If you are not able to complete these scenarios, or have a technical issue regarding any products, please contact the support team via the welcome screen of the .ova, as below:

Bacula Enterprise is used by a large number of high-profile customers, where each one exploits the special qualities of Bacula in a variety of different ways. Bacula Systems looks forward to bringing the same benefits to your organization; please contact us to find out how we can best help you to move forwards, modernize your backup and recovery strategy, and significantly reduce costs.

Bacula Enterprise Features Overview

 

With our best regards,
The Bacula Systems Support Team

Additional notes: The Bacula Enterprise Trial VM will stop working after 30 days. If you would like to continue testing after this time, you can simply click the “Reset Bacula Enterprise Trial environment” icon on the Landing Page, or re-import the Trial ova.