I would like to share with you a problem I faced today and will be glad if you can help me to tackle that issue. The issue related to transactional replication. So, let's go step-by-step to know how the problem is occurred.
- I created 3 separate virtual disks on each of my virtual machines (for backup, data, log files).
- Then I created a database and started practicing transactional replication which btw was successfully implemented.
- HOWEVER after rebooting my virtual machines those created virtual drives disappeared or detached (which is normal) and as a result my database turned to RESTORE PENDING state.
HERE we have 3 problems
- Virtual drives detached
- Database is in RESTORE PENDING state
- Replication doesn't work.
What did I do?
- First of all, I tried to bring back (or make appear again) those virtual drives on both machines. To solve 1st issue, I headed to drive C and opened already created virtual files (just double-click).
- Then, to solve 2nd problem, I found a script which I executed on both servers to repair my database.
ALTER DATABASE [DBName] SET EMERGENCY; GO ALTER DATABASE [DBName] set single_user GO DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; GO ALTER DATABASE [DBName] set multi_user GO
So by far, I can access my database in both servers. Finally, we came to the 3rd problem, how to make replication continue to work after repairing? This is the question which I am not able to answer)))) need your help. What I found on the internet is that RESTORING database breaks the replication and we need to recreate it. I am not sure about this, the only thing I did was disabling and re-enabling replication using sproc sp_replicationdboption which was unsuccessful.