How can I get a SQL dump of a SQL Server 2008 database? That is, a .sql file with inserts to regenerate the data in another database, much like mysqldump.
Asked
Active
Viewed 4.5k times
3 Answers
55
From SQL Server Management Studio you can use the Generate Scripts command in the Tasks item of the right-click menu on the target database. From here you generate scripts for the structure and data and SPs etc: make sure 'Script Data' is set to True in the scripting options to get the inserts.
-
17In generate scripts, you need to press "advanced" and change the option "Types of data script" to "Schema and data" to have also the inserts – Alessandro Teruzzi Dec 01 '11 at 16:18
-
3the advanced button needed is under the 'set scripting options' tab/page – JonnyRaa May 21 '14 at 14:15
11
Not finding what I needed (scriptable tool dumping SQL statements), I have created a tool called SQL Server Dump which is inspired by mysqldump, but for SQL Server. You will find it on github
Daniel
- 1,034
- 1
- 10
- 27
-
3+1 for a useful tool. You are probably not the first one to write that, but it's available and working – PPC Mar 13 '13 at 21:31
8
I had a hard time finding the 'Script Data' option mentioned in stuartd's answer on SQL Server 2008 R2. I found the 'Types of data to script' option and changed it to 'Schema and Data' and that did the trick
Lloyd Banks
- 35,740
- 58
- 156
- 248