Backup of database on SQL 2005.

Restore Query to restore database on SQL 2012 and the database is already part of database high availability group

Restore Query I am running

DATABASE[Test]

DISK=N'E:\Migration SQL Backups\Test.bak'WITHFILE=1, 

N'Test'     TON'E:\SQL Data\Test.mdf', 

N'Test_log'TON'D:\SQL Logs\Test_log.ldf', 

,NOUNLOAD,STATS=10

I get an error message

Msg 3104, Level 16, State 1, Line 1

RESTORE cannot operate on database '******' 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 1

RESTORE DATABASE is terminating abnormally.

The error is fairly clear.

If you want to restore the database you first have to remove it from your availability group, i.e.

ALTER AVAILABILITY GROUP Your_AvailabilityGroup_Name REMOVE DATABASE Your_Database_Name;

 do you have same database configured for AlwaysOn or mirrioring on SQL 2012 server.

if so, do you want to overwrite that database??

if yes, you need to remove the database from the Availability group--do restore with replace and then add the database to AlwaysOn group again(you will need to backup and restore on the secondary server first and then join it,bascially like doing it from scratch) .....

The database is not configured on mirroring

It is part of always on high availability group

I also want to replace the existin

I have scripted this via SMS. It is not tested not sure if this is what you mean.

Also After I remove it from HA is I easy to add or I have do everything from scratch like delete HA group and add databases one by one

USE [master]

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [Test]

FROM  DISK = N'E:\Migration SQL Backups\Test_backup_201505102300.bak' WITH  FILE = 1,

MOVE N'Test' TO N'E:\SQL Data\Test.mdf',

MOVE N'Test_log' TO N'D:\SQL Logs\Test_log.ldf', NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

GO

That looks fine for the restore script.

You can also script out removing the database form your Availability Group.

After you restore the database you'll need to add it back into the AG then join the secondaries - I'd recommend using the wizard in SQL Server Management Studio for this.

• Edited by jmcmullen 8 hours 22 minutes ago clarification re add/join

That looks fine for the restore script.

You can also script out removing the database form your Availability Group.

After you restore the database you'll need to join it back into the AG - I'd recommend using the wizard in SQL Server Management Studio for this.

I do not think you can join it back - you need to configure it from scratch - like you were configuring new database to the replica....

mcmullen - you think he can simply add without any configuration??

Is there any step to follow on secondary replicas (As I have two more servers working as secondary replica for this database) before I remove database from primary replica

mcmullen - you think he can simply add without any configura

Is there any step to follow on secondary replicas (As I have two more servers working as secondary replica for this database) before I remove database from primary r

The size of database too big 60GB and the secondary replica is located on WAN site

I have done this in the past and it takes days to replicate database between primary and secondary servers.

Is there way to make it quicker please?

The size of database too big 60GB and the secondary replica is located on WAN site

I have done this in the past and it takes days to replicate database between primary and secondary servers.

Is there way to make it quicker p

this is summary/steps of what I have to perform during migration please correct me if I am wrong

this is summary/steps of what I have to perform during migration please correct me if I am wrong

Free Windows Admin Tool Kit Click here and download it now

May 17th, 2015 8:23pm

---

 By restoring a backup, you are essentially replacing the database schema and data. You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY. Once your backup is in place, put the DB back into HA and start synchronization again.

HA is very similar to mirroring and uses similar technology, just not nearly as finicky. You will want to treat your HA DBs similarly as well.

Code would be similar to the following:

--on primary

ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;

--on primary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY, 
      MOVE 'AdventureWorks2012_Data' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 
      MOVE 'AdventureWorks2012_Log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;

--on secondary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY, 
      MOVE 'AdventureWorks2012_Data' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 
      MOVE 'AdventureWorks2012_Log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;
refer http://dba.stackexchange.com/questions/82548/restoring-a-sql-server-2012-database-in-high-availability

May 17th, 2015 9:10pm

---

You will need to turn synchronization off,

If I change the status of "Readable secondary" from "yes" to "No" does it going to stop the synchronisation

remove the DB from HA and perform the restore on the primary and replica,

After I remove DB from HA

Do  I need to drop databases from each server?

leaving the replica version in a restoring state by using WITH NORECOVERY.

If I leave the secondary with no recovery when I add database into HA group does it bring the database online by changing the status of synchronisation mode to "Yes"

Once your backup is in place, put the DB back into HA and start synchronization again.

Free Windows Admin Tool Kit Click here and download it now

May 17th, 2015 9:31pm

---

Your database is 60 GB, can take database very small just like 1 0r two table so it will give result very fast.

May 17th, 2015 9:38pm

---

How can I turn off synchronisations?

I have performed the steps but it failed for some reason..

Free Windows Admin Tool Kit Click here and download it now

May 18th, 2015 12:39am

---

I have followed steps.....

Database with recovery into primary server

Database with no recovery into secondary server

Database with no recovery into second secondary server (WAN)

When I run wizard add database into HA group from primary server. the database is greyed out with message "Full Recovery mode is required"

Any comments please?

May 18th, 2015 1:11am

---

When I run wizard add database into HA group from primary server. the database is greyed out with message "Full Recovery mode is required"

Any comments please?

Free Windows Admin Tool Kit Click here and download it now

May 18th, 2015 3:24am

---

This topic is archived. No further replies will be accepted.

Other recent topics

Remote Administration For Windows. Easy remote access of Windows 7, XP, 2008, 2000, and Vista Computers

Click here to find out more

Reboot Hundreds of computers, disable flash drives, deploy power managements settings.

Click here to get your free copy of Network Administrator. Over 25 plugins to make your life easier

Run full backup   of database on existing environment

Transfer the   backup of database and transaction logs to primary server availability group

Transfer the   backup of database and transaction logs to WAN site secondary availability group   (This task has to be via USB Hard drive)

Remove existing   database from high availability group

Remove/Detach   database from secondary server same location

Remove/Detach   database from secondary server WAN location

Restore database   with no recovery on primary server

Restore   transaction logs with recovery on primary server

Restore database   on secondary server with no recovery same location

Restore database   on secondary server with no recovery WAN location

Run wizard SMS   to add database part of high availability group on primary server

This is should   bring the databases online on secondary servers same site and WAN connection