Moving the System Databases (Video)

Jon Seigel

Occasionally after installation, the system databases need to be moved from one location to another. In this video demo, I show you how to accomplish this task.

Key points:

  • Make sure the database engine service account has read/write access to the new file location(s).
  • The paths to the master database files are in the service startup parameters.
  • The paths to the other databases files are changed using ALTER DATABASE … MODIFY FILE.
  • The database files themselves can’t be copied until the database engine service is stopped.

Two things about security which I didn’t mention in the video:

  • When granting permissions on the new data container, the principle for the database engine service account will vary depending on your environment. In my case, it was a local group, but you may need to use a domain group, or a service SID. It should be obvious from the source data container.
  • You may want to grant the Everyone group permission to list the folder contents starting from the root, and remove the inherited permission on each data container. This will allow you to browse to the drive when attaching a database (for example) in Management Studio, yet limit access to only the account that should see the files in each instance folder.