Scripting database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Scripting database

I want to script my DB in a way that whenever I need to create it, just run the script. I can create the script in EM by choosing ‘Generate SQL Script…’ from right click menu on the DB. Then choose all the check boxes and click OK. Is it the best way to generate complete script for a DB? Does it create all the scripts in a way that I can produce the DB exactly same as the first one without data? CanadaDBA
Yes, that’s the best and the only way (besides SQL-DMO) to script the entire database. Make sure that you de-select the DROP<object> command under formatting tab and select all options under option tab.
a caveat though… The Option Script "Script SQL Server Logins" should be handled with care. Remember the BUILTINAdministrators and DOMAINAdministrators? When you run the script to recreate the database on another server, the process may throw a few unnecessary fits. Nathan H.O.
I don’t like using the Scrip SQL Server Logins option. I prefer to use the version of sp_help_revlogin posted on the forums with sp_hexadecimal. I’ve had a lot less problems with that. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA. – the same link Derrick mentioned. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.