Setup a Mirror

[snootalope]

I have to reestablish a mirror set we had between our SQL 2005 servers, however they sit about 15 miles apart from each other. I know I have to backup the data file and transaction log from the to be principal machine and restore it to the to be mirror before any changes are made to the principal database.

Is there a way to pause or lock the to be principal server so nothing changes between the time it takes me to get the backup db and log to the mirror?

 

[olahallengren]

You can establish database mirroring with the database online and with updating users all the time.

The transactions since the last log backup are transferred to the mirror server after you issue the SET PARTNER command (the status changes from Synchronizing to Synchronized).

Ola Hallengren

http://ola.hallengren.com

 

[snootalope]

Ok, here's what I'm thinking will work for this, please tell me if I'm wrong. I'm going off of the microsoft technet how to:

Backup my principal like so:

BACKUP DATABASE IR
TO DISK = 'C:\IR.bak'
WITH FORMAT
GO

Copy this file (which is about 7GB) to the mirror and restore like so:

RESTORE DATABASE IR_Mirror
FROM DISK = 'C:\IR.bak'
WITH NORECOVERY
GO

Then, I backup the log on the princiapl:

BACKUP LOG IR
TO DISK = 'C:\IRL.bak'
GO

and restore it to the mirror:

RESTORE LOG IR_Mirror
FROM DISK = 'C:\IRL.bak'
WITH FILE=1, NORECOVERY
GO

After the log is restored, I should be able to setup the mirror correct? I've got the maintenance plan disabled that backs up our transaction log every hour so I'm only planning on creating the one log after creating a full backup of the database to get the mirror established.

Does this sound about right?

 

[olahallengren]

I think that your plan looks about right. Just a few things.

The database should have the same name on the two servers, when you establish database mirroring.

I think that log backups normally have the .trn file extension.

Ola Hallengren

http://ola.hallengren.com