SQL Server 2014 attach DB failes

I have installed a SQL Server 2014 on a new machine
SQL 2005 runs on another
I did detach my test database from 2005, copied to d:\mssql\data (created by admin) on 2014 server
I did not copy the log from 2015 (to have it rebuilt by it)
Now with Studio on the 2014 machine , I issue this : exec sp_attach_single_file_db 'gmTest','d:\MSSql
\Data\gmTest_Data.mdf
I directly receive this error :
File activation failure. The physical file name "d:\mssql2005\data\gmTest_Log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'gmTest'. CREATE DATABASE is aborted.
The thinks is that the full name specified is the one of the "old" server !
I suppose it comes from inside the data file
How shell I fix this ?
LVL 1
LeTayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
You need to copy both data and log files.
0
arnoldCommented:
You can not.
You need to use intermediary SQL 2008 on which you will restore the Db from backup which will update its schema as well as verify any programming you have .....
Not sure you San go from 2005 directly to 2014.  You may have to go 2005->2008=>2012/2014.


Have you already copied the SQL logins from the kid to the new?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
CREATE DATABASE gmTest
      ON (FILENAME = 'd:\MSSql\Data\gmTest_Data.mdf) 
      FOR ATTACH_REBUILD_LOG 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LeTayAuthor Commented:
Will try Vitor proposal
0
LeTayAuthor Commented:
Doing Vitor statement
Error looks similar, it refers to the log of the other server (mssql2005/etc.....) !
Here is the error message :
File activation failure. The physical file name "d:\mssql2005\data\gmTest_Log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'gmTest'. CREATE DATABASE is aborted.
0
arnoldCommented:
You can not. Attach cross version DB/log files for SQL.  Their schema/structure are incompatible in that type of process.
A backup on older version/restore on newer version will gave the DB/log file structure updated.
With that said, not sure you can go from 2005 to 2014 SQL server. Directly, you have to go through intermediary steps 2005 -2008/r2-2012-2014.
0
LeTayAuthor Commented:
I didn't read anything about these intermediate steps
Will try the backup/restore first, hoping it will be OK
Otherwise, good luck for me !
0
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
I missed the 2014 part.  It is true you have to go through intermediate steps and along the way, you have to increase the compatibility version.  You could attach a 2008 DB to 2014 but not 2005.  Your choice would be to backup the 2005 DB (ensure compatibility level is set to 90 prior to backup) and restore in either SQL 2008 or SQL 2012.  After the restore is completed, change compatibility level to 100 SQL Server 2008 or 110 for SQL Server 2012.  After this is completed, you can then attach the database to SQL Server 2014.
0
LeTayAuthor Commented:
Not aware about compatibility level
Where shell I change it ?
thanks
0
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Go to properties for the database, on the left hand side, click on Options and you will see the Compatibility type option.  Note that with SQL, you can set compatibility to three choices only which are current version and two previous versions.  For 2012, the options are:

- SQL Server 2005 (90)
- SQL Server 2008 (100)
- SQL Server 2012 (110)
0
LeTayAuthor Commented:
Backup/Restore worked fine !
0
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Glad you got it working.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
LeTay, do you still need help with this question?
0
LeTayAuthor Commented:
No, it's okay
Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good.
Please close this question by accepting the comment(s) that helped you solved the issue.
Cheers
0
LeTayAuthor Commented:
In fact I didn't use the intermediary SQL
I just restored the backup
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.