Preferred Methods of copying a new database from Development to Production

Question

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.

Answer 1

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.