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.
Moderator
SQL-Server-Performance.com

I don’t like using the Scrip SQL Server Logins option. I prefer to use the version of sp_help_revlogin posted on the www.sqlteam.com 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.

http://www.databasejournal.com/features/mssql/article.php/2228611 – the same link Derrick mentioned. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>