Sql-server – Preferred Methods of copying a new database from Development to Production

developmentsql server

I am wondering what are preferred methods to copy a new (Development) database into a new environment.
The database will be empty, so essentially I am copying only the schema of this database and not the data.

Options include:

1) SSMS (Backup/Restore). I do not use this one because it will copy test data into Production.
2) SSMS (Generate Scripts)
3) Writing T-SQL myself
4) RedGate (or other 3rd party tools out there)

Does anyone have preferences or biases?

For myself, I use most (4): I create a new empty database using SSMS, then populate the schema of new database using RedGate.

Perhaps, it makes no difference. But I would like to know from the more experienced DBAs what their opinions are. Thank you.

Best Answer

I'd say the best way (in my opinion, of course) is your 2nd option, SSMS (Generate Scripts).

The reason I say to script out your database is because you will have save-able scripts that you can throw into version control. If you are only looking for the schema and no data, it's pretty simple to generate.

As for backup and restore, I agree with you. It's a good way to port databases, but if you need to go through the pain of having a script to remove existing development data, then you are having to go that extra mile (not to mention the possibility of "user error" and not deleting all data, allowing non-prod data in production).

For the third option, writing the T-SQL yourself, that is a very manual process. Might as well take advantage of SSMS' ability to generate a schema script for your entire database.