If you've ever done a server transfer for Microsoft Dynamics GP (Great Plains) you'll know how much effort it is just to move the databases to the new server and run the SQL scripts against them as described in the KB article (878449): https://mbs.microsoft.com/knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmyqlytnsukzpxkmunvxxrmsqnmtquptkvo
We have a company with 80 company databases that we needed to copy into a test environment at our office to set up a complex report. There was no way I was going to do all those steps manually. Thus, the creation of a couple of handy SQL scripts to do the job for me.
The backup script - nothing spectacular here - it just sticks on our common file suffix.
/*
This script backs up all GP databases to a local or network location
and appends a specific file suffix.
If you get an error when running this script and there
are no typeos in the path, make sure your SQL Server
service logon user has appropriate rights to the path.
It does not use your login context to create the backups.
Change the @ variables to fit your needs
***************************
JP Davey
2009
***************************
*/
declare @DatabaseId varchar(100)
declare @SQL varchar(400)
declare @BackupFolder varchar(256)
declare @FileSuffix varchar(256)
declare @GPDatabase tinyint
declare @BackUpGPOnly char(1)
-- Only Back Up Dynamics GP databases: Y = Yes, N = No
-- This script does not restore the system databases (master, msdb, tempdb, model)
-- regardless of which option you select
set @BackUpGPOnly = 'Y'
-- The network (UNC) path where the databases will be backed up TO
set @BackupFolder = 'G:\Transfer\' -- must end with a \
-- The suffix after the database name in the backup filename
-- For example: a FileSuffix of '-ServerTransfer' would give a filename of
-- DYNAMICS-ServerTransfer.bak
set @FileSuffix = '-ServerTransfer'
declare DatabaseLoop cursor for
SELECT
RTRIM(LTRIM(name)) as name,
(CASE WHEN name IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
THEN 1
ELSE 0 END) as GPDB
FROM master..sysdatabases
WHERE name not in ('master','model','msdb','tempdb')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId, @GPDatabase
while (@@fetch_status <> -1)
begin
/*Inner Loop for each Database*/
IF @GPDatabase = 1 OR @BackUpGPOnly = 'N'
BEGIN
RAISERROR (@DatabaseId, 0, 1) WITH NOWAIT;
SET @SQL = 'BACKUP DATABASE [' + @DatabaseId + '] TO DISK = N''' + @BackupFolder
-- ** if you want to create subfolders for each company, you will need to uncomment the following line
-- ** keep in mind that you will have to manually create the folders if they do not already exist
-- + @DatabaseId + '\'
+ @DatabaseId + @FileSuffix
+ '.bak'' WITH NOFORMAT, INIT, NAME = N'''
+ @DatabaseId + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
print @SQL
EXEC(@SQL)
print CHAR(13)+CHAR(10)+'----------------'+CHAR(13)+CHAR(10)
END
fetch next from DatabaseLoop into @DatabaseId, @GPDatabase
end
close DatabaseLoop
deallocate DatabaseLoop
The restore script for restoring the databases and running all the GP scripts to the distination server
/*
This script is compatible with SQL 2005 and 2008
This script creates the script you should open and run on the
new/test server to restore and prepare the databases.
The output from this script is a script
to perform the following tasks:
Restore the databases from the @BackupFolder location
Run the Dex_Req.sql script on the master database
Run the Grant.sql script on all GP databases
Run the sp_changedbowner 'DYNSA' on all GP databases
Run the MassDropUserSchemas.sql script on all GP databases
After running this script on the existing SQL server,
copy/paste the output to the new SQL server and run it there.
Don't include the first line "(xx row(s) affected)".
If you are referencing the backup files from a network share
(UNC path) make sure your SQL Server service logon user has
appropriate rights to the network path.
If it fails on a couple lines, correct the output script on the new
server, commenting out the lines you don't want, and Execute the
script again. Because there are variables declared, you might not be
able to just select a few lines and Execute.
If it fails miserably, you probably don't have your files in
the correct path. Fix the path in this script, Execute, and copy
the script to the new server again.
Change the @ variables to fit your needs
***************************
JP Davey -- Using modified GP scripts
2009
***************************
*/
declare
@BackupFolder varchar(256),
@RestoreDataFileFolder varchar(256),
@RestoreLogFileFolder varchar(256),
@FileSuffix varchar(256),
@DatabaseId varchar(100),
@MDFName varchar(256),
@LDFName varchar(256),
@InDynamics tinyint,
@RestoreGPOnly char(1)
-- The local or network (UNC) path where the databases will be restored FROM
set @BackupFolder = 'D:\Transfer\' -- must end with a \
-- The local path where the Data (.mdf) files will be restored TO
set @RestoreDataFileFolder = 'C:\SQLData\Data\' -- must end with a \
-- The local path where the Log (.ldf) files will be restored TO
-- This may or may not be the same as the @RestoreDataFileFolder
set @RestoreLogFileFolder = 'C:\SQLData\Logs\' -- must end with a \
-- The suffix after the database name in the backup filename
-- For example: a FileSuffix of '-ServerTransfer' would give a filename of
-- DYNAMICS-ServerTransfer.bak
set @FileSuffix = '-ServerTransfer'
-- Restore Dynamics GP databases only: Y = Yes, N = No
-- This script does not restore the system databases (master, msdb, tempdb, model)
-- regardless of which option you select
set @RestoreGPOnly = 'Y'
-------------------------------------------------------------------------------
---------- Get a list of database files ---------------------------------------
-------------------------------------------------------------------------------
CREATE TABLE #GenScript_DBInfo
( DatabaseName VARCHAR(100),
LogicalFileName varchar(100),
LFType smallint)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
' + '''' + '?' + '''' + ' AS DatabaseName,
rtrim(sysfiles.name) AS LogicalFileName,
sysfiles.groupid as LFType
FROM dbo.sysfiles'
INSERT INTO #GenScript_DBInfo
(DatabaseName,
LogicalFileName,
LFType)
EXEC sp_MSForEachDB @command
-------------------------------------------------------------------------------
---------- Restore the databases ----------------------------------------------
-------------------------------------------------------------------------------
print ''
print '-- Restore Databases --'
print ''
declare DatabaseLoop cursor for
(SELECT
DB.DatabaseName as DB,
MFile.LogicalFileName as MDFName,
LFile.LogicalFileName as LDFName,
(CASE WHEN DB.DatabaseName IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
THEN 1
ELSE 0
END) as GPDB
FROM
#GenScript_DBInfo DB
LEFT JOIN
#GenScript_DBInfo MFile
ON DB.DatabaseName = MFile.DatabaseName
AND MFile.LFType = 1
LEFT JOIN
#GenScript_DBInfo LFile
ON DB.DatabaseName = LFile.DatabaseName
AND LFile.LFType = 0
WHERE
DB.DatabaseName NOT IN ('master','model','tempdb','msdb')
AND (@RestoreGPOnly = 'N'
OR (@RestoreGPOnly = 'Y' AND DB.DatabaseName IN (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')))
GROUP BY
DB.DatabaseName,
MFile.LogicalFileName,
LFile.LogicalFileName
)
declare
@Prefix varchar(3),
@SuffixDat varchar(3),
@SuffixLog varchar(3)
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId, @MDFName, @LDFName, @InDynamics
while (@@fetch_status <> -1)
begin
if @InDynamics = 1
begin
set @Prefix = 'GPS'
set @SuffixDat = 'Dat'
set @SuffixLog = 'Log'
end
else
begin
set @Prefix = ''
set @SuffixDat = ''
set @SuffixLog = ''
end
print 'RESTORE DATABASE [' + @DatabaseId + '] FROM DISK = N''' + @BackupFolder
-- ** if you created subfolders for each company, you will need to uncomment the following line
-- + @DatabaseId + '\'
+ @DatabaseId + @FileSuffix
+ '.bak'' WITH FILE = 1, MOVE N'''
+ @MDFName + ''' TO N''' + @RestoreDataFileFolder
+ @Prefix + @DatabaseId + @SuffixDat + '.mdf'', MOVE N'''
+ @LDFName + ''' TO N''' + @RestoreLogFileFolder
+ @Prefix + @DatabaseId + @SuffixLog + '.ldf'', KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 10'
fetch next from DatabaseLoop into @DatabaseId, @MDFName, @LDFName, @InDynamics
end
close DatabaseLoop
deallocate DatabaseLoop
DROP TABLE #GenScript_DBInfo
-------------------------------------------------------------------------------
---------- Grab the logins and passwords --------------------------------------
-------------------------------------------------------------------------------
PRINT ''
PRINT '-- Add Users --'
PRINT ''
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (256) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256) IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seeMigrateSQLLogins
GO
create procedure seeMigrateSQLLogins @login_name sysname = NULL
as
declare
@name char(50),
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@Is_Policy bit,
@Is_Exp bit,
@type char(1),
@Pol char(3),
@Exp char(3)
set nocount on
create table #logins (
[name] nvarchar(128) NOT NULL,
[sid] varbinary(85) NOT NULL,
[type] char(1) NOT NULL,
[is_policy_checked] bit default 0,
[is_expiration_checked] bit default 0,
[password_hash] varbinary(256) )
insert #logins (name, sid, type)
select name, sid, type from sys.server_principals where
(type_desc = 'SQL_LOGIN' or type_desc = 'WINDOWS_LOGIN') and name <> 'sa' and name <> 'NT AUTHORITY\SYSTEM'
update a set a.is_policy_checked = b.is_policy_checked, a.is_expiration_checked = b.is_expiration_checked, a.password_hash = b.password_hash
from #logins a, sys.sql_Logins b
where a.sid = b.sid
set nocount off
IF (@login_name IS NULL) --Not a single user, get the list
DECLARE seelogin_curs CURSOR FOR
SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
WHERE name <> 'sa'
ELSE
DECLARE seelogin_curs CURSOR FOR
SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
WHERE name = @login_name
OPEN seelogin_curs
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
END
SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF @type = 'S'
BEGIN
PRINT '/* SQL Login ******************/'
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF @Is_Policy = 1 Begin set @Pol = 'ON' End ELSE Begin set @Pol = 'OFF' END
IF @Is_Exp = 1 Begin set @Exp = 'ON' End ELSE Begin set @Exp = 'OFF' END
SET @tmpstr = 'Create Login [' + rtrim(@name) + '] WITH PASSWORD = ' + @txtpwd + ' hashed, sid = ' + @SID_string + ', CHECK_POLICY = ' + @Pol + ', CHECK_EXPIRATION = ' + @Exp
PRINT @tmpstr
PRINT ''
END
Else
BEGIN
PRINT '/* SQL Login ******************/'
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
SET @tmpstr = 'Create Login [' + rtrim(@name) + '] FROM WINDOWS; '
PRINT @tmpstr
PRINT ''
END
FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
END
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
drop table #logins
GO
declare @version2005 char(5)
declare @version2008 char(5)
--Get the current version of SQL Server running
select @version2005 = substring(@@version,29,4)
select @version2008 = substring(@@version,35,4)
if @version2005 = '9.00'
Begin
exec seeMigrateSQLLogins
End
Else if @version2008 = '10.0'
Begin
exec seeMigrateSQLLogins
End
Else
begin
exec sp_help_revlogin
End
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seeMigrateSQLLogins
GO
-------------------------------------------------------------------------------
---------- Dex_Req.sql --------------------------------------------------------
-------------------------------------------------------------------------------
PRINT ''
PRINT '-- Dex_Req.sql --'
PRINT ''
print 'use tempdb'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.DEX_LOCK'') and type = ''U'')'
print 'begin'
print ' drop table DEX_LOCK'
print 'end'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.DEX_SESSION'') and type = ''U'')'
print 'begin'
print ' drop table DEX_SESSION'
print 'end'
print 'go'
print 'use master'
print 'go'
print 'if exists (select * from dbo.sysobjects where id = Object_id(''.smDEX_Build_Locks'') and type = ''P'')'
print 'begin'
print ' drop procedure smDEX_Build_Locks'
print 'end'
print 'go'
print 'create procedure smDEX_Build_Locks'
print 'as'
print 'exec (''create table tempdb..DEX_LOCK (session_id int, row_id int, table_path_name char(100))'')'
print 'exec (''create unique index PK_DEX_LOCK on tempdb..DEX_LOCK(row_id,table_path_name)'')'
print 'exec (''create table tempdb..DEX_SESSION (session_id int identity, sqlsvr_spid smallint)'')'
print 'exec (''create unique index PK_DEX_SESSION on tempdb..DEX_SESSION(session_id)'')'
print 'exec (''use tempdb grant insert,update,select,delete on DEX_LOCK to public'')'
print 'exec (''use tempdb grant insert,update,select,delete on DEX_SESSION to public'')'
print 'return'
print 'go'
print 'sp_procoption ''smDEX_Build_Locks'',''startup'',''true'''
print 'go'
print 'smDEX_Build_Locks'
print 'go'
-------------------------------------------------------------------------------
---------- Grant.sql and sp_changedbowner 'DYNSA' -----------------------------
-------------------------------------------------------------------------------
PRINT ''
PRINT '-- Grant.sql, sp_changedbowner, and remove user schemas --'
PRINT ''
declare @DatabaseId varchar(8)
declare DatabaseLoop cursor for
select name from master..sysdatabases where name in
(select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId
print 'declare @cStatement varchar(255)'
print 'declare @UserId sysname'
while (@@fetch_status <> -1)
begin
print ''
print '-----------------------------------------'
print 'print ''Security for ' + @DatabaseId + ''''
print 'USE [' + @DatabaseId + ']'
print ''
print 'declare G_cursor CURSOR for select ''grant select,update,insert,delete on ['' + convert(varchar(64),name) + ''] to DYNGRP'' from sysobjects'
print ' where (type = ''U'' or type = ''V'') and uid = 1'
print 'set nocount on'
print 'OPEN G_cursor'
print 'FETCH NEXT FROM G_cursor INTO @cStatement'
print 'WHILE (@@FETCH_STATUS <> -1)'
print 'begin'
print ' EXEC (@cStatement)'
print ' FETCH NEXT FROM G_cursor INTO @cStatement'
print 'end'
print 'DEALLOCATE G_cursor'
print 'declare G_cursor CURSOR for select ''grant execute on ['' + convert(varchar(64),name) + ''] to DYNGRP'' from sysobjects'
print ' where type = ''P'''
print 'set nocount on'
print 'OPEN G_cursor'
print 'FETCH NEXT FROM G_cursor INTO @cStatement'
print 'WHILE (@@FETCH_STATUS <> -1)'
print 'begin'
print ' EXEC (@cStatement)'
print ' FETCH NEXT FROM G_cursor INTO @cStatement'
print 'end'
print 'DEALLOCATE G_cursor'
-- Change Owner
print ''
print 'exec sp_changedbowner ''DYNSA'''
-- Remove user schemas
print ''
print 'declare UserIDLoop cursor for select USERID from DYNAMICS..SY01400'
print 'open UserIDLoop'
print 'fetch next from UserIDLoop into @UserId'
print 'while (@@fetch_status <> -1)'
print ' begin'
print ' IF EXISTS (SELECT * FROM sys.schemas WHERE name = @UserId)'
print ' exec(''DROP SCHEMA '' + @UserId)'
print ' fetch next from UserIDLoop into @UserId'
print ' end'
print 'close UserIDLoop'
print 'deallocate UserIDLoop'
fetch next from DatabaseLoop into @DatabaseId
end
close DatabaseLoop
deallocate DatabaseLoop
Needless to say, this is a "use at your own risk" sort of thing.