How to have an always current “test database” of SAP B1

Introduction

Sometimes, SAP users need to try a transaction in a test database. But if the test database doesn’t have actual data, it doesn’t help. Always copy the productive and restore can be time consuming. This SQL snippets help you to generate every night an actual “test database”.

Steps

The job is created in the SQL Server Agent. Make sure, the agent is always running (also for your regular backups). On “SQL Server-Agent” > “Jobs” new jobs can be defined. This job has 4 steps:

1. Backup productive database
   A first step creates a regular backup of the productive database. Nothing special
2. Kill processes from the test database
   Because the test database will be overwritten in step 3 and some users or processes could be connected, this small task kills all the connections
3. Restore Backup to test database
   The backup from step 1 will be restored into the test database. Important is to rename the file and logical names correctly.
4. Change company name and background color
   This step is important for end users: The background color can be set to “pink” and the company name is “!!! TEST DATABASE !!!”. So, users are always sure, in which database they test or work productive.

Hint: Step 2 will only work, if already a test database is restored. When running the job first time, please don’t use step 2

Script step 1

BACKUP DATABASE [DB_NAME] TO  DISK = N'Y:\mssql\backup\DB_NAME.bak' WITH NOFORMAT, INIT,  NAME = N'DB_NAME - Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Script step 2

DECLARE @SQL_KillProcess varchar(max)
SELECT @SQL_KillProcess = COALESCE(@SQL_KillProcess,'')
+ 'Kill ' + Convert(varchar, SPId)
+ ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID('DB_NAME_TEST')
AND SPId <> @@SPId

-- Call the Kill Process
EXEC(@SQL_KillProcess)

Script step 3

RESTORE DATABASE [DB_NAME_TEST] FROM  DISK = N'Y:\mssql\backup\DB_NAME.bak' WITH  FILE = 1,  MOVE N'DB_NAME' TO N'M:\MSSQL\Data\DB_NAME_TEST.mdf',  MOVE N'DB_NAME_log' TO N'L:\MSSQL\Logs\DB_NAME_TEST_log.LDF',  NOUNLOAD, REPLACE,  STATS = 10
GO

ALTER DATABASE [DB_NAME_TEST] MODIFY FILE (NAME=N'DB_NAME', NEWNAME=N'DB_NAME_TEST')
GO

ALTER DATABASE [DB_NAME_TEST] MODIFY FILE (NAME=N'DB_NAME_log', NEWNAME=N'DB_NAME_TEST_log')
GO

Script step 4

USE DB_NAME_TEST
GO
UPDATE OADM
SET CompnyName = '!!! TEST DATABASE !!!'

UPDATE CINF
SET CompnyName = '!!! TEST DATABASE !!!'

UPDATE OADM
SET Color = 8

UPDATE OUDG
SET Color = 8

Never do SQL updates on productive databases!!!

Comments

• Schedule the job every night, for example 2am
• You must one time restored the test DB before introduce step 2. Run the job with step 1,3,4 and if success, implement step 2