Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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.

share|improve this answer
    
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 '13 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. –  Thomas Stringer Jun 10 '13 at 15:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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