Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-24 : 08:30:46
|
| Neet writes "Hi,I am currently working on a Sql Server database. At the time of deployment, i want to be able to clean up the database, since it would contain a lot of test data. This would be similar to having the same table structure in the database but the tables would contain no data.I also dont want blank tables in the entire database. I want certain tables to maintain their data. For example if i have a table containing all the States in USA, i dont want to have to repopulate it all the time. But other tables like Users should be empty. Hence i basically need a way to reset the database to a state i can specify using sql scripts, so that everytime i do tests and insert junk data i can clean the tables.Could anyone give me an idea as to how this can be done?Thankyou,Neet" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-24 : 08:33:14
|
| Probably the easiest way to do this is to set up your database exactly how you want it to be, with all the tables, procedures, and data you want, then do a full database backup. Keep this backup file handy, and restore it to a new database whenever you need a new one. You would need to use the WITH MOVE option of the RESTORE command to change the physical file names during the restore. See Books Online under "RESTORE" for more information. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-24 : 12:28:30
|
| You could also create the base schema by generating the script from your database. Then you could run bcp statements for the tables that you want data in them. This is just another option, but Rob's option would be fastest. You might consider doing detach/attach method for very quick "restores".Tara |
|
|
|
|
|
|
|
|
Sql Server Reset Database