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.
Currently, 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.
- 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.
Here 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