Automating the Transfer of Dynamics GP Databases – Part 1

Once upon a time I wrote a blog entry on how to write a script that writes a script that runs against every Dynamics GP company database.  Now let’s do something useful with it.

More and more of our clients are having to deal with the alphabet soup regulatory compliance such as SOX (Sarbanes Oxley), PCI DSS, HIPPA etc. or simply deciding to implement good IT change control policies.  Often this means that before deploying that fancy new eConnect integration to production, we need to test it in a separate test environment.

No big deal.  Build out a new server, install SQL, GP, copy over the databases…How many companies did you say you had?  200…huh…Better cancel my dinner plans.

Best of all, you really need to keep that environment refreshed on a pretty regular basis for support purposes.  So how do you transfer 200 company databases (and don’t forget they lowly DYNAMICS database) and still manage to make it home for The Daily Show?

In this four part entry we’ll figure out how to:

• Backup all of the Dynamics GP databases.
• Restore all of the databases.
• Transfer all of the logins.
• Pull all of the tasks together.

Let’s start with backing up.  Time to dig up the dusty Books Online and find out how to backup a database in the query window.  No double clicking here.  Here’s how we’d backup the DYNAMICS database:

BACKUP DATABASE DYNAMICS
    TO DISK = 'E:\SQLBackups\DYNAMICSTransfer.bak'
            WITH FORMAT,
        MEDIANAME = 'DYNAMICSBackup',
        NAME = 'Full Backup of DYNAMICS for Transfer'

So, let’s use our handy dandy code to blow that out so that we build a script that backs up the DYNAMICS database and then each of the company databases.

--  Setting NOCOUNT on prevents the row count message from appearing at the bottom.
--  Note that the SQL statements do not include column headers.  This makes
--  the entire output of the results window executable.

SET NOCOUNT ON

DECLARE @BackupFolder nvarchar(max)
SET @BackupFolder = 'E:\SQLBackups\'

--  Create statement to backup DYNAMICS database
SELECT
        '
        BACKUP DATABASE DYNAMICS
            TO DISK = ''' + @BackupFolder + 'DYNAMICSTransfer.bak''
                    WITH FORMAT,
                MEDIANAME = ''DYNAMICSBackup'',
                NAME = ''Full Backup of DYNAMICS for Transfer''

        GO
        '

UNION ALL

--  Create statements to backup company databases.
SELECT
        REPLACE(
        '
        BACKUP DATABASE {COMPANYDB}
            TO DISK = ''' + @BackupFolder + '{COMPANYDB}Transfer.bak''
                    WITH FORMAT,
                MEDIANAME = ''{COMPANYDB}Backup'',
                NAME = ''Full Backup of {COMPANYDB} for Transfer''

        GO
        ',
        '{COMPANYDB}', LTRIM(RTRIM(INTERID)))
    FROM DYNAMICS.dbo.SY01500

This should produce the following backup script that will backup all of the Dynamics databases:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        BACKUP DATABASE DYNAMICS
            TO DISK = 'E:\SQLBackups\DYNAMICSTransfer.bak'
                    WITH FORMAT,
                MEDIANAME = 'DYNAMICSBackup',
                NAME = 'Full Backup of DYNAMICS for Transfer'

        GO

        BACKUP DATABASE FOUR
            TO DISK = 'E:\SQLBackups\FOURTransfer.bak'
                    WITH FORMAT,
                MEDIANAME = 'FOURBackup',
                NAME = 'Full Backup of FOUR for Transfer'

        GO

        BACKUP DATABASE TWO
            TO DISK = 'E:\SQLBackups\TWOTransfer.bak'
                    WITH FORMAT,
                MEDIANAME = 'TWOBackup',
                NAME = 'Full Backup of TWO for Transfer'

        GO

At this point we’re all set to backup the databases.  In Part 2 we’ll create a script that restores them.