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?
Are you on RAMP or locally hosted?
RAMP. The consultant will work in a completely separate SQL environment.
In TIM, you can create a new instance of impresario. This is an excellent way to do things that are public training facing, but it takes a lot of work to structurally set-up.
Thanks, Troy.
I don't need a public facing as this is more about building scripts. I want to try to keep it simple as well if possible.
Is is possible to do a back up of the database in SQL and then restore it on our local and separate SSMS?
If you look up Live-to-Test copy scripts you should find most of what you want.
Yes, we've done this before as well. Just create a new database from one of your impresario back-ups. I would loop in your IT department prior to dropping your entire database onto a locally hosted computer (unless you are that person). They may have concerns with the security of that machine.
Yep, what Emily says is spot on. If it's a laptop destination, there's a slew of HIPPA and security to untangle.
I would suggest doing this in your own environment and then providing the consultants with a VPN. We’ve done this in the past when working with Tessitura Consultants (we’re self-hosted).
Hi Seth,
I would be happy to look into this with you. Would you like me to create a TASK ticket on your behalf?
I replied to this yesterday via email but it didn't update. Weird. Here's what I wrote:
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.
Yes, that would be great as some other related questions have come up.