SQL Script to copy Sage databases

This is 10 to 50 times faster than a Dbdump/Dbload.  Enjoy!

USE [master]
--Initialize variables
Declare @db_to_name nvarchar(8)
Declare @db_from_name nvarchar(8)
Declare @sql_data_folder nvarchar(150)
Declare @sql_log_folder nvarchar(150)
Declare @sql_backup_folder nvarchar(150)
declare @full_backup_name nvarchar(200)
declare @ldf_from_name nvarchar(200)
declare @ldf_to_name nvarchar(200)
declare @mdf_from_name nvarchar(200)
declare @backup_description nvarchar(200)
declare @ssql nvarchar(max)
declare @sage_company_name nvarchar(200)

-- Fill in the appropriate values on your system
Set @db_from_name = 'ABCDAT'
Set @db_to_name = 'ABCTST'
Set @sql_data_folder = 'D:\Data\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA'
Set @sql_log_folder = 'D:\Data\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA'
Set @sql_backup_folder = 'D:\Data\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP'
set @sage_company_name = '''ABCTST'''

set @full_backup_name = '''' + @sql_backup_folder + @db_from_name + '.bak'''
set @backup_description = '''' + @db_from_name + '-Full Database Backup'''


--Kill any processes accessing the "to" database
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@db_to_name)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@db_to_name) and spid > @spid
End

-- Do the backup
set @ssql = ' BACKUP DATABASE [' + @db_from_name + '] TO DISK = ' + @full_backup_name
set @ssql += ' WITH NOFORMAT, INIT, NAME = ' + @backup_description + ', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
EXEC sp_executesql @ssql

-- Do the restore
set @mdf_from_name = '''' + @sql_data_folder + @db_to_name + '.mdf'''
set @ldf_from_name = @db_from_name + '_log'
set @ldf_to_name = '''' + @sql_log_folder + @db_to_name + '_log.ldf' + ''''
set @ssql = 'RESTORE DATABASE [' + @db_to_name + '] FROM DISK = ' + @full_backup_name
set @ssql += ' WITH FILE = 1, '
set @ssql += ' MOVE ''' + @db_from_name + ''' TO ' + @mdf_from_name + ', '
set @ssql += ' MOVE ''' + @ldf_from_name + ''' TO ' + @ldf_to_name + ', '
set @ssql += ' NOUNLOAD, REPLACE, STATS = 5'
EXEC sp_executesql @ssql

--Set the Accpac fields
set @ssql = ' USE [' + @db_to_name+ ']'
set @ssql += ' UPDATE CSCOM SET ORGID = ''' + @db_to_name + ''', CONAME = ' + @sage_company_name
set @ssql += ' UPDATE CSAUTH SET COMPANYID = ''' + @db_to_name + ''''
EXEC sp_executesql @ssql
GO