I have a database on a hosetd sql 2008 server.

I have made some changes to the schema in development but don't want to upload this as I want to keep the data that is on the server.

Is there a script I can run that will backup the data in my tables, preferably also have a way to save the shema too.

Then I want to be able to upload the schema changes from dev and restore all the data from my backup.

I am looking for a free way to do this so I can't purchase any tools.


Folks, SQL Server 2008 Management Studio does allow you to script out the schema AND the data - it's a new feature that was added, but not commonly known about.

In the Script Wizard, under Table Options, change the Script Data setting to True (it's False by default) and it will generate INSERT T-SQL statements with all the data in, as well as the table schema.

This was also discussed previously on SF in the question here and I just blogged about this with a screenshot - see Scripting schema AND data with SSMS in SQL 2008.

Hope this helps!

| improve this answer | |

Assuming you have owner rights on the database, you can right-click on the database name and select "Generate Scripts" to create the script. That will start a wizard that allows you to select which objects you want to script and dump that script to a file, a query window, or the clipboard.

There isn't an automated way in Management Studio to dump all of the data, but you shouldn't need to restore the data just to do schema changes - changing a table's definition doesn't normally delete or change data, and no other objects directly touch data. I'd just use the standard backup command in Management Studio make a backup before making the changes to your production server.

| improve this answer | |

"Generate Scripts" is nice, but if you want to be able to "diff" two versions of a schema, I'd recommend ScriptDB (http://www.codeplex.com/ScriptDB). It outputs virtually the same script as "Generate Scripts" (it uses the SQL Server management objects just like the management studio), but places the output into directories with files in them, much like the view in the management studio. It's very handy for running diff's between versions of a schema. I find, with trying to diff the output of "Generate Scripts", that sometimes large blocks of the code move around between versions but otherwise have no changes (particularly when it's coming from two different databases that independent developers have been working in).

| improve this answer | |

Have you considered implementing those changes in a 'staging' or 'qa' environment beside your 'dev' environment?

Take a backup of your production database. Restore it into a new db in the same environment where you're hosting 'dev'. This will give you the opportunity to run those update scripts into a mirror of the live data. Then you'll know how successful your change scripts are.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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