0

I have SQL Server 2014 with Always On. I installed SQL Server 2017 in the same server and I want to move the Database from SQL Server 2014 to SQL Server 2017.

In every replica, I have their own files (.mdf and .ldf). In SQL Server 2014, In the primary I removed the DB from the AG, then I detached the DB. In SQL Server 2017, I attached it successfully. I did the same in the secondary. When I try to add the DB to the SQL Server 2017 replicas I receive the error saying that the secondary is not in Restoring state. I tried generating a new backup and restoring it as NoRecovery in the secondary but when I try again it says that there are inconsistencies.

What am I doing wrong? I am trying to do the "Join Only" option since the DB has more than 300 GB and it will take too much time to synchronize it.

Francisco G
  • 1,434
  • 2
  • 14
  • 22

1 Answers1

0

Check to make sure you don't have a Maintenance job running which has taken a transaction log backup before, while, or during when you are trying to add the database to the AG. This definitely will result in an inconsistency error. As an alternative to the GUI (SSMS), after you attach the database to the 2017 instance, you could use scripts to backup the db and t-log on the Primary, manually copy the 2 backups to the secondary, then, restore the (2) files with no recovery, then run the 2 commands to add it back to the AG. This way may be more helpful to you in figuring out why you are getting inconsistency error. An example of the code is here (be sure to substitute DB names and AG name) -

`While connected to Primary instance -

Step 1) BACKUP DATABASE [MyDatabase] TO DISK = N'\PrimaryServerName\C$\temp\MyDatabase.bak' with init, stats = 10

Step 2) BACKUP LOG [MyDatabase] TO DISK = N'\PrimaryServerName\C$\temp\MyDatabase.trn' with init, stats = 10

Step 3) RDP to Primary server - manually copy backup files (.bak and .trn) from C:\temp on Primary to C:\temp on secondary

Step 6) ALTER AVAILABILITY GROUP [AGGroupName] ADD DATABASE [MyDatabase]

While connected to Secondary instance -

Step 4) RESTORE DATABASE [MyDatabase] FROM DISK = N'C:\temp\MyDatabase.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

Step 5) RESTORE LOG [MyDatabase] FROM DISK = N'C:\temp\MyDatabase.trn' WITH NORECOVERY

Step 7) ALTER DATABASE [MyDatabase] SET HADR AVAILABILITY GROUP = [AGGroupName]`

rvsc48
  • 126
  • 3