In Part 1 we wrote a SQL script that will create a backup script for all of the Dynamics GP databases to help us automate the transfer all of Dynamics GP’s databases to another SQL server or instance. In Part 2 we’ll do the same for restoring the databases.
First, let’s review the T-SQL for restoring a database. In this case the DYNAMICS database.
RESTORE DATABASE DYNAMICS
FROM DISK = 'E:\SQLBackups2\DYNAMICSTransfer.bak'
WITH RECOVERY,
MOVE 'GPSDYNAMICSDat.mdf' TO 'E:\SQLData2\GPSDYNAMICSDat.mdf',
MOVE 'GPSDYNAMICSLog.ldf' TO 'E:\SQLData2\GPSDYNAMICSLog.ldf'
Not too bad but there’s a bit of a kink here. The MOVE clauses put the the database files in the desired location on the destination instance. If you're putting the files in the exact same folder structure on the destination as the source then we’re all set and can skip the MOVE clauses and build our RESTORE statements the same way as the backup script as follows:
-- 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:\SQLBackups2\'-- Create statement to backup DYNAMICS database
SELECT
'
RESTORE DATABASE DYNAMICS
FROM DISK = ''' + @BackupFolder + 'DYNAMICSTransfer.bak''
WITH RECOVERY
GO
'UNION ALL
-- Create statements to backup company databases.
SELECT
REPLACE(
'
RESTORE DATABASE {COMPANYDB}
FROM DISK = ''' + @BackupFolder + '{COMPANYDB}Transfer.bak''
WITH RECOVERY
GO
',
'{COMPANYDB}', LTRIM(RTRIM(INTERID)))
FROM DYNAMICS.dbo.SY01500
If, as in many cases, your folder structure is different on the machine you’re transferring to, you need to deal with the MOVE clause. To do this we need to know how many files there are and their logical file names.
If we were dealing strictly with Dynamics GP Utilities created databases with the default files we could easily just hardcode the two MOVE clauses and insert the company ID in the appropriate places. However, if you’ve added files to a database (such as spanning it over multiple volumes), or restored one company database over another (such as restoring a production database over a training one for training purposes), your logical file names will not be standard.
The database file information is most easily extracted from the master.sys.master_files system view in the master database. Once we have the list of files we can build a statement that creates a restore script as follows:
-- 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 and set variables for the restore folders
DECLARE @BackupFolder nvarchar(max);
DECLARE @NewDataFolder nvarchar(max);
DECLARE @NewLogsFolder nvarchar(max);SET @BackupFolder = 'E:\SQLBackups2\';
SET @NewDataFolder = 'E:\SQLData2\';
SET @NewLogsFolder = 'E:\SQLData2\';-- This common table expression returns the list of Dynamics
-- databases, identifying the min and max file IDs.
WITH Databases
AS
(
SELECT
DatabaseID = Databases.database_id
,DatabaseName = Databases.name
,MinFileID = MIN(DatabaseFiles.file_id)
,MaxFileID = MAX(DatabaseFiles.file_id)
FROM master.sys.databases AS Databases
INNER JOIN master.sys.master_files AS DatabaseFiles
ON Databases.database_id = DatabaseFiles.database_id
WHERE Databases.name = 'DYNAMICS'
OR Databases.name IN (
SELECT
INTERID
FROM DYNAMICS.dbo.SY01500)
GROUP BY
Databases.database_id
,Databases.name
),-- This common table expression returns the list of files associated
-- with the Dynamics databases, identifying the first and last files
-- in the list.
DatabaseFiles
AS
(
SELECT
DatabaseName = RTRIM(LTRIM(Databases.DatabaseName))
,LogicalFileName = DatabaseFiles.name
,PhysicalFileName =
CASE
WHEN DatabaseFiles.file_id > 2 THEN
CASE
WHEN type_desc = 'ROWS' THEN 'GPS'
+ RTRIM(LTRIM(Databases.DatabaseName))
+ 'Dat_' + CAST(DatabaseFiles.file_id AS nvarchar(3)) + '.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
ELSE 'GPS' + RTRIM(LTRIM(Databases.DatabaseName)) + 'Log_'
+ CAST(DatabaseFiles.file_id AS nvarchar(3)) + '.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
END
ELSE
CASE
WHEN type_desc = 'ROWS' THEN 'GPS'
+ RTRIM(LTRIM(Databases.DatabaseName)) + 'Dat.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
ELSE 'GPS'
+ RTRIM(LTRIM(Databases.DatabaseName)) + 'Log.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
END
END
,FileType = type_desc
,FileID = DatabaseFiles.file_id
,IsFirstFile =
CASE
WHEN DatabaseFiles.file_id = Databases.MinFileID THEN 1
ELSE 0
END
,IsLastFile =
CASE
WHEN DatabaseFiles.file_id = Databases.MaxFileID THEN 1
ELSE 0
END
FROM Databases
INNER JOIN master.sys.master_files AS DatabaseFiles
ON Databases.DatabaseID = DatabaseFiles.database_id
)-- The SELECT statement outputs the appropriate parts of the
-- RESTORE statement depending on whether this the first, last
-- or a middle file.
SELECT
CASE
WHEN IsFirstFile = 1 THEN
'
RESTORE DATABASE ' + DatabaseName + '
FROM DISK = ''' + @BackupFolder + DatabaseName + 'Transfer.bak''
WITH RECOVERY,
MOVE ''' + LogicalFileName + ''' TO ''' + @NewDataFolder + PhysicalFileName + ''',
'
WHEN IsLastFile = 1 THEN
' MOVE ''' + LogicalFileName + ''' TO ''' + @NewDataFolder + PhysicalFileName + '''
GO
'
ELSE
' MOVE ''' + LogicalFileName + ''' TO ''' + @NewDataFolder + PhysicalFileName + ''',
'
END
FROM DatabaseFiles
ORDER BY
DatabaseName,
FileID
So this is a little involved and is best read by copying into a query window but essentially we’re doing the following:
- The first common table expression, Databases, returns a row for each Dynamics database and includes the minimum and maximum file IDs. This will allow us to know what the first and last files are for a particular database and thus know when to prepend the RESTORE clause at the beginning and the GO statement at the end.
- The second common table expression, DatabaseFiles, returns the list of database files with their logical names and new physical locations. Additionally, it indicates whether a particular file is the first or last one in the list.
- The final SELECT statement actually builds the RESTORE statement using the DatabaseFiles common table expression.
Using my two company test environment, you get the following restore script:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RESTORE DATABASE DYNAMICS
FROM DISK = 'E:\SQLBackups2\DYNAMICSTransfer.bak'
WITH RECOVERY,
MOVE 'GPSDYNAMICSDat.mdf' TO 'E:\SQLData2\GPSDYNAMICSDat.mdf',
MOVE 'GPSDYNAMICSLog.ldf' TO 'E:\SQLData2\GPSDYNAMICSLog.ldf'
GORESTORE DATABASE FOUR
FROM DISK = 'E:\SQLBackups2\FOURTransfer.bak'
WITH RECOVERY,
MOVE 'GPSFOURDat.mdf' TO 'E:\SQLData2\GPSFOURDat.mdf',
MOVE 'GPSFOURLog.ldf' TO 'E:\SQLData2\GPSFOURLog.ldf',
MOVE 'FOURDat_2' TO 'E:\SQLData2\GPSFOURDat_3.ndf'
GORESTORE DATABASE TWO
FROM DISK = 'E:\SQLBackups2\TWOTransfer.bak'
WITH RECOVERY,
MOVE 'GPSTWODat.mdf' TO 'E:\SQLData2\GPSTWODat.mdf',
MOVE 'GPSTWOLog.ldf' TO 'E:\SQLData2\GPSTWOLog.ldf'
GO
Note that the FOUR company database has had it’s additional data file properly listed in the MOVE clauses.
If we now run this script against our second server (or server instance), we’ll restore all of the databases we backed up previously.
In our Part 3 we’ll look at how to reattach logins to database users.
Posts
