I've updated my script since my
original post back in 2011 that is used to refresh a GP test company with live data.
This tried and tested script will perform the following:
- Back up the current test database (optional; on by default)
- Back up the live database (optional; on by default)
- Restore the live backup (or, optionally, any other SQL backup)
- Run the CreateTestCompany (Microsoft) script
- Set the output settings to print to screen instead of a printer (optional; on by default)
- Change the Recovery Mode to Simple, and shrink the log file
DECLARE
@SourceDatabase varchar(10),
@TESTDatabase varchar(10),
@DatabaseFolder varchar(1000),
@LogFolder varchar(1000),
@BackupFolder varchar(1000),
@BackupFilename varchar(100),
@TESTBackupFilename varchar(100),
@RestoreFile varchar(100),
@RestoreToDataFolder varchar(100),
@RestoreToLogFolder varchar(100),
@UseRestoreFile smallint,
@UseRestoreToFolders smallint,
@CompressionAllowed smallint,
@Compress smallint,
@BackupTESTFirst smallint,
@LogicalFilenameDB varchar(100),
@LogicalFilenameLog varchar(100),
@SetPrintToScreen smallint,
@SQL varchar(max)
-----------------------------------------------------------------------
-- Set up all the information here for the live and test database names
-----------------------------------------------------------------------
SET @SourceDatabase = 'TWO'
USE [TWO] -- enter the Source Database between the braces
SET @TESTDatabase = 'TEST'
SET @BackupFolder = 'F:\DB Backups\' --end with a backslash (\)
-- Folder where you want to save the backup file
SET @Compress = 1 -- 0 = Do not compress backups; 1 = Compress backups
-- 2008 and forward; SQL Express and some Standard editions do not allow compression
SET @BackupTESTFirst = 1 -- 0 = No; 1 = Yes
-- Backup the TEST database before restoring
-- You should do this the first time you restore per day
SET @UseRestoreFile = 0 -- 0 = Create a backup and restore it
-- 1 = Use the below filename to restore from instead of creating new backup
-- Use this when you just want to reload a backup you already created
SET @RestoreFile = 'TWO_20130403.bak'
-- File to restore if you have already created a backup of the live company
SET @UseRestoreToFolders = 1 -- 0 = Restore to the same folder as the live database;
-- 1 = Restore to the folders specified below
SET @RestoreToDataFolder = 'G:\SQL Data TEST\' --end with a backslash (\)
-- Folder where you want to restore the .mdf (Data) file TO
SET @RestoreToLogFolder = 'H:\SQL Log TEST\' --end with a backslash (\)
-- Folder where you want to restore the .ldf (Log) file TO
SET @SetPrintToScreen = 1 -- 0 = No; 1 = Yes
-- Change the posting output to the screen instead of the printer
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT @CompressionAllowed = CONVERT(smallint,ISNULL((SELECT value FROM sys.configurations WHERE name = 'backup compression default'),-1))
SELECT @LogicalFilenameDB = (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 1
SELECT @LogicalFilenameLog = (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 0
IF @UseRestoreToFolders = 1
BEGIN
SET @DatabaseFolder = @RestoreToDataFolder
SET @LogFolder = @RestoreToLogFolder
print 'Using specified data folder ' + @DatabaseFolder + ' and log folder ' + @LogFolder
END
ELSE
BEGIN
SELECT TOP 1 @DatabaseFolder = left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 1
SELECT TOP 1 @LogFolder = left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 0
print 'Using live database''s data folder ' + @DatabaseFolder + ' and log folder ' + @LogFolder
END
print 'Compression: ' + CASE WHEN @Compress = 1 AND @CompressionAllowed = -1 THEN 'NOT ALLOWED' WHEN @Compress = 1 THEN 'ON' ELSE 'OFF' END
print @LogicalFilenameDB
print @LogicalFilenameLog
print @DatabaseFolder
SET @SQL = ''
IF @UseRestoreFile = 0
BEGIN
SET @BackupFilename = @BackupFolder + @SourceDatabase +
'_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
'_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
'_for_' + @TESTDatabase + '.bak'
END
ELSE
BEGIN
SET @BackupFilename = @BackupFolder + @RestoreFile
END
print '.'
print 'Backup Filename: ' + @BackupFilename
print '.'
SET @TESTBackupFilename = @BackupFolder + @TESTDatabase +
'_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
'_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
'_pre_LIVE_restore.bak'
-- check to see if the TEST database is in use first
IF EXISTS(SELECT spid,loginame=rtrim(loginame),hostname,program_name,login_time,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
= @TESTDatabase)
BEGIN
PRINT 'The database is in use'
SELECT spid,loginame=rtrim(loginame),hostname,program_name,login_time,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
= @TESTDatabase
END
ELSE
BEGIN
IF @UseRestoreFile = 0
BEGIN
-- Back up the LIVE database
SET @SQL = '' +
'PRINT ''Backing up Source database (' + @SourceDatabase + ')''; ' +
'BACKUP DATABASE [' + @SourceDatabase + '] TO DISK ' +
'= N''' + @BackupFilename + ''' ' +
'WITH COPY_ONLY, NOFORMAT, NOINIT, NAME ' +
'= N''' + @SourceDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
IF @Compress = 1 AND @CompressionAllowed > -1
SET @SQL = @SQL + ', COMPRESSION'
EXEC (@SQL)
END
-- Back up the TEST database
IF @BackupTESTFirst = 1
BEGIN
SET @SQL = '' +
'PRINT ''Backing up Destination database (' + @TESTDatabase + ')''; ' +
'BACKUP DATABASE [' + @TESTDatabase + '] TO DISK ' +
'= N''' + @TESTBackupFilename + ''' ' +
'WITH COPY_ONLY, NOFORMAT, NOINIT, NAME ' +
'= N''' + @TESTDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
IF @Compress = 1 AND @CompressionAllowed > -1
SET @SQL = @SQL + ', COMPRESSION'
EXEC (@SQL)
END
-- Restore to the TEST database
SET @SQL = '' +
'PRINT ''Restoring to Destination database (' + @TESTDatabase + ')''; ' +
'RESTORE DATABASE [' + @TESTDatabase + '] FROM DISK ' +
'= N''' + @BackupFilename + ''' ' +
'WITH FILE = 1, ' +
'MOVE N''' + @LogicalFilenameDB + ''' TO ' +
'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Dat.mdf'', ' +
'MOVE N''' + @LogicalFilenameLog + ''' TO ' +
'N''' + @LogFolder + 'GPS' + @TESTDatabase + 'Log.ldf'', ' +
'NOUNLOAD, REPLACE, STATS = 10'
EXEC (@SQL)
SET @SQL = '' +
'PRINT ''Setting Recovery Model to Simple''; ' +
'ALTER DATABASE [' + @TESTDatabase + '] SET RECOVERY SIMPLE WITH NO_WAIT'
EXEC (@SQL)
SET @SQL = '' +
'PRINT ''Shrinking Log File'' ' +
'USE [' + @TESTDatabase + '] ' +
'DBCC SHRINKFILE (N''' + @LogicalFilenameLog + ''' , 0, TRUNCATEONLY)'
EXEC (@SQL)
IF @SetPrintToScreen = 1
BEGIN
SET @SQL = '' +
'PRINT ''Setting output to print to screen instead of printer'' ' +
'USE [' + @TESTDatabase + '] ' +
'UPDATE SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0'
EXEC (@SQL)
END
-- Run the CreateTestCompany script from MS
SET @SQL = '' +
'PRINT ''Running CreateTestCompany script on ' + @TESTDatabase + '''; ' +
'USE [' + @TESTDatabase + '] ' +
'if not exists(select 1 from tempdb.dbo.sysobjects where name = ''##updatedTables'') ' +
' create table [##updatedTables] ([tableName] char(100)) ' +
'truncate table ##updatedTables ' +
'declare @cStatement varchar(255) ' +
'declare G_cursor CURSOR for ' +
'select ' +
'case ' +
'when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'') ' +
' then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) ' +
'else ' +
'''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' ' +
'end ' +
'from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c ' +
'where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'', ''COMPANYCODE_I'') ' +
'and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ''BASE TABLE''; ' +
'set nocount on; ' +
'OPEN G_cursor; ' +
'FETCH NEXT FROM G_cursor INTO @cStatement ' +
'WHILE (@@FETCH_STATUS <> -1) ' +
'begin ' +
'insert ##updatedTables select ' +
'substring(@cStatement,8,patindex(''%set%'',@cStatement)-9) ' +
'Exec (@cStatement) ' +
'FETCH NEXT FROM G_cursor INTO @cStatement ' +
'end ' +
'DEALLOCATE G_cursor ' +
'select [tableName] as ''Tables that were Updated'' from ##updatedTables '
EXEC (@SQL)
PRINT 'Don''t forget to run GP SQL Maintenance on the ' + @TESTDatabase + ' database.'
END
Version 4 changes:
- Specify the restore folder for the data file (.mdf)
- Specify the restore folder for the log file (.ldf)
Version 3 changes:
- Backup compression (optional; on by default)
- Uses the "Copy Only" switch - so it does not mark the database as backed up - useful when using certain other backup software as the primary backup
Version 2 changes:
- Messages window shows the auto-generated backup filename of the LIVE database for use in the optional restore feature below
- Restores a backup you already created (optional)
- Changes the posting output of the TEST company in GP to Screen instead of Printer (optional)
- Changes the Recovery Model to Simple and shrinks the log file of the TEST database
Version 1
- Backs up the TEST database (optional)
- Backs up the LIVE database and restore to the TEST database
- Runs CreateTestCompany script from PartnerSource against the TEST database
