How to create Sql server database script and Create database from that script

Introduction: In previous articles i explained How to convert/downgrade SQL Server 2012,2008 database to SQL Server 2005 or lower version and Take automatic backup of Sql server Database and Take automatic backup of Sql server Database and Convert SQL Server 2012,2008 database to SQL Server 2005 or lower version and How to get column values as comma separated list in sql server or Convert column values to row
Now in  this article i will explain with example the step by step procedure to create/generate script of Sql server Database with full schema and data and also How to create Database from the generated script. Sql server 2008 R2 is used for this example but it also works for other versions.

Implementation: Follow the steps mentioned below:

• Step 1 : Right click on your database in the Sql server -> Tasks -> Generate Script as shown in figure below:

• Step 2: Generate and Publish Scripts dialog window will open as shown in figure below. Click on next button.

• Step 3: Now another window will open as shown in figure. From here you can select the database objects to script. Select Script entire database and all database object option if you want to script whole database. But if you want to script only tables or stored procedures etc then select specific database objects option and tick the table or stored procedures etc as per your requirement.

• Step 4: A new window will open as shown in figure. Browse the location for the file e.g. D:\MyDataBaseScript. Click on next button. By default script generates the schema of the database i.e. It will create script of all the tables but not data. But if you also want to script your database with data then click on Advance button.

• Step 5: A new window will open as shown in figure. Scroll down and locate Types of data to script option and set it to Schema and data. By default it is set to Schema only. Click Ok and then click next.

• Step: 6 Summary window will open. Click next.

• Step: 7 Save or Publish Scripts window will open. It will fetch and create the script of your database.Now your are done. Click on Finish button. Your script will be saved in the location where you set the path to store the script file. In our case “D:\MyDataBaseScript”.

Now the next requirement may be to create the DataBase from the script. So i am going to explain

How to create Database from Sql server script

Note: If there is database in the Sql server with the same name as the database in the script then it will give errors. So either change the Database name in the script or rename the existing database.

In order to create Database from the script follow the steps: 

Step 1: Open Sql server and drag the script that we created and  is stored in D:\MyDataBaseScript.sql on to the Sql server window and click on Execute as shown in figure:

Step 2:  It will create the Database named MyDataBase. Click refresh button of Object browser window of Sql server and you can see the Database named MyDataBase created in the Sql server’s object explorer as shown in figure:

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post