0

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.

  1. I created 3 separate virtual disks on each of my virtual machines (for backup, data, log files).
  2. Then I created a database and started practicing transactional replication which btw was successfully implemented.
  3. 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?

  1. 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).
  2. Then, to solve 2nd problem, I found a script which I executed on both servers to repair my database.

Script

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.

  • None of that is normal. Your disks shouldn't disappear, and when they come back you should be able to just start SQL Server and have your databases come online. – David Browne - Microsoft Jul 14 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.