SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrated DB to 2016, having ODBC Access problem


Migrated DB to 2016, having ODBC Access problem

Author
Message
briancampbellmcad
briancampbellmcad
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 622
Thanks in advance for any clues on this problem. I'm migrating several SQL Server databases from SQL2005 to SQL2016. One database is visible (I can connect to the tables) in a MS-Access frontend, and another database is not though I followed, I think, the same method for each. I retrieved a .bak file and loaded it on the C:\ drive of the host machine and from there I loaded it into SQL2016 using this script and all my tables are there with the expected data. The first line of code displayed that the logical data and log files were named ATCB_DB_Data and ATCB_DB_LOG and I can't seem to change that on my SQL2005 backup.



restore filelistonly from disk = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
RESTORE DATABASE CPCE_DB
FROM DISK = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
WITH
MOVE 'ATCB_DB_Data' TO 'D:\Databases\Data\CPCE_DB_Data.MDF',
MOVE 'ATCB_DB_LOG' TO 'D:\Databases\Data\CPCE_DB_Log.LDF',
REPLACE
;


The only other issue is when I go to the 'dbo' user it lists sqlAgentProxy rather than my active directory name:



USE [CPCE_DB]
GO
/****** Object: User [dbo] Script Date: 1/29/2018 10:41:36 AM ******/
CREATE USER [dbo] FOR LOGIN [NBCC-US\sqlAgentProxy] WITH DEFAULT_SCHEMA=[dbo]
GO


What I'm seeing in Access is this message:
Steve Jones
Steve Jones
SSC Guru
SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)

Group: Administrators
Points: 650161 Visits: 21472
That is a security error. My guess is something had issues when you restored.

That script doesn't restore, so I'm not sure what you did there. That gets meta data for the backup file. If you've restored it and things look OK in SSMS, try adding a new login and user and seeing if that lets you connect.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
briancampbellmcad
briancampbellmcad
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 622
If I'm seeing the 'Logical name' ATCB come up instead of CPCE (the expected logical name) do I have something set incorrectly on my backup process and how can this be fixed?:

MOVE 'ATCB_DB_Data' TO 'D:\Databases\Data\CPCE_DB_Data.MDF'
MOVE 'ATCB_DB_LOG' TO 'D:\Databases\Data\CPCE_DB_Log.LDF'

Steve Jones
Steve Jones
SSC Guru
SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)

Group: Administrators
Points: 650161 Visits: 21472
Nope, logical names in the backup are what's in the db. Perhaps you have the wrong file?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
briancampbellmcad
briancampbellmcad
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 622
Steve Jones - SSC Editor - Tuesday, January 30, 2018 1:59 PM
Nope, logical names in the backup are what's in the db. Perhaps you have the wrong file?

It's the correct file as the data is correct, but the naming of the data file and log file are wrong and don't appear to be able to be deleted or edited. I tried another database that did not have this problem and my original problem of not being able to connect to Access persists there too. As to why one Access database connects and 5 others do not is the puzzle. I'm not sure what permissions could be incorrect.

Steve Jones
Steve Jones
SSC Guru
SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)

Group: Administrators
Points: 650161 Visits: 21472
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.

The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
briancampbellmcad
briancampbellmcad
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 622
Steve Jones - SSC Editor - Tuesday, January 30, 2018 3:32 PM
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.

The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

The logical names are from the databases on the source server and they are misnamed... how to rename them has evaded me.

Sue_H
Sue_H
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82867 Visits: 16792
briancampbellmcad - Tuesday, January 30, 2018 3:46 PM
Steve Jones - SSC Editor - Tuesday, January 30, 2018 3:32 PM
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.

The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

The logical names are from the databases on the source server and they are misnamed... how to rename them has evaded me.


It's not going to matter at all for restoring. What's in the backup is what you would use for the restore. It is what it is.
After you get it restored, you can rename those logical files. Using t-sql it's just alter database...modify file. You can also do it in Powershell, SMO if you'd rather. There is an article up here that demonstrates each of those:
Changing SQL Server Database Logical Names

Sue



briancampbellmcad
briancampbellmcad
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3332 Visits: 622
Steve Jones - SSC Editor - Tuesday, January 30, 2018 3:32 PM
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.

The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

Don't the Users and their permissions copy over also? I've combed through any differences between the SQL Server 2016 migrated database I can see from Access and the one I cannot and I'm not seeing any permissions issues.

After restoring to 2016 from a .bak file I can read all the data from SSMS on each attempt and I've tried 6 different databases. Again one is visible from Access (Linked Table Manager) and 5 are not.

I've tried to locate what is special about the one that consistently succeeds and I cannot find anything. All the users are recreated from the .bak file.

Sue_H
Sue_H
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82867 Visits: 16792
briancampbellmcad - Wednesday, January 31, 2018 3:16 PM

Don't the Users and their permissions copy over also? I've combed through any differences between the SQL Server 2016 migrated database I can see from Access and the one I cannot and I'm not seeing any permissions issues.

After restoring to 2016 from a .bak file I can read all the data from SSMS on each attempt and I've tried 6 different databases. Again one is visible from Access (Linked Table Manager) and 5 are not.

I've tried to locate what is special about the one that consistently succeeds and I cannot find anything. All the users are recreated from the .bak file.


The users are in the database but the login mappings would not be. You may want to check those also.

Sue



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search