CODEWITHSUNDEEP
sqldatabasesample
Joseph
Joseph

Reputation: 1

Methods of maintaining sample data in a database

Firstly, let me apologize for the title, as it probably isn't as clear as I think it is.

What I'm looking for is a way to keep sample data in a database (SQL, 2005 2008 and Express) that get modified every so often. At present I have a handful of scripts to populate the database with a specific set of data, but every time the database is changed all the scripts have to be more or less rewritten and I was looking for some alternatives.

I've seen a number of tools and other software for creating sample data in a database, some free and some not. Are there any other methods I haven’t considered?

Thanks in advance for any input.

Edit: Also, if anyone has any advice at all in dealing with keeping data in sync with a changing application or database, that would be of some help as well.

Upvotes: 0

Views: 312

Answers (3)

HLGEM
HLGEM

Reputation: 96552

If you are looking for tools for SQL server, go visit Red Gate Software, they have the best tools. They have a data compare tool that you can use to keep lookup type tables up-to-date and a SQL compare tool that you can use to keep the tables synched up between two datbases. So using SQL data compare, create a datbase with all the sample data you want. Then periodically refresh your testing db (or your prod db if these are strictly lookup type tables) using the compare tool.

I also like the alternative of having a script (you can use Red Gate's tool to create scripts) because that means you can store this info in your source control and use it as part of a deployment package to other servers.

Upvotes: 1

Erick T
Erick T

Reputation: 7439

Joseph,

Do you need to keep just the data in sync, or the schema as well?

One solution to the data question would be SQL Server snapshots. You create a snapshot of your initial configuration, so any changes to the "real" database don't show up in the snapshot. Then, when you need to reset the table, select from the snapshot into a new table. I'm not sure how it will work if the schema changes, but it might be worth a try.

For generation of sample data, the Database project in Visual Studio has functionality that will create fake/random data.

Let me know if this make sense.

Erick

Upvotes: 0

Beth
Beth

Reputation: 9607

You could save them in another database or the same db in different tables distinguished by the name, like employee_test

Upvotes: 0

Related Questions