How to Move a Database That is Configured for Snapshot Replication

This FAQ relates to SQL Server 2000 and Snapshot replication only. These tips may not work if the database uses the Transactional or Merge replication type.

You can move a user database to another logical drive on the same server by following these steps using Enterprise Manager. For example, you might want to move the db to a drive that has more available disk space.

This is what I tried and, to my surprise, it worked beautifully:

1. Perform a full backup of the db
2. Take the db off-line
3. Restore the backup - pointing the files to the new drive path, with 'Leave Database Operational' and 'No additional restores needed' checked

When the Restore completes:
- the database will automatically be on-line and pointing to the data and log files on the new drive
- and the data and log files will have been removed from the drive where the db was originally
- The Snapshot and Distribution jobs should work fine at this point.

When I used these steps, didn't even have to re-create the Subscription.