Has anyone ever download the whole database (or selected table) to a local SQL server? I have a consultant who wants to help with cleaning our database and would like to test and build scripts in a local SQL server, so that the database can be refreshed at will. Looking to see if anyone has taken this route before?
I replied to this yesterday via email but it didn't update. Weird. Here's what I wrote:
Hi Seth,
You can install a fresh, new, empty, database via TIM, and then you can import/export some select (scrubbed) rows to it. I’ve done that and it works okay. There are obviously some object dependencies you’re going to need, but if your consultant can find those he can grab them as well. I believe SSIS will even locate some of those dependencies (I use SQL Redgate tools to identify them, but that’s a paid product).
Another idea: allow the consultant access to the TEST database server via remote desktop connection. An even better idea is to spin up a virtual Terminal Server and put SSMS on that and give the consultant access to connect to the test database server. We keep our consultants in a special ‘consultant’ Active Directory group with limited access. We set their logins to auto expire after 30 days, and then they have to request further access if the project goes longer. We have a paper numbers grid matrix that we can send to them for two factor authentication.
Hope this helps,
Nancy
Nancy-
Thanks . After some internal discussion, we are looking to give the consultant to that is housed here on Virtual Terminal Server.