Search code examples
sql-serversql-server-2012sql-server-2008-express

Move database from SQL Server 2008 Express to SQL Server 2012

We are looking at migrating one of our databases from SQL Server 2008 Express to SQL Server 2012. The guy that has been taking care of the database says he always does a detach, copy the files to the new server and attach. This takes the database offline of course.

Is there a benefit in doing that vs. right click backup, copy backup files and right click restore on the new server?

Taking a backup should pull everything without taking the DB offline correct?

Is there a recommended best practice for this?

(New to the MS database world)

Solution

  • You could Backup and Restore

    You will still have to "un-orphan" sql-users to db-users.

    BACKUP DATABASE MyDB TO DISK = 'c:\backups\MyDB.bak' WITH FORMAT

RESTORE DATABASE MyDB FROM DISK = 'c:\backups\MyDB.bak' WITH REPLACE, 
MOVE 'MyDB' TO 'd:\sqlfiles\mydb.mdf',
MOVE 'MyDB_Log' TO 'd:\sqlfiles\mydb_log.ldf'

    Change the paths however you want.