Post
Ask Question

How To Generate Database Scripts In SQL Server

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. 
  1. USE [EMP]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[ViewCountry]    Script Date: 28-02-2016 17:39:44 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author:      <Author,,Name>  
  10. -- Create date: <Create Date,,>  
  11. -- Description: <Description,,>  
  12. -- =============================================  
  13. CREATE PROCEDURE [dbo].[ViewCountry]  
  14. AS  
  15. BEGIN  
  16.     -- SET NOCOUNT ON added to prevent extra result sets from  
  17.     -- interfering with SELECT statements.  
  18.     SET NOCOUNT ON;  
  19.   
  20.     SELECT [CountryId]  
  21.       ,[CountryName]  
  22.   FROM [EMP].[dbo].[Country]  
  23. END  
  24.   
  25. GO  
  26. /****** Object:  Table [dbo].[Country]    Script Date: 28-02-2016 17:39:44 ******/  
  27. SET ANSI_NULLS ON  
  28. GO  
  29. SET QUOTED_IDENTIFIER ON  
  30. GO  
  31. CREATE TABLE [dbo].[Country](  
  32.     [CountryId] [int] IDENTITY(1,1) NOT NULL,  
  33.     [CountryName] [nvarchar](50) NULL,  
  34.  CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED   
  35. (  
  36.     [CountryId] ASC  
  37. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  38. ) ON [PRIMARY]  
  39.   
  40. GO  
  41. /****** Object:  Table [dbo].[State]    Script Date: 28-02-2016 17:39:44 ******/  
  42. SET ANSI_NULLS ON  
  43. GO  
  44. SET QUOTED_IDENTIFIER ON  
  45. GO  
  46. CREATE TABLE [dbo].[State](  
  47.     [StateId] [int] IDENTITY(1,1) NOT NULL,  
  48.     [CountryId] [int] NULL,  
  49.     [StateName] [nvarchar](100) NULL,  
  50.  CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED   
  51. (  
  52.     [StateId] ASC  
  53. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  54. ) ON [PRIMARY]  
  55.   
  56. GO  
  57. SET IDENTITY_INSERT [dbo].[Country] ON   
  58.   
  59. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (1, N'INDIA     ')  
  60. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (2, N'USA       ')  
  61. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (3, N'UK        ')  
  62. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (4, N'NEW ZEALAND')  
  63. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (5, N'AUSTRALIA')  
  64. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (6, N'SA')  
  65. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (7, N'SRI LANKA')  
  66. INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (8, N'test')  
  67. SET IDENTITY_INSERT [dbo].[Country] OFF  
  68. SET IDENTITY_INSERT [dbo].[State] ON   
  69.   
  70. INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (1, 1, N'GUJARAT')  
  71. INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (2, 1, N'MAHARASHTRA')  
  72. INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (3, 4, N'AUCKLAND')  
  73. SET IDENTITY_INSERT [dbo].[State] OFF  
  74. ALTER TABLE [dbo].[State]  WITH CHECK ADD  CONSTRAINT [FK_State_Country] FOREIGN KEY([CountryId])  
  75. REFERENCES [dbo].[Country] ([CountryId])  
  76. GO  
  77. ALTER TABLE [dbo].[State] CHECK CONSTRAINT [FK_State_Country]  
  78. GO   
I hope you liked this article.
 
Read more articles on SQL Server:


Similar Articles