September 12, 2020 at 6:27 pm
I try to join a secondary database to an Always On availability group, and get error "Database "my_database" database is in single user mode which is incompatible with participating in database mirroring or in an availability group. Set database to multi-user mode, and retry the operation." I can't find solution to this from web.
I'm able to join other databases to this AG using exact SQL commands. Other AGs also work without issues. This database is only one having issues. I have created AG with wizard, and chosen "Skip initial data synchronization". Tried also wizard with "Automatic Seeding" and "Full database and log backup" synchronization options, but all end to same error. Also tried creating this database using schema scripts, and was able to join empty secondary DB to AG without issues.
Originally this database comes from SQL Server 2008, and recovery model was simple. Size 450GB. Second database in this AG comes from same server, and has no issues with secondary nodes. Any help is highly appreciated. Below all the steps.
Primary:
-- Restore production backup to Primary
USE [master]
RESTORE DATABASE [my_database] FROM DISK = N'\\my_server\backup\my_database_prod.bak' WITH FILE = 1,
MOVE N'my_database_Data' TO N'F:\MSSQL15.MSSQLSERVER\MSSQL\Data\my_database_data.MDF',
MOVE N'my_database_data2' TO N'F:\MSSQL15.MSSQLSERVER\MSSQL\Data\my_database_data2.ndf',
MOVE N'my_database_Index_Data' TO N'F:\MSSQL15.MSSQLSERVER\MSSQL\Data\my_database_index1.ndf',
MOVE N'my_database_Index_Data2' TO N'F:\MSSQL15.MSSQLSERVER\MSSQL\Data\my_database_index2.ndf',
MOVE N'my_database_Log' TO N'G:\MSSQL15.MSSQLSERVER\MSSQL\Data\my_database_Log.LDF',
MOVE N'my_database_Logdata2' TO N'G:\MSSQL15.MSSQLSERVER\MSSQL\Data\my_database_Logdata2.ldf',
NOUNLOAD, STATS = 5
-- Change options
USE [master] ;
ALTER DATABASE [my_database] SET RECOVERY FULL ;
ALTER DATABASE [my_database] SET COMPATIBILITY_LEVEL = 150
-- Create initial backup from Primary
BACKUP DATABASE [my_database] TO DISK = N'\\my_server\backup\my_database_first_full.bak'
WITH NOFORMAT, NOINIT, NAME = N'my_database-Full Database Backup',
SKIP, COMPRESSION, STATS = 10
- Backup LOG
BACKUP LOG my_database
TO DISK = '\\my_server\backup\my_database_trn_00001.trn'
WITH STATS
Secondary:
-- Restore backup from primary server
USE [master]
RESTORE DATABASE [my_database]
FROM DISK = N'\\my_server\backup\my_database_first_full.bak' WITH FILE = 1, norecovery,
STATS = 5
-- Restore log from primary server
restore log [my_database] from disk='\\my_server\backup\my_database_trn_00001.trn' with norecovery
-- Join database to AG
alter database [my_database] set HADR availability group= AG_PRD1
ERROR:
Msg 1488, Level 16, State 2, Line 27 Database "my_database" database is in single user mode which is incompatible with participating in database mirroring or in an availability group. Set database to multi-user mode, and retry the operation.
September 13, 2020 at 4:15 pm
The error tells you what the problem is - the database in production is set to single user. After restoring the database in production - change it to multi-user: ALTER DATABASE {database} SET MULTI_USER;
My guess is that the database was set to single user prior to taking the backup - which means the state of the database after restore will be single user.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 13, 2020 at 8:58 pm
Thank you for reply. Database was in multi-user mode when backup was taken. I tried this several times.
As far as I know it is not possible to change mode when state is Restoring. Only not-recovered database can be joined to AG. Or?
Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
September 14, 2020 at 5:22 pm
What is the state of the primary database - not the secondary database?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 15, 2020 at 9:38 pm
Hi JLeino,
Why not just let the HADR process auto backup and restore the secondary database for you?
Also check your "Model" database in the SECONDARY replica and ensure it's not in single user mode. Database restores often look at the model database configuration.
*** Please network with me and feel free to IM if you have any more DBA questions and concerns
https://www.linkedin.com/in/james-rossi-9094651b7/
james rossi
Senior SQL Server DBA
Senior Software Developer
Alpha One Data
Brain Box SQL Mobile DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply