Sql move distribution file

The database cannot be moved. If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates. Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist. OUT file. Under Database Default Locations , browse to the new location for both the data and log files. The following example moves the tempdb data and log files to a new location as part of a planned relocation.

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3.

Until the service is restarted, tempdb continues to use the data and log files in existing location. Determine the logical file names of the tempdb database and their current location on the disk. Delete the tempdb. Skip to main content.

This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No.

Any additional feedback? Important If you move a system database and later rebuild the master database, you must move the system database again because the rebuild operation installs all system databases to their default location. All went fine, except that the distribution database was created on the wrong drive Roy Ernest. I already tried that. Trying to set the distribution database offline will hang forever, because it is being blocked by some of the replication processes not sure if from the subscriber side or the publisher side and was not able to pinpoint the process in order to stop it.

The ideas is to be able to do this moving the distribution DB without having to redo the whole replication configuration After I move the distribution DB, how would I get it going again? Just Start them, Reinitialize, or what must I do? You can start them back. It is just like rebooting your servers. Since you publisher is in Full mode It has to be in full mode since it is replicating all the commands will still be there in the transaction log.

There for when you restart the services the log reader will continue from where it left off. Till now I have never had trouble to stop log reader agents and restart them. Thank you for the step by step. In addition to this, our transactional replication has been setup in a way, that DELETE commands are not being propagated to the subscribers which means that we have a lot more data there than on the publishers:.

Due to this scenario, we are not able just to reinitialize the subscriptions if there are major problems with them or they just fall badly behind. The cure to our situation was probably the very obvious choice to build a new SQL Server, include it in the replication topology and dedicate it to be acting solely as a distributor.

The new high-availability schema would be: two publisher instances, three subscriber instances and one distributor instance. Luckily, we managed to convince all the important people involved, that this is the right solution for us and it has been approved. Next step, the actual work. Initially this seemed like a piece of cake, but as we were going deeper and deeper into particular details, part of the implementation plan, this was not the case. We have read and tried literally tons of articles that we managed to find about moving the distribution database, but they were not working for us due to some reasons — either the posts were not described thoroughly enough and important details are missing or they were intended for more simple scenarios.

After countless numbers of tests, here are the particular steps, we have taken and worked for us:. If it is a pull subscription you have to check the properties for the subscriptions from the publisher to see that it is a pull subscription and from the subscriber to see the accounts:. Drop all subscriptions with the scripts generated from Step 4 — you have to use only the parts to be run on the Subscribers.