SQL Server – 2008 – Generate Script of Database, Generate script to copy whole database schemas and All other object in database, like Tables with data, SPs, Views, Triggers, Functions, Database constraints etc.

Check how to create a script using T-SQL

While we are working on any project and uses SQL Server as a database, we often need to copy database schema either with data or without data to create database on other server. So it inspires me to write down the blog post about ‘how to generate SQL script using SQL Server Management Studio’

Here I will explain step by step process with screen shot that how we can copy database schema with all the database objects.

First of all open SQL Server Management Studio and expand databases node like below images step 1.

Step 1

Now select the database for which you want to generate SQL Script as like below image Step 2. Here I have selected pubs database for demo. Right click on selected database, click on Tasks option and then click Generate Scripts...  I have marked option with red circle in image.

Step 2

Once you click on Generate Scripts option from above image it will show up the below image step 3

Just click on the next button from below image step 3

Step 3

Now in below image you can see the list of all the available databases, by default on which database you have right clicked it is selected but you can change here if you want to generate script for other database. In Step 4 image there is one check box labelled with ‘Script all objects in the selected database’.  Check this check box if you want to generate script for all the objects from database, if you will not check this check box it will ask on next step for database object selection.

  

Step 4

After selecting the database and checking check box it will show up the below image step 5.

Here you can select what you want to include in script, like script table with data or without data, you want to add script like if exist then drop the object first then create it etc. For scripting table with all the data select true in script data option as I have selected in below image. Then click on next button.

Step 5

Now this step 6 has some output file type and file location related options. I have selected 3^rd radio button in below image for generating a script in New Query Window but if you don’t want to generate script in new query window and want to generate script into clipboard then select 2^nd radio button labelled with Script to Clipboard. If you want to generate script in a file then select 1^st radio button Script to file, once you select this option it will ask file name and directory to save the file on hard drive.

I have marked files to generate option as a 3 and then 3.1 and 3.2 in the red colour. If you want to generate whole script in one single file then select single file (3.1), by default it is selected. If you want to generate different file per object then select 3.2 option. If you select 3.2 option and suppose you have 10 tables in database then it will generate 10 different SQL file.

Step 6

Step 7 below image is just a summary page, showing all the options you have selected till step. Just click on the finish button to proceed.

Step 7

Step 8 showing the progress for generating database script

Step 8

Step 9 showing the successfully completion of script generation process.

Step 9

Step 10 shows that all database object has been scripted in new query window for pubs database. Now your script is ready...

Step 10

Please let me know if you have any question on this script generation wizard.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)


This is the common error for sql server while establish connection from visual studio to sql server.


If you encounter this error, while establishing connection from visual studio to to sql server, than first things to check in web.config file that have you set sql server instance name properly and correct?


You can encounter above error if you have 


Change your PC name or sql instance name
or you have installed new isntance of sql server.


EG. Currently your PC name is 'PCName', and sql instance name is 'SQLServer'
Now you are changing your computer name from 'PCName' to 'MyPC' or sql server instance name from 'SQLServer' to 'MySQLServer' then you must change connection string 
in your web.config file  like below sample connection string


Old Connection String:
Data Source=PCName\SQLServer;Initial Catalog=SampleDBName;Integrated Security=True


New Connection String:
Data Source=MyPC\MySQLServer;Initial Catalog=SampleDBName;Integrated Security=True


Solution:
First check your sql server instance name after opening sql server management studio, then check connection string in your vs.net solutions,
then compare both instance name if both instance name are not same then please make both name same and save changes you made in web.config file and run the project