How to export database with data in MSSQL?

Question

How can I export my 2012 database with data to a .sql file so I can import it to a 2008 server? I've never been able to export the data only the structure.

Shaeffer

. . If the database is small, then the SSMS "create scripts" wizard has an option to export the data as well. Just look on the "advanced" tab to find the right option. – Lord

.sql files are only used with databases that don't have robust backup options, like MySql. – Getupandgo

Answer 1

You can right click on the database in management studio. then go to,

Tasks --> Generate scripts --> Advanced

There you can enable "Type of data to script" as Schema and data, then generate the script. So that your script file will be populated with your data in table.

Wigan

It is not working well when there is a Text type column inside binary data. It was scripted INSERT as unicode text so at restore makes error! (Visual FoxPro uses "memo" columns for this type of data and it is translated to "text" in SQL Server) – Provided

I'm not understanding the structure of your data. Please share your table structure and a sample data. – Wigan

I just made a sample: CREATE TABLE [dbo].[IKTCS]( [IKTSSZ] [float] NOT NULL, [CSMEGNEV] [char](254) NULL, [CSITTCSATT] [char](254) NULL, [CSFILE] [text] NULL, [CSUTV] [char](254) NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO INSERT [dbo].[IKTCS] ([IKTSSZ], [CSMEGNEV], [CSITTCSATT], [CSFILE], [CSUTV]) VALUES (1, N'file', NULL, N'EߣŁB†B÷BňBóB‚matroskaB‡B…S€g /Ă?ËM›tÂM»ŚS«„', NULL) – Provided

This is a working script. You should imagine in the TEXT some special binary what contains 'characters' what made the script unworkable. I didn't made investigation what code doing this situation. – Provided

Also under Advanced, make sure you set Script for Server Version to SQL Server 2008. – Stane

If you have the Generate Scripts wizard, you won't see Advanced until about the third screen in. – Derbyshire

That "Advanced" button sure can be hard to find 😉 – Myalgia

Answer 2

Don't use .sql files for this

This is a common request from those with, say, a MySQL background, but the MySQL tools around this are actually kind of awful. SQL Server has much better options:

1. Take a real full backup (.bak file), and then restore the backup
2. Setup a connection between the two servers and use the "Copy Database" wizard
3. I don't really recommend this, but in a pinch if you can handle some downtime, take the database offline (detatch it) and copy the .mdf file(s). Then bring the database back online, and the copied mdf file can be attached on the new server. It's necessary to take the db offline because otherwise some recent changes to data might still be in memory or ldf files. Taking it offline ensures everything was flushed/written.

Getupandgo

Answer 3

This is where to get that option Type of data to script = Schema and data

Step 1

Step 2

Halogen