November 30, 2005 at 3:02 pm
Hi,
I'm trying to automate some tests, and I'd like to write some scripts to put my database in a certain state. After the tests are run, then I would like to restore the database back to the previous state. [Actually the tests will modify two different databases; one will modify ~10 tables, the other about ~30 tables] We are using SQL Server 2000. I've found some GUI wizards to allow the exporting and importing of data, but I'm not sure how to accomplish those things from a script (or command line). The databases are very, very large, so I'm not inclined to simply make a backup of them. I'm not much of a database person, so if anyone has any suggestions of a better way to accomplish this same thing, please feel free to suggest them.
Here's what I'd like to happen:
1) Snapshot existing data; ie. export all data from a list of defined tables
2) Delete all data in those tables
3) Run tests; verify data
4) Restore data (import data) from step 1
5) Database is now in exact state as before running tests.
Does this make sense?
Thanks for any ideas or suggestions,
Beth
November 30, 2005 at 3:58 pm
November 30, 2005 at 4:17 pm
I don't know if test is really the correct word. Anyway, the application will run, which basically takes in a set of files & processes them. By "process", I mean the files are parsed, the data is manipulated a bit, and finally many, many records are inserted into the database.
The ultimate in verification would be to confirm that all the data is correct in the database. That is a big job, and probably not something to tackle right now. For a first step, probably just counting the number of records in the database will be good enough.
December 1, 2005 at 1:38 am
1) Snapshot existing data; ie. export all data from a list of defined tables
Perform a backup of the database
See: http://www.sqlservercentral.com/scripts/contributions/1419.asp
2) Delete all data in those tables
DELETE or TRUNCATE all tables
See: http://www.sqlservercentral.com/scripts/contributions/964.asp
...
4) Restore data (import data) from step 1
Drop database and restore from backup
See: http://www.sqlservercentral.com/scripts/contributions/764.asp
Andy
December 1, 2005 at 9:08 am
Thanks for the input, but I'd rather not backup the entire database unless that is the only solution. I'd prefer to export the data from the few tables I care about. Is there a way to export data from a script?
Thanks,
Beth
December 1, 2005 at 9:25 am
You could script the following:
1. sp_rename tables to new table names (backup_tablename)
2. drop constraints from backup tables
3. re-create original tables with constraints
4. run import/verify
5. drop original tables
6. add constraints to backup tables
7. sp_rename backup tables back to original names.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply