Migration from SQL 2008 to 2017

Hi All,

I‌ need some assistance in the process of migrating from 2008 to 2017. This is the first time doing this, so needless to say, I am very ignorant when it comes to this process. Unfortunately, there is no one for me to lean on for assistance in this process, so I'm going at it alone. I will try to layout what is going on and where I am in the process.

C‌urrently, there are two server environments.‌‌ Server A has SQL 2008 and that is currently what is in production. Server B has been created and I have successfully downloaded:

Microsoft SQL Server Management Studio (14.0.17224.0) I think this is SQL 2017? Please correct me if I am wrong
Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT) Version 15.5.7‌‌ I use SSIS for the ETL tool.

I‌ started this morning by trying to take a Database from Server A and moving it over to Server B. Here are the steps that I took, and here is where I ran into my current issue.

  1. I‌ took the database from server A, took it offline, and located both the mdf and log files. I copied them to a different directory location, zipped them, then copied the zip file and placed it in a directory on Server B.
    2‌. I opened the zip file on Server B, took the files out and placed them in the directory.
    3‌. I then went to the Object Explorer, right clicked on Databases, and selected attach...
    4‌. In the window, I selected add, located the mdf file in the directory on Server B, and selected ok.

H‌ere is my problem, I received the following error message:‌
TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

User 'MY USERNAME' does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)‌

I‌ am assuming this has to do with my username doesn't have the security rights. I tried adding the server role "sysadmin" to my username, but that also errors out.

I‌ apologize ahead of time for probably some blatant errors that I am making, but I would greatly appreciate someones time and help to get me squared away. Please let me know if you can assist. Thanks‌ ‌

Instead of using detach/attach - it would be better to backup the database on Server A - copy the backup file to Server B and restore from that backup file.

When it is time for the actual migration - you would disconnect all users, backup the database, copy and then restore. Once restored you then update the clients to use the new server.

In order to perform these operations - you need to have the right permissions. You do not have to be a sysadmin to restore databases - but it sounds like you are the owner of this process and probably should be granted that level of access. To get that - you need to find out who built the system and which accounts were originally granted sysadmin - and then have one of those individuals grant you the necessary permissions.

1 Like

Thank you Jeff for this information. Out of curiosity, why is it better practice to backup the file and then restore it on the new server rather than detach/attach?


Because you do not have to take the current database offline - and if something happens during the copy process that corrupts the mdf/ldf file you have to recopy the files. With a backup - if something happens during the copy of the backup file you just resend the file and your current system isn't affected.

Be sure to run MS's compatibility tool on at least a few of the old dbs (or all of them if you have the time, of course). A very few things could prevent the db from being able to go to 2017 and, if so, the compatibility tool will tell you what those things are.

The error 2571 is caused due to SQL Login. You have to give permission
Go through the following thread:


1 Like

Yes, thank you Jason. This was the exact issue I was having. Got this taken care of and it works for me now.

Thank you