Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required INSERT statements. In doing so, I can reset the development environment quickly after each test.

Can anyone tell how to accomplish this using SQL Server 2008, SQL Management Studio, and/or Visual Studio 2010?

Note: None of the tables have any foreign keys, I have full permissions in the source database, and the number of rows per table is no more than a few dozen.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

As well as using SSMS, you can use SSMS Tools Pack

I prefer it: more options, batching, cleverer all round.

Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values than the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.

share|improve this answer
    
+1 the perfect tools pack –  garik Jan 24 '12 at 12:38
    
@gdn Thanks, this worked a treat. I exported the scripts and put them in my VS 2010 SQL Server Database Project and now all my static data is under source control and is part of the database deployment package. Sweet! –  JoeGeeky Jan 24 '12 at 19:51
1  
This is actually built into SSMS 2008. Right click the database, go to task, then generate scripts. In the options set Script Data to true. There is also another SSMS add in that makes it easier to get to: ssmsaddins.codeplex.com –  m4tt1mus Feb 17 '12 at 23:07
add comment

Use the standard SSMS scripting feature:

Right click on database in object explorer->Tasks->Generate Scripts

Then choose what tables do you want to script and not forget to check "Script Data"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.