Microsoft SQL Server
How to backup Microsoft SQL Servers using Zmanda
When the Zmanda Client for Windows is configured and licensed for Microsoft SQL backup, it uses the Microsoft Volume Shadow Services to back up the SQL database(s) and logs. Depending on which service is enabled on the server, ZWC uses either the SQL Server VSS Writer or the MSDE Writer. ZWC does not use the MS-SQL API for backup.
ZWC performs an incremental backup of databases and transaction log files just as it does any other file system backup (based on modification time). ZWC does not support transaction log-based backup because this mechanism is not supported either by MSDE or the SQLServer.
ZWC does not truncate transaction logs after a backup. If you wish to truncate transaction logs after a backup, this can be accomplished using a post-backup plugin.
Requirements for Microsoft SQL Server Backup and Restore
These instructions assume you have already installed and licensed the Amanda Enterprise server and the Microsoft SQL Server being backed up. There are a number of additional requirements:
At present, Zmanda exclusively facilitates the backup of MSSQL servers with the instance name set as MSSQLSERVER.
Certain MSSQL servers may be installed with instance names such as SQLEXPRESS for the Express Edition. However, please note that Zmanda does not currently offer support for these instances.
Please ensure that the instance name of your MSSQL server is specifically set to MSSQLSERVER and not any alternative name.
The amandabackup user should be given the sysadmin role within the SQL server
Make sure that your versions of Windows and SQL Server conform to the tested platforms listed on the Zmanda Network Supported Platforms page.
The Volume Shadow Copy Service must be enabled. This means that its startup type must be either automatic or manual.
ZWC will only back up MS SQL databases that are in a Mounted state.
Microsoft recommends that MSSQL and System State back-ups should not be run simultaneously.
ZWC only backs up the MS SQL databases. It does not back up other MSSQL files such as program installation files, etc. To protect an MS SQL server from a disaster, make sure that you create a separate disk list entry to back up the other crucial MS SQL files.
ZWC does not support component-based backup. It backs up all the mounted databases in the MSSQL server. Because it is not component-based, only databases with the Simple Recovery model are supported.
ZWC does not use VSS for restores. Because ZWC does not support component-based backup, post-restore Roll Forward is not supported.
Ensure that the databases intended for restoration are present or created on the MS SQL source instance before initiating the restore process.
Configuring Microsoft SQL Server Backups from the ZMC Backup What Page
Create a dedicated backup set for each Microsoft SQL Server you intend to back up. On the Source page, you are prompted to select what type of object you want to back up. Choose MSSQL under Database, and the following options are displayed:
Host Name
The name of the machine running the Microsoft SQL server you intend to back up.
Data Deduplication, Encryption and Compression
Select the desired options for backup images before transfer to the Amanda backup server.
Discover
The Discover button discovers all the available databases to be backed up. All the databases can be selected using the Select All button. They can also be manually added if preferred.
Click Save to commit the changes.
Please make sure no other backup software is scheduled to run at the same time as the Amanda SQL server backup. You should also make sure to only add the MSSQL Source to a single backup set.
When the backup starts, the Zmanda Windows Client collects a list of mounted databases (including their physical paths) from the SQL Writer. ZWC then takes a snapshot of the drives on which the databases are located. During snapshot creation, the SQL Writer stops all I/O operations to the databases. Once the snapshot is done, the SQL Writer resumes I/O operations, and ZWC takes a backup of the database files from the snapshot volume. The snapshot is deleted after the backup is completed.
Configuring Microsoft SQL Server Restores from the ZMC
When you have selected the disk list entry that includes the MS SQL databases for restore, the Restore What page displays the following options:
Select all the host databases and log files on the Restore What page. Click Next to display the Restore Where page:
If restoring to the original server, make sure that MS SQL is installed in the same location as when the backup was run. The databases and log file locations should also match the original configuration. When no directory is specified in the ''Restore Where'' page, the Zmanda Windows Client stops the host SQL services before beginning the restore, and re-starts after the restore is complete. You must then recover the databases on the server manually.
If restoring to an alternative location, simply specify that directory. The ZWC will then perform a simple copy operation without attempting any pre- or post-restore operations. No SQL recovery is required in this type of restore.
Troubleshooting
If an MS SQL backup fails, follow these troubleshooting steps:
Check the Windows Event Viewer for any VSS or SQL Server VSS Writer or MSDE Writer errors. Run the command vssadmin list writers on the Windows command prompt and check that the SQL Server VSS/MSDE Writers are in a stable state. If not (or if there are any VSS errors, restart the Writers and Volume Shadow Copy Service).
Make sure that the SQL databases are in a Mounted state.
If the SQL databases do not mount, refer to Microsoft documentation.
Last updated
