We refresh our testing databases periodically with no problems. It is a logical protocol to make sure to alert all users ahead of time so that they may backup any needed custom data that they have entered themselves; however, when I did a data refresh this week something went awry and before I knew it there was a line to my desk, the phone was ringing, and email started piling up.
Fortunately, we did have a backup copy of this database so I was able to query and recover this data. However, I needed a quick and simple way to have the users re-create this data without my involvement. I could set up some type of job to restore the data after each refresh , but the data would only need to exist for a few months and I did not have time to go through the red tape that would be required for this. Given this particular scenario, it was decided to create a SQL script that would do INSERTS for the data.
Creating an INSERT script should not be difficult but it was too timely. Exporting and Importing the data was not ideal. I decided to investigate tools that would create the script for me. And there it was — on my machine all along — the SQL Server Publishing Wizard. I had forgotten all about it. I had just needed to locate it since there was no icon or link; therefore, the entire purpose of this way too-long-winded post :
C:Program FilesMicrosoft SQL Server90ToolsPublishing1.4SqlPubWiz.exe
Yay, now I have a reference and reminder!
Anyway, it is worth mentioning that this tool is way inappropriate for large data. The data needed was a very small portion compared to the millions of rows that exists. I had to query the needed data to insert into a temp table and this made the wizard fast. Ideally, it would have been better to have the ability to query a table for data from within the wizard but I’m sure it is just a matter of time.