Generate Script Of Database in SQL Server 2008

Pre-requisites

This article assumes knowledge of SQL Server.

In SQL Server the required database has various tables with data. Here we use the "Address-Book" database.

STEP 1

Choose the database to create a database script in the "Object Explorer" section of SQL Server.

And choose the following option in sequence for generating a script.


STEP 2

We choose which object you want to generate a script for in the "Generate And Published Script" Wizard.

The first step is to describe how many steps are necessary to generate a script.

A. Introduction

There are the following four steps to complete this wizard:

1. Select database object.

2. Specify scripting or publishing options.

3. Review your selection.

4. Generate a script, then save or publish it.

B. Choose Object

Select a database object to script. There the following two options:

Using the first option: (script an entire database and all database objects) we generate a script for the entire database.

Using the second option: (to select a specific database object) we generate a script of specific database objects, like tables, Store Procedure, View and so on.

Here we select the table objects in our database.



C. Set Scripting Option

In this window we select a location to store our script. If you want to rename a script file then rename it with a custom name. If you want to generate a database script without tables data then click on the "Next" Button.

If we want a database script with table data then we must use the setting for generating a script with "Tables Data".

Click on the "Advanced" option  In the Advanced Scripting Window,  select "Type Of Data To Script" with the "Schema and Data" option.



The default location for storing the script file is "C:\Users\......\Documents\script.sql".

Click on "Next" to continue.

 

D. Review Your Selection

In the Wizard Window click on the "Next" button to continue.

E. Save Or Publish Script

In the Wizard Window click on the "Finish" button.

Restore Data

We will now restore a database. To do that we require the same name (that we used for the database script). Of "Blank Database" to restore it. If you want to use an existing same name database then you must "Drop All Database Tables".

Now open a "script.sql" with Notepad and copy the entire script from the Notepad file.
Now return to SQL Server and click on "New Query"   and paste onto the entire code in the Query Wizard.
After pasting it execute the query

 

Finally, we get the message "Query Executed Successfully". We have restored our database successfully.

Summary

Now in this article we learned how to manage our database and secure it.

I hope this article helps you. For any queries regarding this article please comment on it. I will try my best.

Thank you.