Copy Dynamics database from production to an existing test install on separate server

(0) Share

Report

Posted on by Steve Kondolf

Hello -

We have 2 installations of GP, our production installation and a test install on a separate server.

We recently finished upgrading our GP installations to 18.3 and I now want to simply copy our Dynamics database from our production install to test via a backup/restore. I tried this about a year ago and ran into issues with security and permissions so I scrapped the attempt.

Doing some googling I have found various documents and blog entries talking about doing this but they all seem to be for moving GP to a "CLEAN" server where GP is currently not installed. I want to refresh the DYNAMICS database over an existing DYNAMICS db on our test server.

I've found there are some scripts one being sp_help_revlogin that will generate a list of logins from our production box but all that appears to do is create missing logins on our text box. I've also read that I might have to re-create the user passwords on the test box after I copy DYNAMICS over. We do have a licensed copy of GPPT installed however I've never used that particular utility to re-do the passwords.

Can someone provide the steps I need to follow to perform this copy of DYNAMICS from production to our test installation? From everything I've read it sounds like the only thing I didn't do was reset the passwords after copying DYNAMICS over but I want to confirm that. And if anyone has experience with GPPT and resetting passwords I'd appreciate any tips/suggestions.

And I apologize if this has been asked in the past - I've tried searching but again, it always seems to be questions related to setting up a NEW install of GP on a test server, not refreshing an existing install.

Thanks in advance.

Steve

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (6)

Answers (3)

Verified answer

Richard Wheeler 75,848 Moderator on at

Like (1)

Report

This link outlines the entire process.

support.microsoft.com/.../how-to-transfer-an-existing-microsoft-dynamics-gp-microsoft-small-business-financials-or-microsoft-small-business-manager-installation-to-a-new-server-that-is-running-microsoft-sql-server-b430445b-ec78-ddab-0aaa-417b5d6622db

Was this reply helpful? Yes No
Verified answer

SMike Giuffre 281 on at

Like (0)

Report

As long as your users are syncronized you should be ok. I do this often.

I do have to issue ALTER USER <user> WITH LOGIN=<user> commands in SQL to enable the users in the GP test databases. Also you can add <TEST> at the end of your company names to make it clear you are in the test environment.

Was this reply helpful? Yes No
Verified answer

David Musgrave MVP ... 14,095 Most Valuable Professional on at

Like (0)

Report

Don't forget to use GP Power Tools database validation to recreate all the SQL Logins and Database Users.

Much simpler and faster than other methods.

David

Was this reply helpful? Yes No
Steve Kondolf 40 on at

Like (0)

Report

Thanks to everyone for replying. I think my issue is that even though I've seen the documentation as provided by Richard above I always read that as if I was creating an environment from scratch - I never put 2 and 2 together to realize I have to recreate the sql logins after copying the database to the new server. Since all the answers here have basically said the same thing I've set all of them as accepted answers.

Since I do have GP Power Tools I will use it to deal with the Logins issue.

David - do you have any tips/tricks for using the Validation Tool to perform this? I've read the GPPT User Manual but if there's any other insights you could provide it would be very helpful. On your website I checked out the videos but it appears there isn't one yet available for Database Tools so if you can provide any more info that would be very helpful.

Thanks again to everyone!

Was this reply helpful? Yes No
Suggested answer

David Musgrave MVP ... 14,095 Most Valuable Professional on at

Like (1)

Report

Hi Steve Kondolf

On the GP Power Tools Portal (http://winthropdc.com/GPPT) there is a videos page (http://winthropdc.com/GPPT/Videos).

On the videos page the first few videos are full product demonstrations.

The second video covers Database Validation at the 56:00 mark. This is probably the best one.

Also, the first video has Database Validation at 36:30 mark.

Finally, the 592 page manual covers Database Validation starting at page 289 (pdf page 299). Starting on Page 302 (pdf page 312) are step by step instructions for using Database Validation.

Please make sure you are using the latest released build 28.9 code for the best functionality. I know that build 28.7or later includes fixes for Database Validation.

Regards

David

Was this reply helpful? Yes No
Suggested answer

Derek Albaugh on at

Like (1)

Report

To add what everyone has already mentioned:

KB 878449 - How to transfer an existing Microsoft Dynamics GP, Microsoft Small Business Financials, or Microsoft Small Business Manager installation to a new server that is running Microsoft SQL Server

support.microsoft.com/.../how-to-transfer-an-existing-microsoft-dynamics-gp-microsoft-small-busi

**NOTE: The CaptureLogins script in step #1 of KB 878449 hasn’t been updated to work with newer releases of SQL Server, so, as you mentioned you're on SQL Server 2019 currently, you'll need to use this article to migrate the login information for GP users over to the new SQL Server:

KB 918992 - How to transfer logins and passwords between instances of SQL Server

support.microsoft.com/.../how-to-transfer-logins-and-passwords-between-instances-of-sql-server

The KB 878449 article describes how to transfer an existing Microsoft Dynamics GP installation to a new server that is running Microsoft SQL Server. The article also describes how to maintain the user logins and databases.

Was this reply helpful? Yes No