How To Generate Database Scripts In SQL Server

Ankur Mistry

SQL Server SQLDatabase ScriptsSQL Server Database Scripts

16 Comments

Join the conversation! Your thoughts help the community grow.

Rajeev Punhani10y ago
Nice Share.
Ammar Shaukat10y ago
Great.
Amit Kumar Singh10y ago
Thanks
Thiru R10y ago
So far i missed "Script for Server Version" and messed-up many times. Thank you so much.
- Ibrahim Siddique9y ago
  Yes this is only the main glitch
Abdulmannan Bahelim10y ago
nice share
Ankur Mistry10y ago
Thank you all
Gowtham K10y ago
Good one, Thanks for sharing
Asfend Yar10y ago
nice share
csharp programmer10y ago
Is there any way to do the same in Schedules fashion using SQL Jobs ?.
Raja T10y ago
Nice, Thanks for sharing
Sibeesh Venu10y ago
Nice Share
Kirtan Parmar10y ago
thanks for sharing, and what do you suggest for fastest way to take backup from live sql database ?
- Mahesh Chand10y ago
  Fastest way will be use SQL Server's backup utility and copy backup file :)
Pankaj Kumar Choudhary10y ago
Nice Explain Sir.........
- Ankur Mistry10y ago
  Thank you

Many times as a developer we have to move data from development server to staging server and staging server to production server. This article will help developers to generate database scripts in SQL Server with data.

Step 1: Right click on the database you want to generate script and go to Tasks, then click Generate Scripts.

Step 2 : In the next screen, it's nothing but an Introduction screen that explains the following four steps,

Select Database Object
Specifying scripting or publishing options
Review your selection
Generate Scripts and save or publish

Click on 'Next' button.

Step 3:

Choose Objects; here either you can select all tables and stored procedures, etc. or you can select specific tables or stored procedures. Click on 'Next' button.

Step 4:

Set Scripting Options, You can select Output Type like "Save scripts to a specific location" or "Publish to web service," here we will select first option. Give a file name and location where you want to save the script.

Now, Click on 'Advanced' Button.

From the option tab you can set the property like "Script for Server Version." Choose Server version on which you want to run the script, here I have selected SQL Server 2012.

From the "Type of data to script," select "Schema and data," if you want to move only data you can select "Data only."

Schema Only option will create objects scripts only.

Click 'OK' and then 'Next' to proceed further.

Step 5: In the Summary, Just check path and click on 'Next'.

Step 6 : Save or Publish Scripts, Check if all the Action succeeds or not and click on 'Finish'.

Step 7: Open your Script file and run in SQL Server Query Window. For understanding below is the script which we have created.

USE [EMP]
GO
/****** Object: StoredProcedure [dbo].[ViewCountry] Script Date: 28-02-2016 17:39:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ViewCountry]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [CountryId]
,[CountryName]
FROM [EMP].[dbo].[Country]
END
GO
/****** Object: Table [dbo].[Country] Script Date: 28-02-2016 17:39:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [nvarchar](50) NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[CountryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[State] Script Date: 28-02-2016 17:39:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[State](
[StateId] [int] IDENTITY(1,1) NOT NULL,
[CountryId] [int] NULL,
[StateName] [nvarchar](100) NULL,
CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
(
[StateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Country] ON
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (1, N'INDIA ')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (2, N'USA ')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (3, N'UK ')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (4, N'NEW ZEALAND')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (5, N'AUSTRALIA')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (6, N'SA')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (7, N'SRI LANKA')
INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (8, N'test')
SET IDENTITY_INSERT [dbo].[Country] OFF
SET IDENTITY_INSERT [dbo].[State] ON
INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (1, 1, N'GUJARAT')
INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (2, 1, N'MAHARASHTRA')
INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (3, 4, N'AUCKLAND')
SET IDENTITY_INSERT [dbo].[State] OFF
ALTER TABLE [dbo].[State] WITH CHECK ADD CONSTRAINT [FK_State_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([CountryId])
GO
ALTER TABLE [dbo].[State] CHECK CONSTRAINT [FK_State_Country]
GO

I hope you liked this article.