SQL Server data disk upgrade/downgrade

I had to move (copy) SQL server data files to a smaller (and different) disk with same drive letter. This sounded like a trivial operation, but managed to find out the hard way, it is not the case.

Googling this matter also displayed more advanced method with alter tables and taking DB’s offline.

For future reference:

  • Make backups of all databases
  • Stop all SQL Server service(s)
  • Copy SQL Server data folders (and containing files) to a new disk with exact folder structure
  • Change OLD data drive’s letter to a new one (just pick one available)
  • Change NEW data drive to a letter you had in use before change
  • Critical: Check and add ‘Full control’-permission to
    ‘NT SERVER\MSSQLSERVER’ by manually typing the group name for data folders (and containing files). Also check the owner to be same as in original folder and files.
  • Start SQL Server service(s)
  • Verify you can connect and see data from SQL Server with management studio

If you miss the critical part, the SQL Server service (if running with default user) has no access to DB- and log files. Also, if you did not add access to all folder and files, the DB will probably start with ‘Suspect’ or ‘Recovery pending’-state -> stop DB, re-copy files and folders and verify the permissions.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.