Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Copy Dynamics database...
Finance | Project Operations, Human Resources, ...
Answered

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

(0) ShareShare
ReportReport
Posted on by Steve Kondolf 40

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
  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Copy Dynamics database from production to an existing test install on separate server

    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.

  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,917 Most Valuable Professional on at
    RE: Copy Dynamics database from production to an existing test install on separate server

    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

  • Steve Kondolf Profile Picture
    Steve Kondolf 40 on at
    RE: Copy Dynamics database from production to an existing test install on separate server

    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!

  • Verified answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,917 Most Valuable Professional on at
    RE: Copy Dynamics database from production to an existing test install on separate server

    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

  • Verified answer
    SMike Giuffre Profile Picture
    SMike Giuffre 275 on at
    RE: Copy Dynamics database from production to an existing test install on separate server

    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.

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Copy Dynamics database from production to an existing test install on separate server

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans