MS SQL generate create database script – quick tips

If you want to quickly generate script that will create – recreate your database on some other server (standalone or Azure),
below are some quick tips.
I’ve created them for my colleagues within 5 minutes, so it’s really a brief.

generatescriptoptions

Quick description :

  • Check for object existence : True – see Comment #1
  • Continue scripting on error : True – see Comment #1
  • Generate script for Dependent Object : TrueIMPORTANT !
    When you have db dependent objects on each other, in CREATE script the objects will be in good order.
    If you miss to switch it True and you have dependent objects then your script will fall, so you will notice it.
  • Script DROP and CREATE : Script DROP and CREATE – see Comment #1
  • Script for Server Version : SQL Server 2012 – if you want to be MS SQL Azure compatible. SQL 2012 got most of the most needed stuff.
  • Script USE DATABASE : False – important if you want to be MS SQL Azure compatible, USE is forbidden on Azure DB.
  • Script indexes : TrueCRITICALLY IMPORTANT !!
    Default false won’t script your indexes¬†and you can easily miss it because the create script will not fall !!!
  • Script Full-Text indexes : True – critically important when you use full text search. Good practice to click it true.
  • Script Triggers : TrueCRITICALLY IMPORTANT !!
    Default false won’t script your triggers and you can easily miss it because the create script will not fall !!!

Comment #1:¬†DROP & CREATE & Check for object existence & Continue : this combination is good for developers work, when DB schema frequently changes, developers need to quickly update database and we don’t care for legacy data – just drop old database, re-create it with new schema.

Advertisements

One thought on “MS SQL generate create database script – quick tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s