How to export ms sql database using sql server management studio





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.


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




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.


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.


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.


This is just a summary of what it will do.


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


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

USE [students]
/****** Object:  Table [dbo].[student_info]    Script Date: 05/29/2011 15:20:20 ******/
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

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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s