Database Mirroring for Newbies in SQL 2005

-- Take a backup of your database from the principle SQL server:
BACKUP DATABASE [MyDatabase]
    TO DISK = 'C:\MyDatabase.bak' 
    WITH FORMAT
GO

-- Next take a backup of the log file:
BACKUP LOG [MyDatabase]
    TO DISK = 'C:\MyDatabaseLog.bak' 
GO

-- Copy these files to the local filesystem of your mirror SQL server.
-- You must now restore them, note the NORECOVERY flag. RESTORE THE FILE SEPERATELY. DO NOT TRY TO RESTORE BOTH THE DB AND THE LOG AT THE SAME TIME!
-- Ensure that the full path is valid. E.G. If on the principle the MDF and LDF files were kept on T:\Microsoft SQL Server\MSSQL.1\MSSQL\Data then you must have this folder structure on your mirror server too

RESTORE DATABASE [MyDatabase] 
    FROM DISK = 'C:\MyDatabase.bak' 
    WITH NORECOVERY
GO

RESTORE LOG [MyDatabase]
    FROM DISK = 'C:\MyDatabaseLog.bak' 
    WITH FILE=1, NORECOVERY
GO

-- Until mentioned otherwise, the following SQL statements needs to be rn on BOTH your servers that you want to setup for mirroring.

-- The following two SELECT statements will show you if you have any mirroring endpoints already setup:
SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints 
SELECT name, port FROM sys.tcp_endpoints

-- If you have already got endpoints, but are not using them for mirroring then please delete as per below, i'm assuming that the name of the old endpoint is mirroring but it could be anything - please use the name that is listed from the results of the above query
DROP ENDPOINT mirroring

-- Create new end point on both your SQL servers
CREATE ENDPOINT mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

-- Using the credentials that both your SQL Server instances are running under, create that user in SQL Server: (skip if already created)
USE master;
GO
CREATE LOGIN [DOMAIN\SQLServer] FROM WINDOWS;
GO

-- With the user account that SQL uses to run it's process created in SQL Server itself as a logon, you must grant it permission to use the endpoint. You MUST make sure you do this on BOTH servers!!!:
USE master;
GO
GRANT CONNECT on ENDPOINT::Mirroring TO [DOMAIN\SQLServer];
GO

-- VERY IMPORTANT BIT - READ THE NEXT LINE CAREFULLY!
-- On the server that you want to be the mirror (not the principle), run the following. Do NOT run this on the principle yet
-- Ensure that you change [databasename] to the DB that you want to mirror, and change the server.domain.com to the FQDN of the principle server. You may also need to change the port if you didn't use port 7022

ALTER DATABASE [databasename] SET PARTNER = 'TCP://principle.domain.com:7022'

-- Once the above command is completed, run the same on your principle server. Obviously change the server FQDN to the mirror server

ALTER DATABASE [databasename] SET PARTNER = 'TCP://mirror.domain.com:7022'

-- All done - go have a beer