MSSQL: How to mirror a database from one server to another.

How to mirror a database from one server to another.

Mirroring a database to a second node is fairly simple. There are basically 4 steps where Restore will be in NORECOVERY mode.

Prerequisite

1. The database must be in a FULL RECOVERY MODE. 
2. Identical windows and SQL Server logons and privileges.

Verify database recovery model.

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'VCDB_2016' ; 

If it is not in FULL RECOVERY such as SIMPLE or BulkLogged, switch it to FULL.

USE MASTER

ALTER DATABASE VCDB_2016 SET RECOVERY FULL;

Step 1: Full Database Backup

BACKUP DATABASE VCDB_2016 TO DISK = N'C:\BACKUP\VCDB_2016_BACKUP.bak' WITH FORMAT;

Step 2: Backup transaction logs

BACKUP LOG VCDB_2016 TO  DISK = N'E:\BACKUP\VCDB_2016_BACKUP.trn'

GO

Both Database and Transaction Logs backup.

Step 3: Copy to node 2 and Restore Database with NORECOVERY

RESTORE DATABASE VCDB_2016 FROM DISK = N'E:\BACKUP\VCDB_2016_BACKUP.bak' WITH NORECOVERY

GO

Step 4: Copy to node 2 and Restore with Transaction Log with NORECOVERY

RESTORE LOG VCDB_2016 FROM DISK = N'E:\BACKUP\VCDB_2016_BACKUP.trn' WITH FILE=1, NORECOVERY

GO