Attix5 Pro FAQ
Search:     Advanced search

Microsoft SQL backup key facts

Article ID: 192
Last updated: 01 Apr, 2016

Can Attix5 Pro be used to back up SQL?
Yes, Attix5 Pro can back up SQL Server 2000, 2005, 2008, 2012, and 2014. Older versions are not supported.
SQL Express and Enterprise SQL are both supported.
Note: See details below if using the Windows Internal Database (also known as SQL Server Embedded Edition, SSEE).

How does the backup work?
Attix5 Pro uses plug-ins to facilitate SQL backups. The appropriate plug-in to use differs between SQL Server versions.

Which plug-ins can be used for SQL backup?
The MS SQL plug-in and the VSS plug-in.
The MS SQL plug-in is compatible with SQL 2000, 2005, 2008, 2012, and 2014 but does not support the Windows Internal Database.
The VSS plug-in is compatible with SQL 2005, 2008, 2012, and 2014 and does support the Windows Internal Database.
Both plug-ins have advantages and disadvantages.

MS SQL dumping plug-in:
The MS SQL plug-in is also known as the "dumping" plug-in as it writes database.bak files out to disk, and then backs the dumped files up.
The .bak files take up disk space on the client side, but these files can be used as a locally available backup for restore purposes without having to retrieve data from the Storage Platform.
The SQL dump process takes longer than a VSS backup.
The MS SQL plug-in works by establishing a TCP session with the SQL Server, so is not compatible with the Windows Internal Database as this does not support TCP/IP at all.
The MS SQL plug-in can work over a network, so can connect to a SQL Server running on a different host.

VSS plug-in:
The VSS Plug-in uses VSS shadow copy space and Microsoft APIs, and writes to the toBackup folder directly.
Less disk space is used, but the last backup is not directly restorable to the SQL Server. The backup must always be first restored from the Storage Platform.
The VSS plug-in is faster than the dumping plug-in as it does not need to write out separate backup files, but instead takes a memory snapshot of the .mdf and .ldf files. The .mdf and .ldf files should be restored using the VSS plug-in.
The VSS plug-in works using memory, so is compatible with the Windows Internal Database and can back this up.
The VSS plug-in does not operate over networks, so must be run on the SQL Server host itself.

I’ve been backing up using one plug-in but I think the other plug-in may now be better for me. Can I switch over?
Yes, in principle. Bear in mind that the data selection will change and that the new data will be sent in full to the Storage Platform. The selection size will be different, so there may be implications with your Backup Account size limit.

What data counts towards the backup size?
For the MS SQL plug-in, this is the size of the dumped .bak files.
For the VSS plug-in, this is the combined size of all files that comprise the database itself (e.g. .ldf and .mdf files)

How is SQL log maintenance handled?
In the MS SQL plug-in, maintenance settings can be configured to enable or disable log truncation. If the settings are incorrect for your database you may encounter error messages similar to the following:

Message: 20:00:13 [MS SQL Server Plugin] Truncating log file for database master
Warning: 20:00:13 [MS SQL Server Plugin] Unable to export database localhost:1433.master: Cannot back up the log of the master database. Use BACKUP DATABASE instead.
Message: 20:00:14 [MS SQL Server Plugin] Exporting database longcat
Message: 20:00:16 [MS SQL Server Plugin] Truncating log file for database longcat
Warning: 20:00:16 [MS SQL Server Plugin] Unable to export database localhost:1433.longcat: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

You will continue to encounter error messages until the setting is changed within the plug-in, or the database recovery model itself is changed.
Log maintenance is only a consideration for the “FULL” or “BULK Logged” recovery models. If you are using “SIMPLE” mode with VSS, log file growth and truncation is not a concern.
No maintenance setting is needed for the database in the VSS plug-in, and it is a limitation of VSS itself that it will not truncate logs.

Note:
The VSS maintenance setting applies to Microsoft Exchange only.
If you encounter maintenance mode error messages in the MS-SQL Server plug-in, please also refer to FAQ article 200.

Tip: If using FULL or Bulk Logged recovery models with VSS, you can trunctate logs using the following methods:

  • Manually, using SQL Management Studio (refer to the "Shrink a File" Microsoft article for more information)
  • Automatically, using an SQL script with the Script plug-in. Refer to FAQ article 282 on how to achieve this.
     

How do I restore?
With the MS SQL plug-in, restore the files to disk and use SQL Management Studio to restore these to the SQL Server.
With the VSS Plug-in, the restore will take place directly from the Backup Client to the SQL Server, with the plug-in accessing the required VSS Writer to in turn access the SQL instance.
It is also possible to restore the .mdf and .ldf files to disk and then attach these to the database.

Are any special configuration steps needed if using the MS SQL plug-in?
Yes. The SQL Server needs to be configured to use TCP/IP connections, and with the bindings set to use to use a static IP address and static port. The static IP address can be 127.0.0.1 if the Attix5 Pro Backup Client is running on the SQL Server host and no other TCP/IP connections are being made.
A user with db_owner permissions for the database(s) to be backed up must also exist, and the plug-in should be set to log in as this user.
The SQL Server service user must have full access permissions to the export directory configured in the plug-in.

I have more than one SQL Server instance. Is this supported?
Yes. Make sure that the static IP addresses and ports in use do not clash, and configure a second instance within the MS SQL plug-in.

I would like to use a share on a UNC path with the MS SQL plug-in. Is this possible?
Yes. Add a UNC path to the client, and configure the plug-in to use a subdirectory within this as an export directory.
The SQL Server itself writes out the exported .bak files, so needs to be able to resolve the address and must have full control over the export directory. The SQL Server service user may need to be changed to achieve this.
Note: Configuring a share as an export directory will result in an error message. Creating a subdirectory inside the share, and using this as the export directory will resolve the error.

My database is running in a SQL Server cluster. Is this supported?
Yes. SQL Server clusters are supported. Using a separate client host, configure the plug-in to connect to the cluster name, and use a UNC share on the client as the export directory, following the appropriate details about UNC shares above.
In an active-passive cluster, the client host will carry on backing up when the cluster fails over from one cluster node to the other, as it uses the shared cluster name which remains constant.

I would like to back up the transaction logs only, and do so multiple times daily. How is it best to achieve this?
First of all, make sure that you have a full backup configured at least once per day through the MS SQL plug-in or the VSS plug-in.
Using SQL Management Studio, configure scheduled transaction log backups of the desired frequency to a directory.
Within Attix5 Pro, create an additional backup set with the transaction log backup folder selected, and with the backups set to run at the desired frequency.
As each transaction log backup completes, the file will be added to the selected folder and will be added to the backup selection automatically next time a backup runs.
Files will continue to be added throughout the day, and will be backed up to the Storage Platform when the next Attix5 Pro backup occurs.
There is no need to enforce a strict synchronisation between SQL transaction log backup and Attix5 Pro backup.
Periodically, e.g. once per day, set SQL Management Studio to purge older transaction log backups.
When restoring, first restore the full backup, and then the transaction logs to the desired point.

Article ID: 192
Last updated: 01 Apr, 2016
Revision: 13
Views: 7777
Comments: 0
Posted: 03 Oct, 2012 by -- .
Updated: 01 Apr, 2016 by Du Plessis S.
This article was:  
Prev   Next
Article 187 - Why does the OnDisk size for all Groups and all StorageServers...     Article 190 - Various Restore errors encountered on the Backup Client