Backing Up a Mirrored Database

Question

I have on test servers set up Mirroring. We want to process our data and see how the Mirroring is working with our processing.

Now I am thinking about how the database should be backup, etc. Now we just do a full backup once a day. However, someone remarked that I should set up Transaction Log backups. Is this true?

I am wondering what my backup strategy should be now that the database is going to be Mirrored?

　

And set it up on the Test Servers to test how it would work.

Could someone give me some advise?

---

lcerni

Answer 1

Hi Icerni,

You should have the database recovery model = FULL . Then you can do a daily full backup and transaction log backup every x amounts of minutes (in my case 10 - 15 minutes)

To setup database mirroring, you have to restore the latest full backup and all the transaction log backups with NORECOVERY option. This is the requisite to establish a mirror session.

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

What I am asking is after the mirror session is established.  Do you need to do something different with your backup strategy on the principal?  Does the transaction log grow when the principal database is mirrored?  Or because it is mirrored the transaction log is kept small because the transactions are written to the mirror database?

---

lcerni

Answer 3

the transaction log will keep growing because the database is in FULL recovery model, does not matter whether mirroring is enabled or not. to keep it controlled you have to perform transaction log backups periodically on the principal.

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

Also one more important note related to transaction log file on pricipal is that if send queuue history is growing i.e unsent log to mirror exists ..then your transactionlog file will get increased contnuosly on principal untill the issue resolved.

---

ManyThanks, NARI