Moving an SQlServer database from one machine to a new one with SQLServer 2016

I’ve just come to move to a new windows 10 development laptop and need to install a number of SQLServer databases that were on the old one (also windows 10 with various flavours of SQLServer). I have access to all of the .mdf and ldf files in a backup image.

I have restored these files and put them onto a data disk. However, when I came to attach them using SQL Management Studio (right click on databases and choose “Attach”), I was getting access denied errors. It turns out that all you need to do is give “Full Control” to the files “OWNER RIGHTS”.

Do this by selecting each file in turn, right click and choose properties. In the security tab, click “Edit”. Then click “Add”. Type “OWNER RIGHTS” in the box, click “Check Names” and click Ok. Make sure that “OWNER RIGHTS” is now selected in the top list and then check if “Full Control” is checked in the lower list. If it is not, click on the “Full Control” check box to select it.

Finally click OK on the two dialog boxes to dismiss them. You should now be able to attach the database

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *