2005 Mirroring for Centralized Backup?

I am new to 2005 mirroring but have read a lot of documentation on it. I want to use mirroring to centralize a bunch of DBs to one location and then back them up from that central point. However, from what I've read since the mirror is kept in a "recovering state" it cannot be accessed directly. Does that mean I wont be able to backup the mirrors? If so, are there any work arounds or different strategies for this?

Thanks!

Craig

yes the mirror db will be in recovering state so that additional transactional logs cud be applied.........so you can't take backup or read the contents of the mirror db.......only option is you can take a snapshot of the mirror db and read through the contents of it........refer,

http://msdn2.microsoft.com/en-us/library/ms175511.aspx

snapshot is only read-only so you cannot alter the db in any ways

|||

Thanks for the quick reply.

It also looks like from this link, that the snapshot cannot be backed up either. (Look at the CONS section)

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/

Do you know this to be true?

|||yes you are 100% right, we cannot backup the database snapshot........you can use it only for reporting purposes..........|||

Why dont you consider Snapshot or Transactional Replication for this purpose ? I strongly beilive using Transactional / Snapshot Replication could resolve the problem of Centralised backups again depends on the scenario

HTH

Vishal

|||

I may have my terminology wrong but I think thats what we discussed above. Deepak says we cant backup a database snapshot. I believe the transactional replication or "High Protection" mode as 2005 calls it, will leave the database in a "restoring" mode so you cannot access the database in any way, including a backup.

Please do tell if you know anything different or if I am confused!

Thanks,

Craig

|||Craig you have misinterpreted what Visha had said....we were discussing about database mirroring....in that the mirror db will be in recovering state and cannot read its contents nor backup the mirror db....if you want to read the contents you can go for database snapshots but it cannot be backed up as you rightly said......Vishal is talking about Replication in sql 2005 one of which is snapshot replication,transactional replication....etc..

for database mirroring you can refer,
http://deepakinsql.blogspot.com/ > just scroll till the end to read through the contents about mirroring
http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html

for replication refer,
http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx

feel free to clear your doubts......

thanxxxxxxxxx
|||

I had no idea there was a difference between replication and database mirroring!

Looks like replication is a bit more involved. I'm trying to find some decent step by step guides for replication but cant find much. Let me know if you have any good resources for setting it up!

Thanks again for your help.

|||

Found a good article - but again this doesnt sound like what I want. Says its a read only copy. I'm going to give it a shot and I'll let you know.

http://www.informit.com/articles/article.asp?p=169612&seqNum=2

Transactional Replication

Transactional replication involves copying data from the publisher to the subscriber(s) once and then delivering transactions to the subscriber(s) as they occur on the publisher. The initial copy of the data is transported by using the same mechanism as with snapshot replication: SQL Server takes a snapshot of data on the publisher and moves it to the subscriber(s). As database users insert, update, or delete records on the publisher, transactions are forwarded to the subscriber(s).

To make sure that SQL Server synchronizes your transactions as quickly as possible, you can make a simple configuration change: Tell it to deliver transactions continuously. Alternatively, you can run synchronization tasks periodically. Transactional replication is most useful in environments that have a dependable dedicated network line between database servers participating in replication. Typically, database servers subscribing to transactional publications do not modify data; they use data strictly for read-only purposes. However, SQL Server does support transactional replication that allows data changes on subscribers as well.

|||For Documentation on Replication the best source is SQL Server 2005 Books Online