Automating the Transfer of Dynamics GP Databases – Part 4

In parts 1 through 3 we:

• Created a script to backup all of the Dynamics GP databases.
• Created a script to restore all of the databases.
• Created a script to transfer all of the related SQL logins.

In Part 4 we’re going to tie it altogether.  And to do that we’re going old school.  The batch file.  The following batch file will perform the entire process.

ECHO OFF

ECHO Deleting previous backup files from both source and destination folders
DEL "E:\SQLBackups\*.bak"
DEL "E:\SQLBackups2\*.bak"

ECHO Creating backup script
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -i CreateBackupScript.sql -b -o BackupScript.sql

ECHO Creating restore script
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -i CreateRestoreScript.sql -o RestoreScript.sql

ECHO Creating login transfer script - Using the -d parameter to ensure connecting to the master db
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -d master -i CreateLoginTransferScript.sql -o LoginTransferScript.sql

ECHO Backing up databases
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -i BackupScript.sql -b -o Backup.log

ECHO Copy back files to folder accessible to second SQL server or instance.
XCOPY "E:\SQLBackups\*.bak" "E:\SQLBackups2\"

ECHO Stop and start second SQL instance to ensure that all connections have been cleared out
net stop mssql$SQL2005_2
net start mssql$SQL2005_2

ECHO Restoring databases
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN\SQL2005_2 -E -i RestoreScript.sql -b -o Restore.log

ECHO Transferring logins
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN\SQL2005_2 -E -i LoginTransferScript.sql -b -o LoginTransfer.log

It’s a little difficult to read here (better to cut and paste into a text editor) so here’s what it’s doing:

• Deleting all backup files in both the source and destination folders.  Don’t use folders that are being used by other backup files as they will be deleted as well.
• Creating the backup, restore and login transfer scripts.  Note that all of the creation scripts are run against the source server.
• Running the backup script against the source server.
• Copying all of the backups to the destination server folder.  In a multi-server environment this would presumably be a share on the destination server.
• This procedure will pick up new company databases and SQL logins used by GP but will not remove deleted ones.
• Stopping and starting the destination SQL instance.  This is a quick and dirty way to kill any connections to the databases you’re restoring.
• Restoring the databases to the destination server.
• Running the logins script.

A few things to take note of:

• I’m performing this entire operation on single server with two instances of SQL 2005.  However, I am performing all of the actions necessary to do this between two machines.
• This was written and tested using SQL 2005.  Some of the constructs, particularly common table expressions and the use of SQLCMD.EXE in the batch file won’t work in SQL 2000 and would have to be modified.  My guess is that most of this would work in SQL 2008.
• I’m assuming integrated logins.  In other words, the logged in user’s AD login or service account that’s running the batch file needs to be a member of the sysadmin role on both SQL server instances.
• I came across an error in the restore when there was an additional but empty data file associated with a database.  It wasn’t included in the backup and so errored on the MOVE clause in the restore for that file.

The trickiest part of this was developing the restore script as I needed to take into account all of the possible scenarios of the database files.  Certainly, having the same volume and folders structure on your destination environment would simplify that.

Are There Better Ways of Doing This?

That’s always the question isn’t it.  There are a hundred ways to skin a cat.  Was the 8 hours I spent putting all this together worth it?  I certainly learned a few new things.

There are certainly other ways of doing it.  Other technologies that would be interesting to explore in this regard include:

• SSIS
• .NET or ASP.NET apps
• PowerShell.  I haven’t had the chance to play with PowerShell but it’s becoming the default command line tool for most Microsoft products.

for now I’ll leave that exploration to others.