Sql-server – the recommended method to move databases

migrationsql server

I want to know the recommended method to move a database and data from dev to pre-prod to production in SQL Server. The databases are originally created in MS Access so some upsizing to SQL Server will be needed

The 3 options I've read about: (I'm sure there are others)

  • Use Detach and Attach – I've been led to believe this doesn't give a clean build and may cause issues but is easy to do as just moving/copying the .mdf, .ldf files
  • Script the database and objects into each environment – this provides a clean build but is more time consuming
  • Do a back-up/ restore – takes longer but appears to be a favored method.

Any advice most welcome.

Best Answer

Do a back-up/ restore - takes longer but appears to be a favoured method.

This is what we prefer. -- Backup and Restore. Safer Method.

Option to detach the database using sp_detach_db and then attach it using CREATE DATABASE ... FOR ATTACH; or detach and attach using the GUI will actually detach the database from the source server.

Many things can go wrong here, which is why we prefer backup and restore. The backup process is online and since the source database was never made un-available in the first place, you don't risk destroying anything in case of mistakes, data corruption, or whatever other things that can happen.

Also for data move, depending upon how frequent you're data needs to be moved from Dev to pre-prod to prod, RedGate's SQL Compare tool can be used to keep the schema of 2 databases in sync.(Includes cost but depends upon you're needs and wallet)

http://www.red-gate.com/products/sql-development/sql-compare/

Also, something similar to Redgate as free open source tool you can find something :https://code.google.com/p/sql-dbdiff/

Related Question