👉 I am using SQL Server 2008. The process is quite similar in new versions of SQL Server.
But, what is it and why do it?
You can simply take a backup of the entire database and restore it in a different database elsewhere. However, when you take a backup of an SQL Server database, it also takes the data along with the objects (an object can be a table etc.).
However, sometimes developers and DBAs want to take the backup of objects (just the objects), without the data. A database often has many objects like tables, stored procedures, functions etc. and it be would difficult and time consuming for a DBA or a developer to create scripts manually of each object.
Note: You can generate scripts with data too.
Here’s how a script looks like.
USE [ENCODEDNA] GO /****** Object: Table [dbo].[Birds] Script Date: 01/14/2016 12:04:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Birds]( [ID] [int] NOT NULL, [BirdName] [varchar](50) NULL, [TypeOfBird] [varchar](50) NULL, [ScientificName] [varchar](50) NULL, CONSTRAINT [PK_Birds] PRIMARY KEY CLUSTERED ( [ID] 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 ANSI_PADDING OFF GO
I mean, I’ll be sick and tired doing this to each object type, assuming the database has approximately 200+ tables (it can more, trust me) and I don’t know how many stored procedures and/or functions. Hmm, you forgot the views and keys. Although, you may sometimes generate scripts for each object one by one, but for a big database, in one go, it would be difficult.
This is where the Generate Scripts … method in SQL Server comes into picture. It’s a wizard, which allows you to create a script of an entire database easily. I’ll show you how this is done.
A script is also useful, when you want to create a new database elsewhere. It is easy to manage and shift (emailing etc.).
So, First thing first, make sure you have access to SQL Server Management Studio and the database for which you want create the script.
Follow these few simple steps.
1) Open SQL Server Management Studio.
2) Drag the mouse and set focus on the database for which you want to generate the script. Right click the database → choose Tasks → and select Generate Scripts….
3) It will open the wizard. Click the Next button.
4) Choose the database you want to script. You also have an option to select the entire database.
5) Next, choose the options for objects you want to script.
This step is important. In any case, you want to generate the scripts along with the data, simply scroll down the options list, and find Script Data. It is set as False by default. Set it True to generate the scripts of the objects (like tables) with its data.
6) Next, choose the Object Types to script, like Tables, Stored Procedures, and Functions etc. It gives you a choice with option boxes. Or, click the Select All button to select all the objects.
7) The next step will depend on what Object Types you have selected (refer to step 6). So, if you have selected Stored Procedures and Tables, it will ask you to select the stored procedures from of the procedures, followed by the tables.
If you have selected only tables, it would ask you select the tables for the script. You have option to select all the tables of select tables (multiple tables) using the check boxes.
8) Ok we are almost done. This is the final step.
Now, you have to choose the location where you want the script to be generated. You can save it in a file or generate the script in a New Query Window.
In-addition, if you choose the option “Script to file”, you can ask the wizard to create a Single file (that is generate the script for the entire or selected objects in a single file) or create multiple files per object (each object will be scripted in an individual file). I always choose the latter option. Since, its more manageable.
