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.

1 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.

  • Thank you! (Just a side note: RedGate prepares scripts and it does the version control for me as well--though I am still fine tuning that).
    – Dina
    Jun 10, 2013 at 15:23
  • @Dina Understandable. I haven't had the opportunity to use RedGate's tool, so I can only give advice on native options. Jun 10, 2013 at 15:24

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.