Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using always on in sql 2012 and have an AG setup. I would like to know if i were to backup the primary database and restore it would that be allowed without taking the cluster down etc.

Secondly if i have a SSDT project that is deploying my database and i had an AG setup before hand and were to deploy that blank database by way of the project to the primary would it "sync" the changes therefore removing all the data if any was present in the secondary? This is an unlikely scenario just wondering what would happen.

Thanks!

share|improve this question
    
Highly recommended to watch: brentozar.com/archive/2012/07/… –  Jon Seigel Jul 17 '12 at 16:42
    
after backing it up, where are you trying to restore it? To another member of the availability group? –  Aaron Nelson - SQLvariant Jul 18 '12 at 14:09
    
That is correct, i would be trying to restore it back to the primary member of the AG –  deadLock Aug 2 '12 at 20:11

1 Answer 1

This is actually two separate questions.

1) You cannot restore a database that is part of a database mirroring pair or availability group. If you try to do so, you will receive an error like this:

Msg 3104, Level 16, State 1, Line 2 RESTORE cannot operate on database 'AdventureWorks2012' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.

Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

2) Any changes made to the primary database in an Availability Group WILL be sync'd to the other replicas. This includes DML and DDL changes. However, when doing an SSDT deploy, there are mechanisms to check for possible data loss in the target and prevent the deploy (those settings can be disabled). Essentially, deploying with SSDT to the primary would be like deploying to all of the replicas as well.

Also, certain operations are not allowed on databases that are members of a database mirroring pair or Availability Group (such as database restores, enabling service broker, etc.), so if your SSDT deploy tries to do one of those things, it will cause an error like this:

Msg 1468, Level 16, State 1, Line 1 The operation cannot be performed on database "AdventureWorks2012" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

share|improve this answer
    
enabling Service broker is allowed in a mirrored database. –  Remus Rusanu Aug 22 '12 at 19:09
    
Sorry about that, but I do know it's not allowed in a database that is joined to an Availability Group, because that is how I received the error message in the answer I gave above. –  Adam Belebczuk Aug 23 '12 at 20:06
    
Also the AG is allowed, see Service Broker with AlwaysOn Availability Groups –  Remus Rusanu Sep 11 '12 at 6:48
    
I know you can use Service Broker with AlwaysOn, but you must alter database set new broker before you join the database to an AG, or else you receive the error in my answer. –  Adam Belebczuk Sep 12 '12 at 12:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.