GAPTHEGURU

Geek with special skills

tasks before setting up database mirroring in sql server 2008

Before you can start a database mirroring session, you must
create the mirror database. You should do this just before starting the
mirroring session.

    1. To use database mirroring with the AdventureWorks2008R2 database, modify it to use the full recovery model:
      USE master;
      GO
      ALTER DATABASE AdventureWorks2008R2
      SET RECOVERY FULL;
      GO
    1. After modifying the recovery model of the database  from SIMPLE to
      FULL, create a full backup, which can be used to create  the mirror
      database. Because the recovery model has just been changed,  the WITH
      FORMAT option is specified to create a new media set. This is  useful to
      separate the backups under the full recovery model from any  previous
      backups made under the simple recovery model. For the purpose  of this
      example, the backup file (C:\AdventureWorks2008R2.bak) is created on the
      same drive as the database.

      NoteNote
      For a production database, you should always back up to a separate device.

      On the principal server instance (on PARTNERHOST1), create a full backup of the principal database as follows:

      BACKUP DATABASE AdventureWorks2008R2
          TO DISK = 'C:\AdventureWorks2008R2.bak'
          WITH FORMAT
      GO
    1. Copy the full backup to the mirror server.
    1. Restore  the full backup WITH NORECOVERY onto the mirror server
      instance. The  restore command depends on whether the paths of principal
      and mirror  databases are identical.

        • If the paths are identical:
          On the mirror server instance (on PARTNERHOST5), restore the full backup as follows:

          RESTORE DATABASE AdventureWorks2008R2
              FROM DISK = 'C:\AdventureWorks2008R2.bak'
              WITH NORECOVERY
          GO
        • If the paths differ:
          If  the path of the mirror database differs from the path of the
          principal  database (for instance, their drive letters differ), creating
          the mirror  database requires that the restore operation include a MOVE
          clause.

          Important noteImportant
          If  the path names of the principal and mirror databases differ, you
          cannot  add a file. This is because on receiving the log for the add
          file  operation, the mirror server instance attempts to place the new
          file in  the location used by the principal database.

          For  example, the following command restores a backup of a principal
          database residing in C:\Program Files\Microsoft SQL
          Server\MSSQL.n\MSSQL\Data\ to a different location, D:\Program
          Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\, where the mirror
          database is to reside.

          RESTORE DATABASE AdventureWorks2008R2
             FROM DISK='C:\AdventureWorks2008R2.bak'
             WITH NORECOVERY,
                MOVE 'AdventureWorks2008R2_Data' TO
                   'D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\AdventureWorks2008R2_Data.mdf',
                MOVE 'AdventureWorks2008R2_Log' TO
                   'D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\AdventureWorks2008R2_Log.ldf';
          GO
    1. After you create the full backup, you must  create a log backup on
      the principal database. For example, the  following Transact-SQL
      statement backs up the log to the same file used  by the preceding full
      backup:

      BACKUP LOG AdventureWorks2008R2
          TO DISK = 'C:\AdventureWorks2008R2.bak'
      GO
    1. Before you can start mirroring, you must apply the required log backup (and any subsequent log backups).
      For example, the following Transact-SQL statement restores the first log from C:\AdventureWorks2008R2.bak:

      RESTORE LOG AdventureWorks2008R2
          FROM DISK = 'C:\AdventureWorks2008R2.bak'
          WITH FILE=1, NORECOVERY
      GO
    1. If any additional log backups occur before you start  mirroring, you
      must also restore all of those log backups, in sequence,  to the mirror
      server using WITH NORECOVERY.
      For example, the following Transact-SQL statement restores two additional logs from C:\AdventureWorks2008R2.bak:

      RESTORE LOG AdventureWorks2008R2
          FROM DISK = 'C:\AdventureWorks2008R2.bak'
          WITH FILE=2, NORECOVERY
      GO
      RESTORE LOG AdventureWorks2008R2
          FROM DISK = 'C:\AdventureWorks2008R2.bak'
          WITH FILE=3, NORECOVERY
      GO

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: