Manually Prepare a Secondary Database for an Availability Group (SQL Server)

This topic describes how to prepare a secondary database for an AlwaysOn availability group in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or PowerShell. Preparing a secondary database requires two steps: (1) restoring a recent database backup of the primary database and subsequent log backups onto each server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY, and (2) joining the restored database to the availability group.

Prerequisites and Restrictions

Make sure that the system where you plan to place database possesses a disk drive with sufficient space for the secondary databases.

The name of the secondary database must be the same as the name of the primary database.

Use RESTORE WITH NORECOVERY for every restore operation.

If the secondary database needs to reside on a different file path (including the drive letter) than the primary database, the restore command must also use the WITH MOVE option for each of the database files to specify them to the path of the secondary database.

If you restore the database filegroup by filegroup, be sure to restore the whole database.

After restoring the database, you must restore (WITH NORECOVERY) every log backup created since the last restored data backup.

Recommendations

On stand-alone instances of SQL Server, we recommend that, if possible, the file path (including the drive letter) of a given secondary database be identical to the path of the corresponding primary database. This is because if you move the database files when creating a secondary database, a later add-file operation might fail on the secondary database and cause the secondary database to be suspended.

Before preparing your secondary databases, we strongly recommend that you suspend scheduled log backups on the databases in the availability group until the initialization of secondary replicas has completed.

Security

When a database is backed up, the TRUSTWORTHY database property is set to OFF. Therefore, TRUSTWORTHY is always OFF on a newly restored database.

Permissions

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. For more information, see BACKUP (Transact-SQL).

When the database being restored does not exist on the server instance, the RESTORE statement requires CREATE DATABASE permissions. For more information, see RESTORE (Transact-SQL).

Unless you already have a recent database backup of the primary database, create a new full or differential database backup. As a best practice, place this backup and any subsequent log backups onto the recommended network share.

Create at least one new log backup of the primary database.

On the server instance that hosts the secondary replica, restore the full database backup of the primary database (and optionally a differential backup) followed by any subsequent log backups.

On the RESTORE DATABASEOptions page, select Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY).

If the file paths of the primary database and the secondary database differ, for example, if the primary database is on drive 'F:' but the server instance that hosts the secondary replica lacks an F: drive, include the MOVE option in your WITH clause.

Unless you have a recent full backup of the primary database, connect to the server instance that hosts the primary replica and create a full database backup. As a best practice, place this backup and any subsequent log backups onto the recommended network share.

On the server instance that hosts the secondary replica, restore the full database backup of the primary database (and optionally a differential backup) followed by all subsequent log backups. Use WITH NORECOVERY for every restore operation.

If the file paths of the primary database and the secondary database differ, for example, if the primary database is on drive 'F:' but the server instance that hosts the secondary replica lacks an F: drive, include the MOVE option in your WITH clause.

If any log backups have been taken on the primary database since the required log backup, you must also copy these to the server instance that hosts the secondary replica and apply each of those log backups to the secondary database, starting with the earliest and always using RESTORE WITH NORECOVERY.

Note

A log backup would not exist if the primary database has just been created and no log backup has been taken yet or if the recovery model has just been changed from simple to full.

After modifying the recovery model of the database from SIMPLE to FULL, create a full backup, which can be used to create the secondary database. Because the recovery model has just been changed, the WITH FORMAT option is specified to create a new media set. This is useful to separate the backups under the full recovery model from any previous backups made under the simple recovery model. For the purpose of this example, the backup file (C:\
AdventureWorks2012
.bak) is created on the same drive as the database.

Note

For a production database, you should always back up to a separate device.

On the server instance that hosts the primary replica (INSTANCE01), create a full backup of the primary database as follows:

Copy the full backup to the server instance that hosts the secondary replica.

Restore the full backup, using RESTORE WITH NORECOVERY, onto the server instance that hosts the secondary replica. The restore command depends on whether the paths of primary and secondary databases are identical.

If the paths are identical:

On the computer that hosts the secondary replica, restore the full backup as follows:

If the path of the secondary database differs from the path of the primary database (for instance, their drive letters differ), creating the secondary database requires that the restore operation include a MOVE clause.

Important

If the path names of the primary and secondary databases differ, you cannot add a file. This is because on receiving the log for the add file operation, the server instance of the secondary replica attempts to place the new file in the same path as used by the primary database.

For example, the following command restores a backup of a primary database that resides in the data directory of the default instance of SQL Server 2014, C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. The restore database operation must move the database to the data directory of a remote instance of SQL Server 2014 named (AlwaysOn1), which hosts the secondary replica on another cluster node. There, the data and log files are restored to the C:\Program Files\Microsoft SQL Server\MSSQL12.ALWAYSON1\MSSQL\DATA directory . The restore operation uses WITH NORECOVERY, to leave the secondary database in the restoring database.

If any additional log backups occur before the database joins the secondary replica, you must also restore all of those log backups, in sequence, to the server instance that hosts the secondary replica using RESTORE WITH NORECOVERY.

For example, the following Transact-SQL statement restores two additional logs from E:\MyDB1_log.bak:

If you need to create a recent backup of the primary database, change directory (cd) to the server instance that hosts the primary replica.

Use the Backup-SqlDatabase cmdlet to create each of the backups.

Change directory (cd) to the server instance that hosts the secondary replica.

To restore the database and log backups of each primary database, use the restore-SqlDatabase cmdlet, specifying the NoRecovery restore parameter. If the file paths differ between the computers that host the primary replica and the target secondary replica, also use the RelocateFile restore parameter.

Note

To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

Sample Backup and Restore Script and Command

The following PowerShell commands back up a full database backup and transaction log to a network share and restore those backups from that share. This example assumes that the file path to which the database is restored is the same as the file path on which the database was backed up.