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
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;
