Prepare a Mirror Database for Mirroring (SQL Server)

Artikkeli
03/03/2023

Applies to: SQL Server

Before a database mirroring session can start, the database owner or system administrator must make sure that the mirror database has been created and is ready for mirroring. Creating a new mirror database minimally requires taking a full backup of the principal database and a subsequent log backup and restoring them both onto the mirror server instance, using WITH NORECOVERY.

This topic describes how to prepare a mirror database in SQL Server by using SQL Server Management Studio or Transact-SQL.

Before you begin:

Requirements

Limitations and Restrictions

Recommendations

Security
To Prepare an Existing Mirror Database to Restart Mirroring
To Prepare a New Mirror Database
Follow Up: After Preparing a Mirror Database
Related Tasks

Before You Begin

Requirements

The principal and mirror server instances must be running on the same version of SQL Server. While it is possible for the mirror server to have a higher version of SQL Server, this configuration is only recommended during a carefully planned upgrade process. In such a configuration, you run the risk of an automatic failover, in which data movement is automatically suspended because data cannot move to a lower version of SQL Server. For more information, see Upgrading Mirrored Instances.
The principal and mirror server instances must be running on the same edition of SQL Server. For information about support for database mirroring in SQL Server, see Editions and supported features of SQL Server 2022.
The database must use the full recovery model.

For more information, see View or Change the Recovery Model of a Database (SQL Server) or sys.databases (Transact-SQL) and ALTER DATABASE (Transact-SQL).
The name of the mirror database must be the same as the name of the principal database.
The mirror database must be in the RESTORING state for mirroring to work. When preparing a mirror database, you must use RESTORE WITH NORECOVERY for every restore operation. Minimally, you will need to restore WITH NORECOVERY a full backup of the principal database, followed by all subsequent log backups.
The system where you plan to create the mirror database must possesses a disk drive with sufficient space to hold the mirror database.

Limitations and Restrictions

You cannot mirror the master, msdb, temp, or model system databases.
You cannot mirror a database that belongs to an Always On availability group.

Recommendations

Use a very recent full database backup or a recent differential database backup of the principal database.
If a log backup job is scheduled to run very frequently on the principal database, you might have to disable the backup job until mirroring has started.
If possible, the path (including the drive letter) of the mirror database should be identical to the path of the principal database.

If the file paths must differ, for example, if the principal database is on drive 'F:' but the mirror system lacks an F: drive, you must include the MOVE option in the RESTORE STATEMENT.

Important

Adding a file during a mirroring session without impacting the session requires that the path of the file exists on both servers. Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended. For information about dealing with a failed create-file operation, see Troubleshoot Database Mirroring Configuration (SQL Server).
If the principal database has any full-text catalogs, we recommend that you see Database Mirroring and Full-Text Catalogs (SQL Server).
For a production database, always back up to a separate device.

Security

TRUSTWORTHY is set to OFF when a database is backed up. Therefore, TRUSTWORTHY is always OFF on a new mirror database. If the database needs to be trustworthy after a failover, additional setup steps are necessary. For more information, see Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL).

For information about enabling automatic decryption of the database master key of a mirror database, see Set Up an Encrypted Mirror Database.

Permissions

Database owner or system administrator.

To Prepare an Existing Mirror Database to Restart Mirroring

If mirroring has been removed and the mirror database is still in the RECOVERING state, you can restart mirroring.

Take at least one log backup on the principal database. For more information, see Back Up a Transaction Log (SQL Server).
On the mirror database, use RESTORE WITH NORECOVERY to restore all log backups taken on the principal database since mirroring was removed. For more information, see Restore a Transaction Log Backup (SQL Server).

To Prepare a New Mirror Database

To prepare a mirror database

Note

For a Transact-SQL example of this procedure, see Example (Transact-SQL), later in this section.

Connect to principal server instance.
Create either a full database backup or a differential database backup of the principal database.
- Create a Full Database Backup (SQL Server)
- Create a Differential Database Backup (SQL Server).
Typically, you need to take at least one log backup on the principal database. However, a log backup might be unnecessary, if the 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.
- Back Up a Transaction Log (SQL Server)
Unless the backups are on a network drive that is accessible from both systems, copy the database and log backups to the system that will host the mirror server instance.
Connect to mirror server instance.
Using RESTORE WITH NORECOVERY, create the mirror database by restoring the full database backup and, optionally, the most recent differential database backup, onto the mirror server instance.

Note

If you restore the database filegroup by filegroup, be sure to restore the whole database.
- Restore a Database Backup Using SSMS
- RESTORE (Transact-SQL) and RESTORE Arguments (Transact-SQL).
Using RESTORE WITH NORECOVERY, apply any outstanding log backup or backups to the mirror database.
- Restore a Transaction Log Backup (SQL Server)

Example (Transact-SQL)

Before you can start a database mirroring session, you must create the mirror database. You should do this just before starting the mirroring session.

This example uses the AdventureWorks2022 sample database, which uses the simple recovery model by default.

To use database mirroring with the AdventureWorks2022 database, modify it to use the full recovery model:
```
USE master;  
GO  
ALTER DATABASE AdventureWorks   
SET RECOVERY FULL;  
GO  
```
After modifying the recovery model of the database from SIMPLE to FULL, create a full backup, which can be used to create the mirror 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:\AdventureWorks.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 principal server instance (on PARTNERHOST1), create a full backup of the principal database as follows:
```
BACKUP DATABASE AdventureWorks   
    TO DISK = 'C:\AdventureWorks.bak'   
    WITH FORMAT  
GO  
```
Copy the full backup to the mirror server.
Using RESTORE WITH NORECOVERY, restore the full backup onto the mirror server instance. The restore command depends on whether the paths of principal and mirror databases are identical.
- If the paths are identical:
  
  On the mirror server instance (on PARTNERHOST5), restore the full backup as follows:
```
RESTORE DATABASE AdventureWorks   
    FROM DISK = 'C:\AdventureWorks.bak'   
    WITH NORECOVERY  
GO  
```
- If the paths differ:
  
  If the path of the mirror database differs from the path of the principal database (for instance, their drive letters differ), creating the mirror database requires that the restore operation include a MOVE clause.
  
  Important
  
  If the path names of the principal and mirror databases differ, you cannot add a file. This is because on receiving the log for the add file operation, the mirror server instance attempts to place the new file in the location used by the principal database.
  
  For example, the following command restores a backup of a principal database residing in C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\ to a different location, D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\, where the mirror database is to reside.
```
RESTORE DATABASE AdventureWorks  
   FROM DISK='C:\AdventureWorks.bak'  
   WITH NORECOVERY,   
      MOVE 'AdventureWorks_Data' TO   
         'D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\AdventureWorks_Data.mdf',   
      MOVE 'AdventureWorks_Log' TO  
         'D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\AdventureWorks_Log.ldf';  
GO  
```
After you create the full backup, you must create a log backup on the principal database. For example, the following Transact-SQL statement backs up the log to the same file used by the preceding full backup:
```
BACKUP LOG AdventureWorks   
    TO DISK = 'C:\AdventureWorks.bak'   
GO  
```
Before you can start mirroring, you must apply the required log backup (and any subsequent log backups).

For example, the following Transact-SQL statement restores the first log from C:\AdventureWorks.bak:
```
RESTORE LOG AdventureWorks   
    FROM DISK = 'C:\AdventureWorks.bak'   
    WITH FILE=1, NORECOVERY  
GO  
```
If any additional log backups occur before you start mirroring, you must also restore all of those log backups, in sequence, to the mirror server using WITH NORECOVERY.

For example, the following Transact-SQL statement restores two additional logs from C:\AdventureWorks.bak:
```
RESTORE LOG AdventureWorks   
    FROM DISK = 'C:\AdventureWorks.bak'   
    WITH FILE=2, NORECOVERY  
GO  
RESTORE LOG AdventureWorks   
    FROM DISK = 'C:\AdventureWorks.bak'   
    WITH FILE=3, NORECOVERY  
GO  
```

For a complete example of setting up database mirroring, showing security setup, preparing the mirror database, setting up the partners, and adding a witness, see Setting Up Database Mirroring (SQL Server).

Follow Up: After Preparing a Mirror Database

If any additional log backups have been taken since your most recent RESTORE LOG operation, you must manually apply every additional log backup, using RESTORE WITH NORECOVERY.
Start the mirroring session. For more information, see Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio) or Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL).
If you disabled the backup job on the principal database, reenable the job.
If the database needs to be trustworthy after a failover, extra setup steps are necessary after mirroring begins. For more information, see Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL).

Jaa