March 26, 2018 at 2:54 pm
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.
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.
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.
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)
------------------------------
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
March 26, 2018 at 3:01 pm
skaggs.andrew - Monday, March 26, 2018 2:54 PMHi 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.
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.
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.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)
------------------------------
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
Run SQL Management studio as an Administrator
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 26, 2018 at 3:11 pm
Hi Michael,
I just closed it and ran it as an administrator. I still get the same error message at the same step.
March 26, 2018 at 10:21 pm
Do you mean your attempt to make yourself a sysadmin failed, or that you continue to receive the error after you successfully elevated the login account you are using? If you're not a sysadmin, you can't just make yourself one.
Permissions do appear to the the issue.
Also, is your 2008 source at least SP4? (or 2008R2 SP3)
-Eddie
Eddie Wuerch
MCM: SQL
March 27, 2018 at 1:43 am
Hello,
can you please try to provide full permission to your data file folder. this can help you to solve your access issue atleast.
March 27, 2018 at 5:54 am
skaggs.andrew - Monday, March 26, 2018 2:54 PM[...]
I tried adding the server role "sysadmin" to my username, but that also errors out.
Do you mean your attempt to make yourself a sysadmin failed, or that you continue to receive the error after you successfully elevated the login account you are using? If you're not a sysadmin, you can't just make yourself one.
Permissions do appear to the the issue.
Also, is your 2008 source at least SP4? (or 2008R2 SP3)
-Eddie
I suppose yes that is what I tried to do and makes sense that I cannot just give myself these rights, but essentially, I need these rights (I think) so I can migrate the databases. Suggestions on what I can do? I am the only person that works in this environment.
March 27, 2018 at 5:55 am
devrahul58 - Tuesday, March 27, 2018 1:43 AMHello,
can you please try to provide full permission to your data file folder. this can help you to solve your access issue atleast.
How do I go about doing this?
March 27, 2018 at 6:16 am
I have a few questions.
Is SQL Server actually installed on Server B?
You can see if it is by checking for SQL Server Configuration Manager. If this is installed, open it, select "SQL Server Services", and check to see if the SQL Server service is started.
When you installed Server B, one of the parts of the install allows you to specify what users or groups are administrators of the server. Do you remember this, and what did you make administrators?
Are you logging onto the server, and performing this, or are you attempting to do this from your local machine? If you are logging onto the server, try logging on using the LOCAL administrator account.
Do you have any idea how to grant permissions to a folder or file in Windows? Right click, properties, security? There are 2 basic types of files for a database. A .mdf (Data), and a .ldf (logs). You need to find the folder where these reside, and grant the permissions to this folder.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 27, 2018 at 10:38 am
Michael L John - Tuesday, March 27, 2018 6:16 AMI have a few questions.
Is SQL Server actually installed on Server B?
You can see if it is by checking for SQL Server Configuration Manager. If this is installed, open it, select "SQL Server Services", and check to see if the SQL Server service is started.When you installed Server B, one of the parts of the install allows you to specify what users or groups are administrators of the server. Do you remember this, and what did you make administrators?
Are you logging onto the server, and performing this, or are you attempting to do this from your local machine? If you are logging onto the server, try logging on using the LOCAL administrator account.
Do you have any idea how to grant permissions to a folder or file in Windows? Right click, properties, security? There are 2 basic types of files for a database. A .mdf (Data), and a .ldf (logs). You need to find the folder where these reside, and grant the permissions to this folder.
Hi Michael,
Thank you for your post. Let me see if I can answer your questions.
1. I am able to open SQL Server Configuration Manager. It appears that all services are in "running" state except for 2. Both (SQL Server Browser) and (SQL Server Agent) are stopped. Should these be manually started?
2. I was not part of the installation process for Server B. My client put together the server for me. Should I refer to them for the admin user/password? The did provide me a user/password to remote into the server and I'm the only one who works in this environment. I would have assumed they would grant me this access being they know I have to migrate from 2008.
3. I am using a remote desktop connection to log into the server.
4. I know the location of the folder. When I look into the properties of this folder, I can see the different group/usernames for the server. I just applied FULL CONTROL permissions to my group. I tried to go back and attach the mdf file, but I still get the same error message here.
Let me know what you think based on my answers. Thank you so much for your time and help.
March 27, 2018 at 12:31 pm
skaggs.andrew - Tuesday, March 27, 2018 10:38 AMMichael L John - Tuesday, March 27, 2018 6:16 AMI have a few questions.
Is SQL Server actually installed on Server B?
You can see if it is by checking for SQL Server Configuration Manager. If this is installed, open it, select "SQL Server Services", and check to see if the SQL Server service is started.When you installed Server B, one of the parts of the install allows you to specify what users or groups are administrators of the server. Do you remember this, and what did you make administrators?
Are you logging onto the server, and performing this, or are you attempting to do this from your local machine? If you are logging onto the server, try logging on using the LOCAL administrator account.
Do you have any idea how to grant permissions to a folder or file in Windows? Right click, properties, security? There are 2 basic types of files for a database. A .mdf (Data), and a .ldf (logs). You need to find the folder where these reside, and grant the permissions to this folder.
Hi Michael,
Thank you for your post. Let me see if I can answer your questions.
1. I am able to open SQL Server Configuration Manager. It appears that all services are in "running" state except for 2. Both (SQL Server Browser) and (SQL Server Agent) are stopped. Should these be manually started?2. I was not part of the installation process for Server B. My client put together the server for me. Should I refer to them for the admin user/password? The did provide me a user/password to remote into the server and I'm the only one who works in this environment. I would have assumed they would grant me this access being they know I have to migrate from 2008.
3. I am using a remote desktop connection to log into the server.
4. I know the location of the folder. When I look into the properties of this folder, I can see the different group/usernames for the server. I just applied FULL CONTROL permissions to my group. I tried to go back and attach the mdf file, but I still get the same error message here.
Let me know what you think based on my answers. Thank you so much for your time and help.
Couple of thoughts on this for you:
1. The browser service is generally only needed if you have multiple SQL instances, I'd leave it stopped. As for the Agent service, presuming you intend to have jobs run on a set schedule, yes, enable it and start it.
2. I would double check that the client put the account you're using to connect to SQL in the Sysadmin role.
3. Doesn't matter.
4. It's not *your* account that needs access to the location with the MDF / LDF, but the SQL Server service account, verify that it has full control over that folder and the files.
I thought your problem sounded familiar, check out this blog posting:
https://blog.sqlauthority.com/2018/03/14/sql-server-database-attach-failure-msg-2571-user-guest-not-permission-run-dbcc-checkprimaryfile/
(Please note, that is not me, I only wish I were as well-versed in SQL as Pinal Dave!)
March 27, 2018 at 12:39 pm
skaggs.andrew - Tuesday, March 27, 2018 10:38 AMMichael L John - Tuesday, March 27, 2018 6:16 AMI have a few questions.
Is SQL Server actually installed on Server B?
You can see if it is by checking for SQL Server Configuration Manager. If this is installed, open it, select "SQL Server Services", and check to see if the SQL Server service is started.When you installed Server B, one of the parts of the install allows you to specify what users or groups are administrators of the server. Do you remember this, and what did you make administrators?
Are you logging onto the server, and performing this, or are you attempting to do this from your local machine? If you are logging onto the server, try logging on using the LOCAL administrator account.
Do you have any idea how to grant permissions to a folder or file in Windows? Right click, properties, security? There are 2 basic types of files for a database. A .mdf (Data), and a .ldf (logs). You need to find the folder where these reside, and grant the permissions to this folder.
Hi Michael,
Thank you for your post. Let me see if I can answer your questions.
1. I am able to open SQL Server Configuration Manager. It appears that all services are in "running" state except for 2. Both (SQL Server Browser) and (SQL Server Agent) are stopped. Should these be manually started?2. I was not part of the installation process for Server B. My client put together the server for me. Should I refer to them for the admin user/password? The did provide me a user/password to remote into the server and I'm the only one who works in this environment. I would have assumed they would grant me this access being they know I have to migrate from 2008.
3. I am using a remote desktop connection to log into the server.
4. I know the location of the folder. When I look into the properties of this folder, I can see the different group/usernames for the server. I just applied FULL CONTROL permissions to my group. I tried to go back and attach the mdf file, but I still get the same error message here.
Let me know what you think based on my answers. Thank you so much for your time and help.
Let me know what you think based on my answers. Thank you so much for your time and help.
Here's what I think. You are in way over your head. It sounds as if you have over-sold your abilities to a client.
1. SQL Browser may or may not need to be started,
This will help you figure it out.
https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service
SQL Agent likely needs to be started. I am guessing that you will need to set up backups and have them run automatically? As well as many other possible things.
2. If the client installed SQL, then they should have the admin users and passwords. I would ask them first.
3. WHO are you logging into the machine as with remote desktop? That login needs to have admin permissions to the server.
4. If you are using the UI, are you getting both the log and the data files?
Try doing this with T-SQL, Here is the syntax, please change the values to match your needs. Run SSMS as an administrator, and let us know what error you get. USE [master]
GO
CREATE DATABASE [Junk] ON
( FILENAME = N'D:\SQLData\Junk.mdf' ),
( FILENAME = N'L:\SQLLogs\Junk_log.ldf' )
FOR ATTACH
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 9, 2018 at 11:04 pm
This was removed by the editor as SPAM
April 10, 2018 at 11:21 pm
Never assume, that’s the worst thing to do.
Go back to the client and request your account has at least server role dbcreator.
Don’t need to zip the files just copy them directly to the new server folders and ensure there are sufficient ACLs on the folders for the database engine account to access them
You could also use backup\rrestore instead of copying the raw files if you wanted to
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2018 at 9:58 pm
Even once you get past this issue, can't there be another issue with the way data is created in 2008 version versus 2017? Or does some automatic conversion occur when a database is being attached to a newer version of Sql Server?
----------------------------------------------------
April 12, 2018 at 6:09 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply