How to: Prepare a Mirror Database for Mirroring (Transact-SQL)

Article
12/03/2008

The mirror database must exist before a database mirroring session can begin. The name of the mirror database must be the same as the name of the principal database. The database owner or system administrator can create the mirror database from a recent full backup of the principal database and at least one subsequent log backup. For mirroring to work, the mirror database must remain in the RESTORING state. Therefore, when you restore a backup to a mirror database, you must always use WITH NORECOVERY for every restore operation.

If mirroring has been removed and the mirror database is still in the RECOVERING state, you can restart mirroring. However, first, at least one log backup must be taken on the principal database. Then, on the mirror database, you must restore WITH NORECOVERY all log backups that were taken on the principal database since mirroring was removed.

Note

You cannot mirror the master, msdb, temp, or model system databases.

Procedure

To create a database for database mirroring

Make sure that both the principal and mirror server instances have Microsoft SQL Server 2005 installed.
Verify that the database uses the full recovery model.

For more information, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio) or sys.databases (Transact-SQL) and ALTER DATABASE (Transact-SQL).
Back up the principal database to a full database backup.

For more information, see How to: Back Up a Database (SQL Server Management Studio) or How to: Create a Full Database Backup (Transact-SQL).

Note

Alternatively, you can restore an existing full database backup and, optionally, a differential database backup, followed by every subsequent log backup.
Make sure that the system where you plan to create the mirror database possesses a disk drive with sufficient space to hold the database.
Unless the backup is on a network drive that is accessible from both systems, copy the database backup to that system.
To create the mirror database, restore the full database backup onto the mirror server instance. The RESTORE DATABASE database_name statement must specify WITH NORECOVERY, where database_name is the name of the database to be mirrored.

Note

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

Also, we recommend that, if possible, the path (including the drive letter) of the mirror database be identical to the path of the principal database.

If the file layouts 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 exist 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 Troubleshooting Database Mirroring Setup.

For more information, see RESTORE (Transact-SQL) and RESTORE Arguments (Transact-SQL).
Typically, at least one log backup must be taken on the principal database, copied to the mirror server, and restored on the mirror database (using WITH NORECOVERY). 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.

To apply the log backup to the mirror database, on the mirror server you can use either SQL Server or Transact-SQL:
- How to: Apply a Transaction Log Backup (Transact-SQL)
- How to: Restore a Transaction Log Backup (SQL Server Management Studio).
If any additional log backups have been taken on the principal database since the required log backup, you must also copy these to the mirror server and apply each of those log backups to the mirror database, starting with the earliest and always using WITH NORECOVERY.

Security Note:
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 How to: Set Up a Mirror Database to Use the Trustworthy Property.

Important

If database mirroring is stopped, all subsequent log backups taken on the principal database must be applied to the mirror database before mirroring can be restarted.

Example

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 AdventureWorks sample database, which uses the simple recovery model by default.

To use database mirroring with the AdventureWorks 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.
Restore the full backup WITH NORECOVERY 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.

Share via

How to: Prepare a Mirror Database for Mirroring (Transact-SQL)

Procedure

To create a database for database mirroring

Example

See Also

Tasks

Concepts

Other Resources

Help and Information

Additional resources