SQL Mirroring and Backup Policy

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL Mirroring and Backup Policy

SQL Mirroring and Backup Policy

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Tuesday, July 29, 2008 1:04 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi-

I'm designing the new database backup policy for databases that are in Mirrroring.

1) i can NOT backup (full backups or differential backups or log backups) the mirror database , only the principal database can be backed up ( with full backups or differential backups or log backups).

2) restoring the principal database is not allowed.

1) question:
Is this true? if sow, i have a question about the second issue:

i can not make a restore on the principal database (any type of restore? log restore or full restore?)

2) question:

I know that, because system databases, can not be mirrored, i do have to create all the logins of one server (principal), in the other server (mirror).
And because of the same thing (system databases) , every jobs (maintenance plans ) that i have on one server, i have to create it on the other server.

Because of job backups , i have a question:

Supose i have 2 jobs (in the principal server), one job to make full backups , and the other job to make log backups.
i Have to create the two jobs on the other server.
After the work done (creation of the jobs on the other server), suppose the following:

On the principal server, the job of FULL Backup is made with sucess (backup is created).
After the job run, the principal server goes down and this server is now the mirror server.
The other server (now the principal) executes job 2 (make a log bakup).

The question is , can i restore the full backup (made on principal 1) and then restore the log backup made on the principal 2 in other computer?
Or will have problems because the full backup was made in one server(srv1) and the other backup was made in other computer(srv2)?
What about diferencial backups with full backup? it the same?

The other question (3rd) is the same case, but with another crash, like this:

Supose i have 2 jobs (in the principal server), one job to make full backups , and the other job to make log backups.
i Have to create the two jobs on the other server.
After the work done (creation of the jobs on the other server), suppose the following:

On the principal server(srv1), the job of FULL Backup is made with sucess (backup is created).
After the job run, the principal server goes down and this server is now the mirror server.
The other server (now the principal) executes job 2 (make a log bakup).
After log backup is made on srv2 with sucess, this server goes down and the srv1 is now the principal again.
srv1 now executes the job 2 (log backup) with sucess.

The question is , can i restore the full backup (made on principal 1) and then restore the log backup made on the principal 2 and then restore the log backup made on srv1 again? in other computer?
Will the LSN not be lost because of this?

Tks Masters.

Post #542384

« Prev Topic | Next Topic »

Permissions