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.
-
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– Gordon Linoff2013-08-21 10:56:21 +00:00Commented Aug 21, 2013 at 10:56
-
.sql files are only used with databases that don't have robust backup options, like MySql.Joel Coehoorn– Joel Coehoorn2024-04-26 14:22:11 +00:00Commented Apr 26, 2024 at 14:22
Add a comment
|
4 Answers
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.
7 Comments
Jettero
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)
Vinu
I'm not understanding the structure of your data. Please share your table structure and a sample data.
Jettero
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
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.
clayRay
Also under Advanced, make sure you set Script for Server Version to SQL Server 2008.
|
This is where to get that option Type of data to script = Schema and data
Step 1
Step 2
Comments
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:
- Take a real full backup (.bak file), and then restore the backup
- Setup a connection between the two servers and use the "Copy Database" wizard
- 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.
1 Comment
ilhan
Does backup file contains users and user rights? I want backup of only structure and content.
My DB has 250 tables. I selected 10 and was able to export the data to .sql file
