Prepare Secondary Database for Mirroring, SQL Server

When you plan to Mirroring Database, first you have to prepare the Secondary Database.

Follow below steps to prepare the secondary database for mirroring...

STEP 1:  Take Backup of Principal Database.

• Take Full Database Backup of Principal Database.
• BACKUP DATABASE  TEST_DB  TO DISK = N'C:\TEST\TEST_DB.BAK'
• Take a Transaction Log Backup of Principal Database 
• BACKUP LOG  TEST_DB  TO DISK = 'C:\TEST\TEST_DB.TRN'

NOTE: Do not make any changes in the Principal database after the Log Backup has been done.

STEP 2: Restore Full and Log Backup to Secondary Database.

• Copy the Backup files to a location which is accessible from Secondary Database
• Restore Full Backup WITH NORECOVERY to Secondary Database
• Restore Transaction Log Backup WITH NORECOVERY to Secondary Database

Restore Database

RESTORE DATABASE  TEST_DB  

FROM DISK = N'C:\TEST\TEST_DB.BAK'

WITH NORECOVERY;

If you want to replace the existing database:

RESTORE DATABASE  TEST_DB  

FROM DISK = N'C:\TEST\TEST_DB.BAK'

WITH NORECOVERY, REPLACE;

NOTE: If REPLACE not worked for existing database, ALTER database as SINGLE_USER. Then try again to restore with NORECOVERY.

ALTER DATABASE TEST_DB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

If you want to move the data file (.mdf / .ldf) location:

-- logical_file_name  is the name of the .mdf file in the database.

-- physical_path is the drive/network location, which is accessible from the secondary database

-- Retrive DB File name

-- select * from sys.database_files

RESTORE DATABASE  TEST_DB  

FROM DISK = N'C:\TEST\TEST_DB.BAK'

WITH NORECOVERY,

MOVE  logical_file_name to 'physical_path\TEST_DB.mdf', 

MOVE  logical_file_name_log to 'physical_path\TEST_DB.ldf' ;

Restore Database Log

RESTORE LOG TEST_DB

   FROM DISK = 'C:\TEST\TEST_DB.TRN'

   WITH NORECOVERY;