Copy Live to Test company - version 4

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