I am testing a DR scenario but I am running into an issue and wondering if I can get some help. We use DPM tool to backup DBs. DPM only runs on secondary replicas so there is no impact on primary server. DPM takes the snapshot of a data and log file so the only way to restore a database on a target server in case of a disaster is using the attach method. However, when I try to attach a database using the mdf and ldf file, I get an error "Database cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery." The source and target servers are on 2016 enterprise edition. So what I understand is that the only way to attach a database is to take the dpm backup on a primary server. Only then, I should be able to restore a database. But is it a good practice? We are 24/7 shop and we have an average of 40+ DBs on each server with about 3TB+ of used space.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Try the next thing.
Create a database with the same exact name and exact files in the correct orders.
i.e: DB NAME = TEST
FILES = File1.mdf, file2.mdf, file3.mdf and log.ldf.
take the database offline and swap your data files with the ones that you can't attach, the database should start with the files you changed to these locations.
your dummy database file names should be the exact same one as your production database file names and in the same order.
this will only work if you have the files and not a backup.
Regards,
January 3, 2020 at 1:41 pm
Replacing a file worked. Thanks!
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 3, 2020 at 1:56 pm
You are welcome! 🙂
January 3, 2020 at 2:22 pm
Replacing the file worked, however; I am not able to join the DBs into AG.
I took the full backup and then the log backup of the database on a primary server. Restore the database on a secondary server with norecovery then tried adding a DB into AG using the alter statement on the secondary server but it failed.
USE master
GO
ALTER DATABASE [********] SET HADR AVAILABILITY GROUP = [*********];
'Cannot join database 'DatabaseName' to availability group 'AGName'. The specified database does not belong to the availability group. Verify the names of the database and the availability group, and retry the command specifying the correct names.'
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 3, 2020 at 2:42 pm
is it a VLDB? if it isn't you could try seeding, i'll try to re-create your environment and see if i get the same error.
The database restored in the secondary replica should be in the restoring state, so your last log should have be restored with the no recovery option.
January 3, 2020 at 3:09 pm
I tried this method and it worked. I turned off the encryption, dropped the encryption on the database, took the backup, restore the database on the secondary with norecovery, join the database into AG, created the encryption key, set the encryption on and it worked.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 3, 2020 at 3:20 pm
What kind of encryption were you using?
backup encryption by certificate?
January 3, 2020 at 3:31 pm
TDE.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply