How to export ms sql database using sql server management studio

 

Requirements

 

Prerequisites

You already know the basics of using Server Management Studio. Like connecting to the ms sql server, browsing the different databases and tables, executing sql statements.

Steps

First you have to open up SSMSE. Right-click on your database and select task then generate scripts.

image

image

 

Select your database. You can also check the script all objects in the selected database. So that it will no longer ask you later on what object (tables, stored procedures, views, etc..) to include in the sql file that will be produced.

image

Just go ahead and press the next button right away if you see this screen. That is if you don’t really see something that makes sense.

image

Then select the script to file. Make sure that you have also selected single file and Unicode text. After that, just click on the browse button and select the location where you want to export the sql file. And then name it using the name of the database or any name you want.

image

This is just a summary of what it will do.

image

Then here’s the screen that will notify you if the operation was a success or not.

image

If you open up the sql file that has been produced:

USE [students]
GO
/****** Object:  Table [dbo].[student_info]    Script Date: 05/29/2011 15:20:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_info](
	[idnum] [nchar](10) NULL,
	[fname] [nchar](10) NULL,
	[lname] [nchar](10) NULL,
	[course] [nchar](10) NULL,
	[address] [nchar](10) NULL
) ON [PRIMARY]
GO

Basically it looks like the sql file that’s been produced by mysql. But it won’t work if you try to import it using phpmyadmin. Since ms sql and mysql are very different databases.

So what you can do with this sql file? You can actually open it up on notepad, copy the contents and execute it on SSMSE to rebuild your table. As you can see from the code above, it doesn’t create the database, it just uses it. So you still have to create a database with the same name as the one specified in the sql file to make use of this sql file. Another thing, it doesn’t actually backup your data. Or dump statements as they call it.

So what’s the purpose of this tutorial? To create an sql file which describes an ms sql database. If you are planning to migrate from ms sql to mysql database. Then this file can serve as a basis for the database that you are going to create in mysql. All of the table structure are in one place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s