Can Redstor Pro be used to back up SQL?
Yes, Redstor Pro can back up SQL Server 2000, 2005, 2008 and 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?
Redstor Pro uses plugins to facilitate SQL backups. The appropriate plugin to use differs between SQL Server versions.
Which plugins can be used for SQL backup?
The MS SQL plugin and the VSS plugin.
The MS SQL plugin is compatible with SQL 2000, 2005, 2008, 2012, and 2014 but does not support the Windows Internal Database.
The VSS plugin is compatible with SQL 2005, 2008, 2012, and 2014 and does support the Windows Internal Database.
Both plugins have advantages and disadvantages.
MS SQL dumping plugin:
The MS SQL plugin is also known as the "dumping" plugin 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 plugin works by establishing a TCP session with the SQL Server, so it is not compatible with the Windows Internal Database as this does not support TCP/IP at all.
The MS SQL plugin can work over a network, so it can connect to a SQL Server running on a different host.
The VSS Plugin 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 plugin is faster than the dumping plugin 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 plugin.
The VSS plugin works using memory, so is compatible with the Windows Internal Database and can back this up.
The VSS plugin does not operate over networks, so must be run on the SQL Server host itself.
I've been backing up using one plugin but I think the other plugin 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 plugin, this is the size of the dumped .bak files.
For the VSS plugin, 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 plugin, 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 plugin, 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 plugin, and it is a limitation of VSS itself that it will not truncate logs.
Note: The VSS maintenance setting applies to Microsoft Exchange only.
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 plugin.
How do I restore?
With the MS SQL plugin, restore the files to disk and use SQL Management Studio to restore these to the SQL Server.
With the VSS plugin, the restore will take place directly from the backup client to the SQL Server, with the plugin 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 plugin?
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 Redstor 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 plugin 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 plugin.
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 plugin.
I would like to use a share on a UNC path with the MS SQL plugin. Is this possible?
Yes. Add a UNC path to the client, and configure the plugin 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 plugin 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 do I achieve this?
First of all, make sure that you have a full backup configured at least once per day through the MS SQL plugin or the VSS plugin.
Using SQL Management Studio, configure scheduled transaction log backups of the desired frequency to a directory.
In Redstor 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 Redstor Pro backup occurs.
There is no need to enforce a strict synchronisation between SQL transaction log backup and Redstor 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.