Migration from SQL 2008 to 2017

SQL Server Administration
1

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)

ADDITIONAL INFORMATION:

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‌ ‌

2

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
3

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?

Thanks

4

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.

5

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.

6

The error 2571 is caused due to SQL Login. You have to give permission
Go through the following thread:
https://blog.sqlauthority.com/2018/03/14/sql-server-database-attach-failure-msg-2571-user-guest-not-permission-run-dbcc-checkprimaryfile/

https://www.systoolsgroup.com/sql-server/migration/

1 Like
7

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