March 26, 2013

my SQL server mirroring script and guide

Note: Script below is only for guide.
Credits to Joan Peralta for the initial guide.

Step 1 - backup Database

BACKUP DATABASE EBP TO DISK = 'T:\ebp_backup\ebp_326' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Step 2 - backup log

BACKUP LOG EBP TO DISK = 'T:\ebp_backup\ebplog_326' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Step 3 - Copy the physical files to known local standby disk (with enough space to carry backups) such as i:\usr\sap

Step 4 - restore database to Standby system. (Make sure no recovery option is selected)

RESTORE DATABASE EBP from disk = 'i:\usr\sap\ebp_backup\ebp_326' with norecovery, replace,
move 'EBPDATA1' to 'R:\EBP\DATA1\EBPdata1.mdf',
move 'EBPDATA2' to 'R:\EBP\DATA2\EBPdata2.ndf',
move 'EBPDATA3' to 'R:\EBP\DATA3\EBPdata3.ndf',
move 'EBPDATA4' to 'R:\EBP\DATA4\EBPdata4.ndf',
move 'EBPLOG1' to 'R:\EBP\log1\EBPlog1.ldf'
go

Step 5 - Restore log to standby system

RESTORE LOG EBP FROM DISK = N'i:\usr\sap\ebp_backup\ebplog_326' WITH NORECOVERY, NOUNLOAD, STATS = 10

Step 6 - Using source Database EBP > Right Click to Properties
>Go to Mirroring 
>Configure security
>Select principal node
> Next
>Select mirror node
>Next
>Finish

Step 7 - Start Mirroring