SQL Server database mirroring

Submitted by madvip on Thu, 04/10/2008 - 14:44.

You can find lots of online resources which describe how to mirror an MSSQL database, however the ones I've seen all manage to fail to explain all the necessary prerequisites. The first step to correctly configure a database for mirroring is to take a full backup of the database and another backup of its transaction log file:



BACKUP DATABASE my_db TO DISK='c:\my_db_full.BAK' WITH INIT 
BACKUP LOG my_db TO DISK='c:\my_db_log.TRN' WITH INIT

Then we need to restore the database (but not recovering it, so we keep it closed for mirroring) on our destination site:

--- First we need to deduce the logical file names from the backup extract
RESTORE FILELISTONLY FROM DISK='c:\CreditCheck_full.BAK'

--- Restore database
RESTORE DATABASE my_db FROM DISK='c:\my_db_full.BAK'
	WITH MOVE 'my_db_data' TO 'D:\SQL_DATA\my_db_data.mdf',
	MOVE 'my_db_log' TO 'D:\SQL_DATA\my_db_log.ldf',
	MOVE 'my_db_log2' TO 'D:\SQL_DATA\my_db_log2.ldf',
	NORECOVERY;

---Restore Transaction Log
RESTORE LOG my_db FROM DISK='c:\my_db_log.TRN' WITH NORECOVERY;

Once our setup is complete, you can follow an easy wizard to create the mirror. The steps are as follows:
- Go to the database's properties windows and select 'Mirroring'
- Click on 'Configure Security'
- I normally don't configure a Witness Server so I can use asynchronous mirroring
- After filling the necessary details, click on Start mirroring and voila!

»