Best Practice For Moving Large Number of GP Company Databases To New Server

Hello,

We a client with GP 2010/SQL 2005.  Their server is failing and we will be moving them to new hardware and upgrading to SQL 2008 R2.  They currently have 75 companies in their GP 2010 environment. 

Is there a recommended method (or SQL script) that will expedite the process of moving so many databases?  Following MIcrosoft’s standard moving GP to a new server documentation and simply restoring each database will take a considerable amount of time. 

Any suggestions on a way to speed up this process would be much appreciated.

Thank You.

  • John Lowther
    Posted by John Lowther

    Verified Answer
    Verified by Dynamics83

    As long a the Drive and Paths for the separate MDF and LDF files are the same on both servers, as in “D:\Data\Apps\Database.mdf” and “E:\LogFiles\Database.ldf” then this is what I would do.

    Please note that this is just my personal opinion, but it have never failed me so far.

    Step 1) (This script runs on your current server)

    Using the following snippet as a template create a sql script that will backup all of your Dynamics GP databases.

    BACKUP DATABASE [FirstDatabase] TO DISK = ‘\\UNC Path\Folder name\FirstDatabase.bak’ WITH INIT, COPY_ONLY

    Cut and past the template above as many times as you have Dynamics databases. Note: do not forget about the Dynamics System database usally named Dynamics or the sample test company usally named TWO. Note that the backup location is UNC network share location that both servers can access. The reason I do that is so that I do not have to copy the backup files from one server to another.

    You should end up with some that looks somewhat like

    BACKUP DATABASE [Dynamics] TO DISK = ‘\\ServerName\SQLBackups\Dynamics.bak’ WITH INIT, COPY_ONLY
    BACKUP DATABASE [TWO] TO DISK = ‘\\ServerName\SQLBackups\TWO.bak’  WITH INIT, COPY_ONLY
    BACKUP DATABASE [COMPANY1] TO DISK = ‘\\ServerName\SQLBackups\COMPANY1.bak’ WITH INIT, COPY_ONLY

    <<Notice how you are really only changing each line only in two places>>

    Once you have this script you can run the script on your current server and it will make a backup of every one of you databases while you are working on the next piece.

    Step 2) (This script runs on you new server)

     

    Using the following snippet as a template create a sql script that will restore all of your Dynamics GP databases on the new server.

     

     

    RESTORE DATABASE [FirstDatabase] FROM DISK = ‘\\UNC Path\Folder name\FirstDatabase.bak’

     

    Cut and past the template above as many times as you have Dynamics databases. Note: do not forget about the Dynamics System database usally named Dynamics or the sample test company usally named TWO. Note that the backup location is UNC network share location that both servers can access. The reason I do that is so that I do not have to copy the backup files from one server to another. This script should have a line for each of the line in the first script.

    You should end up with some that looks somewhat like

    RESTORE DATABASE [Dynamics] FROM DISK = ‘\\ServerName\SQLBackups\Dynamics.bak’
    RESTORE DATABASE [TWO] FROM DISK = ‘\\ServerName\SQLBackups\TWO.bak’
    RESTORE DATABASE [COMPANY1] FROM DISK = ‘\\ServerName\SQLBackups\COMPANY1.bak’

     

    <<Again notice how you are really only changing each line only in two places>>

    At this point check back on the executation of the first script on the current server. Depending on how many companies you have, how fast you can cut-n-paste, the script may or may not still be executating. If it is still running then it is “Time for you to take a break, without your boss knowing of course, you are so hard at work having to backup all those databases” 🙂

    Once the first script finishes successfully on your current server, then you are ready to execute the second script on your new server and it will restore every one of you databases while you get back to taking that break that your boss does not know your taking. 😉

    When the second script finishes you should end up with all of your databases on your new server.

    Please note however, that just copying the databases from one server to another does not mean that you are completly done.

    You still have to copy over the SQL Logins, and change all the ODBC connection on all the client machines to use the new server, and as the servername as changed you will also need to reset all the passwords. But there are lots of blog post, forum post, white papers, etc. so I will leave that out of this reply. But get you started you can check out this blog post on Moving Dynamics – GP Databases. Please note that it is has lots of links to other post as well.

    Let me know how it goes,

     

     

  • There is no magic here. But here is what you can try.

    1) Install GP 2010 onto the new server with the same service pack as the old. Just install the Dynamics data. This will get some basic SQL/GP items in place. Then delete this DYNAMICS database from SQL.

    2) Run the capture login script on the old server and create script to create logins on new server.

    3) Detach the DYNAMICS and all company databases on old server and copy to new server.

    4) Attach all databases on new server

    5) Run the login script to create the users.

    6) You will need to login to GP on the new server and set users password to PASSWORD99 and then set each account to change password as they login.

    7) Copy over your shared reports,forms, letters and notes.

    That should be enough. If you have SQL jobs you can copy them over as well or simply recreate them.

    Richard E. Wheeler 2013 MVP

    MS Dynamics GP Support

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • Richard is right, there is no true magic here.

    However, there may be a way for you to move all the databases while you are drinking coffee with you feet propped up, just don’t let your boss catch you 🙂

    But before I advise on that part I need to know something.

    Are all the drive letters for the two servers the same for the location of the Dynamics and Companies Database files?

    Exampe:

    Drive D – Data (location of the Dynamics and companies MDF Files)

    Drive L – Log (location of the Dynamics and companies LDF Files)

  • Jon,

    They will be moving to a new virtual server, which has not been provisioned yet.  We would have the ability to make the drive letters and folder structures the same as the current server if that would allow us to expedite the process.

    Currently:

    C: for OS

    D: Data/Apps

    E: Log files.

  • John Lowther
    Posted by John Lowther

    Verified Answer
    Verified by Dynamics83

    As long a the Drive and Paths for the separate MDF and LDF files are the same on both servers, as in “D:\Data\Apps\Database.mdf” and “E:\LogFiles\Database.ldf” then this is what I would do.

    Please note that this is just my personal opinion, but it have never failed me so far.

    Step 1) (This script runs on your current server)

    Using the following snippet as a template create a sql script that will backup all of your Dynamics GP databases.

    BACKUP DATABASE [FirstDatabase] TO DISK = ‘\\UNC Path\Folder name\FirstDatabase.bak’ WITH INIT, COPY_ONLY

    Cut and past the template above as many times as you have Dynamics databases. Note: do not forget about the Dynamics System database usally named Dynamics or the sample test company usally named TWO. Note that the backup location is UNC network share location that both servers can access. The reason I do that is so that I do not have to copy the backup files from one server to another.

    You should end up with some that looks somewhat like

    BACKUP DATABASE [Dynamics] TO DISK = ‘\\ServerName\SQLBackups\Dynamics.bak’ WITH INIT, COPY_ONLY
    BACKUP DATABASE [TWO] TO DISK = ‘\\ServerName\SQLBackups\TWO.bak’  WITH INIT, COPY_ONLY
    BACKUP DATABASE [COMPANY1] TO DISK = ‘\\ServerName\SQLBackups\COMPANY1.bak’ WITH INIT, COPY_ONLY

    <<Notice how you are really only changing each line only in two places>>

    Once you have this script you can run the script on your current server and it will make a backup of every one of you databases while you are working on the next piece.

    Step 2) (This script runs on you new server)

     

    Using the following snippet as a template create a sql script that will restore all of your Dynamics GP databases on the new server.

     

     

    RESTORE DATABASE [FirstDatabase] FROM DISK = ‘\\UNC Path\Folder name\FirstDatabase.bak’

     

    Cut and past the template above as many times as you have Dynamics databases. Note: do not forget about the Dynamics System database usally named Dynamics or the sample test company usally named TWO. Note that the backup location is UNC network share location that both servers can access. The reason I do that is so that I do not have to copy the backup files from one server to another. This script should have a line for each of the line in the first script.

    You should end up with some that looks somewhat like

    RESTORE DATABASE [Dynamics] FROM DISK = ‘\\ServerName\SQLBackups\Dynamics.bak’
    RESTORE DATABASE [TWO] FROM DISK = ‘\\ServerName\SQLBackups\TWO.bak’
    RESTORE DATABASE [COMPANY1] FROM DISK = ‘\\ServerName\SQLBackups\COMPANY1.bak’

     

    <<Again notice how you are really only changing each line only in two places>>

    At this point check back on the executation of the first script on the current server. Depending on how many companies you have, how fast you can cut-n-paste, the script may or may not still be executating. If it is still running then it is “Time for you to take a break, without your boss knowing of course, you are so hard at work having to backup all those databases” 🙂

    Once the first script finishes successfully on your current server, then you are ready to execute the second script on your new server and it will restore every one of you databases while you get back to taking that break that your boss does not know your taking. 😉

    When the second script finishes you should end up with all of your databases on your new server.

    Please note however, that just copying the databases from one server to another does not mean that you are completly done.

    You still have to copy over the SQL Logins, and change all the ODBC connection on all the client machines to use the new server, and as the servername as changed you will also need to reset all the passwords. But there are lots of blog post, forum post, white papers, etc. so I will leave that out of this reply. But get you started you can check out this blog post on Moving Dynamics – GP Databases. Please note that it is has lots of links to other post as well.

    Let me know how it goes,

     

     

  • Have been successful or is this a plea for help?

    Richard E. Wheeler 2013 MVP

    MS Dynamics GP Support

    www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

  • Hi Richard,

    Sorry – this was not a plea for help. We have been having issues accessing and posting on OTC for the last couple of days.  I was testing to see if our issues had been resolved.

    The posts above helped with the server move. Thank you for your responses.

About nijamkhan

I am simple Hardworking,and i would like improve the Knowledge time by time
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s