We are in the middle of a migration and we need your help urgently! Replication is not working and the distribution database was deleted! The maintenance window is close to finish.
Hi, SQL Server Lovers!
Today I’ll talk about one critical situation that happened to me this week. Last Friday, the distribution database was accidentally deleted in one of our apps…. A team from the app was migrating from 2008 R2 to 2016 and they had this big issue:
Publisher and distributor on the same server with SQL Server 2016 SP1 Version
Subscriber with SQL Server 2016 SP1 Version
A DBA tried to configure replication, but since there were some issues, he tried to remove the replication and start from scratch. Unfortunately, the disable part was not working, so he decided to execute this command:
PLEASE, IF YOU HAVE THIS SITUATION DO NOT DELETE THE DISTRIBUTION DATABASE!
FIX THE ISSUE THAT YOU ARE GETTING TO DISABLE THE REPLICATION!
ALTER DATABASE DISTRIBUTION SET OFFLINE;
DROP DATABASE DISTRIBUTION;
Which means that distribution DB was deleted… Obviously he tried to disable replication and he couldn’t:
Msg 911, Level 16, State 4, Procedure sp_MSrepl_helpsubscriberinfo, Line 120 [Batch Start Line 0]
Database ‘distribution’ does not exist. Make sure that the name is entered correctly.
So, here you have 2 ways:
- Easy Way
After you delete the distribution database, the Database files remain on the same place (unless you delete them), so you should execute this script in order to create again the database:
CREATE DATABASE Distribution ON PRIMARY
( NAME = N’distribution’,
FILENAME = N’Last file path\Name.mdf’ ,
SIZE = 8192KB , FILEGROWTH = 65536KB )
( NAME = N’distribution_log’,
FILENAME = N’Last file path\Name.ldf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
Then you can disable again the replication:
- Hard Way
After you deleted the distribution database, you also removed the files from the hard drivers and the recycle bin (Yes, believe it or not, this was my situation!). So, to fix this, you can follow this steps:
- Create a new distribution database:
EXEC SP_ADDDISTRIBUTIONDB @database = N’Distribution_New’,
@data_folder = ‘C:\Temp’, –Choose your own folder name
@data_file = N’Distribution_New.MDF’, –Choose your own file name
@data_file_size = 128,
@log_folder = ‘C:\Temp’, –Choose your own folder name
@log_file = N’Distribution_New.LDF’, –Choose your own file name
@log_file_size = 64,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
2) Stop SQL Server Services
3) Copy/Paste the distribution_new files and rename them as the old distribution files database names
— Copy/Paste Distribution DB files
–Rename Both distribution DB files
4) Start SQL Server Service
5) Execute this script to attach the database again
CREATE DATABASE Distribution
( NAME = N’distribution’, FILENAME = N’C:\temp\Distribution.mdf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
( NAME = N’distribution_log’, FILENAME = N’C:\temp\Distribution.ldf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
6) Disable replication and distribution:
— Remove the local server as a Distributor.
EXEC sp_dropdistributor @no_checks = 1;
Have you ever faced this same situation?? Can you add any insights?
Happy new year, sql fans!