January 18, 2016 at 8:03 am
Hi all not having any experience on SQL at all I am hoping there will be some magical answer to my question.
I have an MSSQL2005 I have been asked to move a single Database from that server to my own Server.
I have installed SQLServer 2012 Express 64bit with SQL authentication.
I was told you can detatch, then copy the MDH file on the 2005 copy it to the 2012 and voila!!
This seemed to work ok but then when I try to access the file from the Web portal I get the a login error.
I believe the issue is that the SQL 2005 is Windows Authenticated and the Database has a Padlocked Icon whereas the 2012 database is SQL authenticated.
Is there a super easy way to copy, migrate, backup and restore from 2005 windows authenticated to SQL 2912 Express SQL authenticated.
Thanks
January 18, 2016 at 8:31 am
I hope the move between servers is meant to be permanent, or that you took a backup, using the BACKUP DATABASE command before you did the move, because what you did, detach and attach to a newer version of SQL Server, made that a permanent move.
All you need to do is connect to the server locally and create a SQL Login, assuming when you did the install you chose "Mixed" as the security settings. If not, no issue, you just have to connect to the server locally and change that. Then create a SQL Login. All this is possible through SQL Server Management Studio. You can download that independently from Microsoft and use it to connect to the server. Make sure you connect as the same user that the server is currently running under because that's probably the system administrator of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2016 at 8:59 am
Thank you.
The detatch did not work so in the end I stopped the SQL2005 and just copied the MDH file.
So with regard to changing the authetication, would I need to do that on the 2005, then copy the MDH file and move to new one, or could I move it the new one run Studio on the new and make the change after the move.
January 18, 2016 at 10:15 am
No, the security has to be done after you successfully move the database to 2012.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2016 at 10:37 am
Thank you.
I will try it and hopefully get this sorted.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply