How to move Distribution database to a different location (MSSQL 2005/2008)

-->

1. Preparation

a. Stop Log Reader Agent

b. Stop Distribution Agent

c. Backup Distribution database

2. Check Distribution database Status

SELECT name, physical_name FROM sys.master_files where database_id = db_id('distribution')

3. Modify Distribution database files location

Alter database distribution modify file (name = distribution, filename = ‘New Location\distribution.MDF') Alter database distribution modify file (name = distribution_log, filename = 'New Location \distribution.LDF')

4. Stop SQL SERVER service (NOTE: SQL AGENT SERVICE WILL STOP TOO)

5. Move old Distribution database files to NEW Location

a. MDF file

b. LDF file

6. Start SQL SERVER service

7. Start SQL AGENT service

8. Check Distribution database Status

SELECT name, physical_name FROM sys.master_files where database_id = db_id('distribution')

9. Start Distribution Agent service

10. Start Log Reader Agent service

Note:Make sure sql service agent has permission to access the new location