3

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.

Improve this question
1

2 Answers 2

Reset to default
5

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/

Improve this answer
3

The backup/restore method is generally my preferred way especially if you're on 2008R2 or higher due to backup compression. If you don't have access to that feature, the backup file generally is still a smaller file copy than the .mdf/.ldf file copy because a backup file removes any wasted space in those database files

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.