Move MS-SQL 2005 database to MS-SQL 2012

I need to move a database from a Win2008R2 server running MS-SQL 2005 to another server running Win2012R2/MS-SQL2012.
Can I simply do a detach from the old server and an attach on the new server? If so, does this process also handle the log files?
cwhitmore88Asked:
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.

arnoldCommented:
It will not be advisable, IMHO.

Backup and restore after you transfer the SQL logins.
Login transfer


https://support.microsoft.com/en-us/kb/918992
0
cwhitmore88Author Commented:
Logins on both servers are via Windows AD.
I'm using Veeam backup, so just restore the database to the new server?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you already have a backup and no changes was made in the database since then, yes you can just restore the database in MSSQL 2012 instance. After migration first thing to do is change the compatibility mode to from 9 (2005) to 11 (2012).
If the logins already exists in the MSSQL instance then you don't need to do nothing else. Otherwise you'll need to migrate the logins as well.
I'm assuming also that there are no jobs and SSIS packages to migrate.
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
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.

cwhitmore88Author Commented:
I was wrong. It does look like there are about 20 logins under that instance in SQL 2005. I looked over the link from arnold above, but it looks like all three methods reset the password. Is there a better way to transfer the logins and passwords?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Logins on both servers are via Windows AD.
I looked over the link from arnold above, but it looks like all three methods reset the password.
You can't change password from Windows logins in SQL Server. You can script them and then run the script in the SQL Server 2012 instance so they will be created there.
0
arnoldCommented:
The passwords do not get reset they get transferred as they are for the sql logins. windows based logins do not have passwords.

The reference in the DB security is to the sql account SID. this is why if you create a login manually using create login using the same username  and password, the restored DB will see the username as foreign SID mismatch.

the output from runing the SP enumerating the logins
you can copy the ones you want primarily, sql logins.  The windows logins that are domain based, can be copied as well. The logins that are sql server system specific can be skipped.
mSSQL$USEr.......
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 2005

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.