how to move a database from a SQL Server 2008 R2 Enterprise server to SQL server 2012.

BuffStateIT
BuffStateIT used Ask the Experts™
on
Move a SQL database from one server to another server.  How to move a database from a SQL Server 2008 R2 Enterprise server to SQL server 2012. We know there are at least 3 ways to do it. Yet, what is the most fool proof, simple or generally used. is there a way to use MS SQL Data Wizard for managing your MS SQL database move? Backup/Restore? What would be the actual steps? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
The easiest by far (if you can take the downtime required) is to stop all activity against it on 2008 server (but I mena ALL), take a full backup to a share between 2008 and 2012 server, set the 2008 db offline so nothing can touch it, restore it on new 2012 server, test against it and move on.
IF you must limit the downtime to minimum - then almost same but you must enable-perform Log shipping from 2008 to 2012 so when the cutover time comes all you'll need is to bring down all activity against 2008 database, stop/pause log shipping, take a final tail-log backup on 2008 and restore it to 2012, test, etc. remeber to disable Log shipping after moving successfully to 2012 SQL server.
JnavarroMcIT Consultant

Commented:
You can use Data Migration  Assistant. Project Type: Migration.

Just make sure you have a shared path that both servers can access and have enough backup space. The tool will also help you migrate the Logins.

Author

Commented:
Here is what we get in using Data Migration Assistant:

See attached.

Any clues or ideas?

Thanks in advance.
Connection-to-the-Server-failed..png

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial