July 23, 2012 at 12:12 am
I am writing a program to restore a Database which is part of a AAG group.
The steps which i followed through the SQL management studio is as below
1> Create a bakcup(type=full)
2>Remove the database from the AAG group
3>Do a restore of the database through a SQL management Studio
4>wait for the restore to finish and then add the database back to AAG group at the primary site.
behavior I see through SQL management studio is as below
Primary site: database is recovered, up and running and was able to add the database to the AAG group
Secondary site: I tried joining the database it gave me error "the remote copy of database "<databasename>" is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database. (Microsoft SQL Server, Error:1408).
And the database is in the (Restoring...) state.
Later I tried restoring the database at the secondary site with the secondary database's logs (right click on database -->task-->restore-->TransactionLog) then again joining the database to the AAG gave me the below error
Database <DatabaseName> is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using the "WITH NORECOVERY". (Microsoft SQL Server, Error:1464).
and i restored it "WITH NORECOVERY" but of no use it again me the first error mentioned above.
Can anyone please let me know the right procedure to restore the database which is part of the aag group is SQL 2012, If i am doing anything wrong with the procedure for restoring of database please let me know.
Thanks
Santosh
July 23, 2012 at 5:46 am
santosh.balaraj (7/23/2012)
I am writing a program to restore a Database which is part of a AAG group.
What are you trying to restore, the primary or a replica database?
santosh.balaraj (7/23/2012)
The steps which i followed through the SQL management studio is as below1> Create a bakcup(type=full)
2>Remove the database from the AAG group
3>Do a restore of the database through a SQL management Studio
4>wait for the restore to finish and then add the database back to AAG group at the primary site.
behavior I see through SQL management studio is as below
Primary site: database is recovered, up and running and was able to add the database to the AAG group
Secondary site: I tried joining the database it gave me error "the remote copy of database "<databasename>" is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database. (Microsoft SQL Server, Error:1408).
And the database is in the (Restoring...) state.
Later I tried restoring the database at the secondary site with the secondary database's logs (right click on database -->task-->restore-->TransactionLog) then again joining the database to the AAG gave me the below error
Database <DatabaseName> is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using the "WITH NORECOVERY". (Microsoft SQL Server, Error:1464).
and i restored it "WITH NORECOVERY" but of no use it again me the first error mentioned above.
Can anyone please let me know the right procedure to restore the database which is part of the aag group is SQL 2012, If i am doing anything wrong with the procedure for restoring of database please let me know.
Thanks
Santosh
Can you provide more detail about the scenario you are using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2012 at 10:18 pm
Yes Perry, i am trying to restore a primary database
July 30, 2012 at 11:56 am
Any help on this ..
July 30, 2012 at 12:49 pm
There's not enough information to give a helpful answer which is why nobody has replied. You'll need to supply a lot more info on the state of the AO group, obfuscate where necessary but provide more info.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 9, 2012 at 9:38 am
Hi,
I have the following code written to restore the DB which is part of the AAG primary site, but its failing with an exception pasted below
//sample code for Restore
//Test_AAG is my AAG Name
AvailabilityGroup ag = svr.AvailabilityGroups["Test_AAG"];
//santosh is my dbName
AvailabilityDatabase dbtoSupend = ag.AvailabilityDatabases["santosh"];
//Suspend the datamovement for the database
dbtoSupend.SuspendDataMovement();
while (!dbtoSupend.IsSuspended) //Check for suspend complete.
{
Thread.Sleep(10000);
dbtoSupend.Refresh();
}
//making the database leave the Availability Group
dbtoSupend.LeaveAvailabilityGroup();
while (dbtoSupend.IsJoined)
//Waiting for the Database toleave the group
{
Thread.Sleep(1000);
dbtoSupend.Refresh();
}
DoRestore();
//Calling my restore method
//If restore is success then we come back here and JOin the database to the available group
//dbtoSupend.JoinAvailablityGroup();
//Create is used to put the database back to the AAG as its a primary
dbtoSupend.Create();
while(!dbtoSupend.IsJoined)
//Check if the join has happened.
{
Thread.Sleep(1000);
dbtoSupend.Refresh();
}
Unhandled Exception: Microsoft.SqlServer.Management.Smo.FailedOperationException: The database 'santosh' failed to leave the availability group 'Test_AAG' on the availability replica 'cluster1\san_aag'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Database 'santosh' cannot be joined to or unjoined from availability group 'Test_AAG'. This operation is not supported on the primary replica of the availability group.
few other approaches which I tried
Instead of doing a "dbtoSupend.LeaveAvailabilityGroup();" in the code i did "dbtoSupend.Drop();" this passed that step but later gave error while joining the database "dbtoSupend.JoinAvailablityGroup()" back to AAG.
But the same procedure works fine for me when executed through SQL management studio.
Can anyone let me know, if I am doing anything wrong here.
Thanks
Santosh.B.B.
August 9, 2012 at 11:33 am
santosh.balaraj (8/9/2012)
This operation is not supported on the primary replica of the availability group.
Failover the group before attempting to join or remove the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2012 at 12:13 pm
Hi Perry
I dont want to do the failover as its a costly operation. Now i am successfuly restore the database back to AAG. But am facing difficult to put back the databases back to AAG. The program for the same which i am using is as below
static void Main(string[] args)
{
string logicalpath_primary = "primary_server\\san_aag";
string logicalpath_secondary = "secondary_server\\san_aag";
ServerConnection conn_primary = new ServerConnection(logicalpath_primary);
ServerConnection conn_secondary = new ServerConnection(logicalpath_secondary);
Server svr_primary = new Server(conn_primary);
Server svr_secondary = new Server(conn_secondary);
//Availability Groups on Primary
AvailabilityGroupCollection AvailabilityGroups_primary = svr_primary.AvailabilityGroups;
Console.WriteLine("Number of availability Groups: {0}", AvailabilityGroups_primary.Count);
//Availability Groups on Secondary
AvailabilityGroupCollection AvailabilityGroups_secondary = svr_secondary.AvailabilityGroups;
Console.WriteLine("Number of availability Groups: {0}", AvailabilityGroups_secondary.Count);
//Databases on Primary
DatabaseCollection dbc_primary = svr_primary.Databases;
for (int i = 0; i < dbc_primary.Count; i++)
{
Database db_p1 = dbc_primary;
Console.WriteLine("Database name: {0}", db_p1.Name);
Console.WriteLine("Database AvailabilityGroupName: {0}", db_p1.AvailabilityGroupName);
Console.WriteLine();
}
//Databases on Secondary
DatabaseCollection dbc_secondary = svr_secondary.Databases;
for (int i = 0; i < dbc_secondary.Count; i++)
{
Database db_s1 = dbc_secondary;
Console.WriteLine("Database name: {0}", db_s1.Name);
Console.WriteLine("Database AvailabilityGroupName: {0}", db_s1.AvailabilityGroupName);
Console.WriteLine();
}
//Create the AAGroup object and AAGDb object for primary
AvailabilityGroup ag_p = svr_primary.AvailabilityGroups["Test_AAG"];
AvailabilityDatabase db_p = ag_p.AvailabilityDatabases["santosh"];
AvailabilityReplicaCollection replicas = ag_p.AvailabilityReplicas;
DataTable dtab = ag_p.EnumReplicaClusterNodes();
Console.WriteLine("the number of replicas found is {0}.", replicas.Count);
for (int idx = 0; idx < replicas.Count; idx++)
{
Console.WriteLine("The availabilityGroup Replicas is: {0},", replicas[idx]);
}
foreach (DataRow row in dtab.Rows)
{
Console.WriteLine("----");
foreach (var item in row.ItemArray)
{
Console.Write("Item: "); // Print label.
Console.WriteLine(item); // Invokes ToString abstract method.
}
}
//Create the AAGroup object and AAGDb object for secondary
AvailabilityGroup ag_s = svr_secondary.AvailabilityGroups["Test_AAG"];
AvailabilityDatabase db_s = ag_s.AvailabilityDatabases["santosh"];
//SuspendDataMovement for the availability Group on primary site.
//There is no SuspendDataMovement from secondary as the below call is two-way
db_p.SuspendDataMovement();
while (!db_p.IsSuspended)
{
Thread.Sleep(10000);
db_p.Refresh();
}
//As there is no datamovement secondarydb will LeaveAvailability Group
db_s.LeaveAvailabilityGroup();
//Drop the database from the AAG at primary site.
db_p.Drop();
//Refresh the AAG group both at the primary and secondary site
ag_p.Refresh();
ag_s.Refresh();
//drop the database from the secondary node also.
DatabaseCollection secdbc = svr_secondary.Databases;
for (int j = 0; j <= secdbc.Count; j++)
{
Database db = secdbc[j];
if (db.Name == "santosh")
db.Drop();
break;
}
Console.WriteLine("//After Dropping @ primary ");
svr_primary.Initialize();
DatabaseCollection dbc_p = svr_primary.Databases;
for (int i = 0; i < dbc_p.Count; i++)
{
Database db1 = dbc_p;
Console.WriteLine("PDatabase name: {0}", db1.Name);
Console.WriteLine("Database AvailabilityGroupName: {0}", db1.AvailabilityGroupName);
Console.WriteLine();
}
Console.WriteLine("//After Dropping @ secondary");
DatabaseCollection dbc_s = svr_secondary.Databases;
for (int i = 0; i < dbc_s.Count; i++)
{
Database db1 = dbc_s;
Console.WriteLine("SDatabase name: {0}", db1.Name);
Console.WriteLine("Database AvailabilityGroupName: {0}", db1.AvailabilityGroupName);
Console.WriteLine();
}
//Call Restore method here
//Add the db back to primary site AAG
Console.WriteLine("We are creating @ primary");
AvailabilityGroup ag2 = svr_primary.AvailabilityGroups["Test_AAG"];
AvailabilityDatabase dbTocreate_p = new AvailabilityDatabase(ag2, "santosh");
Console.WriteLine("Calling create ");
dbTocreate_p.Create();
//Add the db back to secondary site AAG
Console.WriteLine("We are Joining @ seondary ");
db_s.JoinAvailablityGroup();
}
After doing a create at the primary site, when the join is called on the secondary site am getting the error as below
SMO:Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException: You cannot execute this operation since the object has not been created.
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckObjectStateImpl(Boolean throwIfNotCreated)
at Microsoft.SqlServer.Management.Smo.AvailabilityDatabase.JoinAvailablityGroup()
at vcdbaPSlib_S.SmoUtils.FinishAAGDBofRestoreforSecondary(String log, String secondaryserver, String instance, String database, String aagName)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply