MS SQL

Table of Contents 

Microsoft SQL Server Backup

This chapter provides information on how Amanda Enterprise backs up Microsoft SQL server. It also provides information on how to configure SQL server backups, perform backup and recovery of SQL server. The last section provides an example on how to configure Amanda Enteprise for disaster recovery of a Windows server running Microsoft SQL server.

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 can perform full and differential backup of databases and transaction log files using Volume Shadow Services. Log only backups are not supported.

ZWC supports Simple Recovery Model, Full Recovery Model and Bulk Recovery Model for SQL server databases. If the database is in Simple Recovery Model, the full backup will contain .MDF, .LDF and .NDF (in case of filegroups) files in the backup image. The differential backup will contain .LDF files. If the database is in Full Recovery Model, the full backup will contain .MDF, .LDF and .NDF (in case of filegroups) files in the backup image. The differential backup will contain .MDF-Offset (partial file of .MDF database that has changed), .LDF and .TRN (transaction logs flushed to the disk) in the backup image. If the database is in Bulk-Log Recovery Model, the full backup will contain .MDF, .LDF and .NDF (in case of filegroups) files in the backup image. The differential backup will contain .MDF-Offset (partial file of .MDF database), .LDF and .TRN (transaction logs flushed to the disk) files in the backup image.

During full backup, SQL server transaction logs are not truncated. Transaction logs are backed up during differential backup. Transaction logs are truncated by Amanda during differential backup for Full and Bulk-Log Recovery models. In Simple Recovery Model, Logs are truncated automatically by SQL server.  Log truncation removes transactions from the database log file but it doesn't reduce the amount of space reserved for the log files. SQL server expects the transaction log will grow to its pre-truncation size and so it does not released the disk space allocated to transaction logs.  To reduce the disk space used for transaction logs, you should shrink log file using Transact-SQL command DBCC SHRINK FILE <filename>, <desired shrink size in Megabytes>. You can reclaim the most disk space immediately following a transaction log truncation operation.

If you are doing SQL server log shipping, you should perform only full backups.

The master and model databases are only backed up as part of the full backups. Any changes to these databases are not included in differential backups. During restore of master database, SQL server is stopped and is restarted after restoration.

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:

  • 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.
  • The amandabackup user is used to perform backup and recovery. Appropriate permissions for backup and recovery must be provided for this user. The easiest way to provide backup/restore permissions is to add the user to sysadmin server role. Use SQL Management Studio: Go to Security->Logins->add new login->add the amandabackup user account and give it necessary permissions for backup and recovery. If you are using amandabackup domain user, the domain-wide amandabackup user should have same permissions on the SQL server.

  • If SQL server is running as different user other than local system user context, the user that SQL server is running as should have write permissions to <ZWC installation directory>\misc folder (for example: C:\Program Files\Zmanda\Zmanda Client for Windows - Server Edition(x64)\misc\ )on the Windows server.

  • ZWC will only back up MS SQL databases that are in 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, SSL certificates 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.
  • For MS SQL server 2000, only full backups are supported. Block level differential backup is not supported because SQL server 2000 VSS writer does not return changed block information.

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 Backup What page you are prompted to select what type of object you want to back up. Choose mssql, and the following options are displayed:

AE336-BackupWhat-SQLServer.PNG

Host Name
The name of the windows machine running the Microsoft SQL server you intend to back up.
Databases
You can select a specific database or all databases configured on the windows machine. Click Discover button to discover the SQL server databases.
Compression
Select where compression should be performed - Server or Client and How it should be performed - fast or best.
Encryption
         Select where encryption should be performed - Server or Client.

After you have set the options, click the Add button to add the Microsoft SQL Server object to the backup set.  ZMC will perform automatic configuration check when the object is added to the backup set. You can then configure the backup set just as you would any other by setting the options on Backup Where, Backup How, and Backup When, etc.

Please make sure no other backup software is scheduled to run at the same time as the Amanda SQL server backup.

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: 

Fig 1. MS SQL Restore What Options

Select all the host databases and log files on the Restore->What page. Click Next to display the Restore Where page:

restore where cropped.png

  • 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 restores the SQL database files from the backup by communicating with host SQL server and SQL recovery is performed. The SQL server is restarted if the master database is restored.
  • If restoring to an alternative location on the original server, 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 performed in this type of restore.
  • If restoring to an alternate server and original location, make sure that MS SQL is installed in the same location as when the backup was run. The System Name, databases and log file locations should also match the original server from where backup was performed. When no directory is specified in the '''Restore Where''' page, the Zmanda Windows Client restores the SQL database files from the backup by communicating with host SQL server and SQL recovery is performed.  The SQL server is restarted if the master database is restored.

Troubleshooting

If an MS SQL backup fails, follow these troubleshooting steps:

  1. Check the Windows Event Viewer for any VSS or SQL Server VSS Writer or MSDE Writer errors. Run the command vssadmin list writers at 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).
  2. Make sure that the SQL databases are in Mounted state.

 

SQL server Disaster Recovery

Disaster recovery refers to restoring the system and data in the event of partial or complete failure of a computer because of natural or technical causes. Following sections explain how to prepare for Disaster Recovery of a server running Microsoft SQL server and recover the system as part of Disaster recovery.

Backups for Disaster Recovery

Use Amanda Enterprise to configure backups of the following server components as separate DLEs in the ZMC Backup What page.  It is recommended that all these DLEs are in the same backup set.

  • System Drive (default: C:\). Exclude all system and temporary files. This is drive where the Windows server software is installed.
  • SQL server installation directory if it is not installed in C:\ (default location: C:\Program Files\Microsoft SQL Server\). You can exclude the Database and installation directory as they backed up as part of SQL server database backup
  • System State Backup of the server. This backup will contain all Registry and other system information.
  • SQL server backup of all SQL server databases running on the server.

 

Disaster Recovery procedure

  1. Install the same version of Windows server as the original server. Make sure that the new server has exact same drive partitions, NTFS file systems, Windows installation directory and Computer Name as the original server. Do not join any domain or Workgroup.
  2. Install Zmanda Client for Windows in the same directory location as the original server.
  3. Use Zmanda Management Console on the Amanda server and Access the ZMC Restore What page. Select the backup date and time to be restore (if you do not want to restore from the last full backup)
    1. Select System Drive (such as C:\) in the Alias/Directory/Path field as shown below. SQL-Restore-What1.png
    2. Click on Express Restore.  All backups from the last full backup to current time (or time selected) will be selected for restoration.
    3. ZMC Restore Where page: Enter the name or the IP address of the SQL Server in the Destination Host field, amandabackup as the Destination Username, Original Location as the location.
    4. Review the Restore From and Restore To  settings on the ZMC Restore Restore page. Click Restore to start the restore process.
  4. Repeat Step 3 for SQL server installation directory if it is not part of default Windows system drive.
  5. Repeat Step 3 for System State backup. Reboot the SQL server machine after successful restoration of the System State.
  6. After reboot, the server will have the same Computer name, Domain/Workgroup, users and groups as the Original server. All the applications will also be in an installed state.
  7. The SQL Server will be installed on the machine but is not in a running state since the master & model databases have not been restored.
  8. Restore the master and model databases from the Full backup of the SQL server. Select the Full Backup time stamp from the ZMC Report Summary page by clicking the time stamp. You will be taken to the Restore What page with the backup time stamp already filled in. The master and model databases are only part of full  backups.
  9. Explore the backup image and select the files for master and model databases for restoration as shown below. Select the same parameters as Step 3 in the Restore Where page and start the restore process on the Restore Restore page. SQL-Restore-What2.jpg
  10. After the databases have been restored to the original location, start the SQL Instance services. SQL will run a database recovery and start the SQL server instance.
  11. After SQL Instance is in the running mode, the other databases will need to be restored. Repeat the Step 3 for restoring full and differential backups of other databases.  When other databases are being restored to original location, Microsoft VSS will automatically run a recovery of the SQL databases and bring them to a consistent state.