script for duplicating Dynamics SL production databases into test databases

SLtestdbs.sql

BACKUP DATABASE IECISAPP --YOUR PRODUCTION DB HERE, takes ~5min for 8GB
TO DISK = 'V:\backups_temp\DYNSL\applicationdb.bak'
WITH COPY_ONLY, INIT, SKIP;
GO

BACKUP DATABASE IECISSYS --YOUR SYSTEM DB HERE, takes <30 seconds for 50MB
TO DISK = 'V:\backups_temp\DYNSL\systemdb.bak'
WITH COPY_ONLY, INIT, SKIP;
GO

-- UPDATES TEST DB HERE, takes ~3min
ALTER DATABASE [TEST_IECISApp] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST_IECISApp] FROM  DISK = N'V:\backups_temp\DYNSL\applicationdb.bak' WITH REPLACE,  STATS = 5 
ALTER DATABASE [TEST_IECISApp] SET MULTI_USER
GO

ALTER DATABASE [TEST_IECISSys] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST_IECISSys] FROM  DISK = N'V:\backups_temp\DYNSL\systemdb.bak'   WITH REPLACE,  STATS = 5 
ALTER DATABASE [TEST_IECISSys] SET MULTI_USER
GO

ALTER DATABASE TEST_IECISAPP
SET RECOVERY SIMPLE;
GO

ALTER DATABASE TEST_IECISSYS
SET RECOVERY SIMPLE;
GO

USE [TEST_IECISApp]
GO
DBCC SHRINKFILE (N'IECISApp_Log' , 0, TRUNCATEONLY)
GO


USE TEST_IECISSYS;

UPDATE COMPANY SET DatabaseName = 'TEST_IECISApp', CpnyColor = '255' WHERE DATABASENAME = 'IECISApp'
UPDATE DOMAIN SET DatabaseName = 'TEST_IECISApp' WHERE DATABASENAME = 'IECISApp'

UPDATE DOMAIN SET DatabaseName = 'TEST_IECISSys' WHERE DATABASENAME = 'IECISSys'
GO

USE TEST_IECISAPP;
GO
ALTER VIEW [dbo].[vs_company] AS SELECT * FROM test_IECISSys..company