46

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.

Abdullah Ilgaz
  • 719
  • 1
  • 17
  • 39
Jamie
  • 3,031
  • 5
  • 36
  • 59
  • 3
    . . 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. – Gordon Linoff Aug 21 '13 at 10:56

1 Answers1

92

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.

mehmetseckin
  • 3,061
  • 1
  • 31
  • 43
Vinu
  • 1,222
  • 1
  • 11
  • 14
  • 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) – Jettero May 11 '16 at 07:35
  • I'm not understanding the structure of your data. Please share your table structure and a sample data. – Vinu May 11 '16 at 08:29
  • 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) – Jettero May 14 '16 at 14:50
  • 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. – Jettero May 14 '16 at 14:57
  • 2
    Also under Advanced, make sure you set Script for Server Version to SQL Server 2008. – clayRay Mar 01 '17 at 21:18
  • If you have the Generate Scripts wizard, you won't see Advanced until about the third screen in. – Captain Hypertext Jul 28 '17 at 20:20
  • That "Advanced" button sure can be hard to find – Netside Oct 26 '20 at 06:27