RE: Refreshing Test Database
Here's a basic script I use to sort of automate it. Adjust DB and filenames to meet your needs.
Use master
PRINT GETDATE()
ALTER DATABASE SLTestApp
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
ALTER DATABASE SLTestSys
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE SLTestApp
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SLApplication.bak'
WITH REPLACE,
MOVE 'SLApplication_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SLTestApp.mdf',
MOVE 'SLApplication_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SLTestApp_1.ldf'
GO
RESTORE DATABASE SLTestSys
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\restore\SLSystem.bak'
WITH REPLACE,
MOVE 'SLSystem_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SLTestSys.mdf',
MOVE 'SLSystem_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SLTestSys_1.ldf'
ALTER DATABASE SLTestApp SET MULTI_USER
ALTER DATABASE SLTestSys SET MULTI_USER
USE SLTestSys
--Update Server and Database names in system db tables
update Domain set DatabaseName = 'SLTestApp' where DatabaseName = 'SLApplication'
update Domain set DatabaseName = 'SLTestSys' where DatabaseName = 'SLSystem'
update Domain set ServerName = 'TESTSERVER'
update Company set DatabaseName = 'SLTestApp' where DatabaseName = 'SLApplication'
--Change colors to RED
update Company set CpnyColor = 255
--Rename Companies
update Company set CpnyName = '-- TEST -- ' + LEFT(CpnyName,19)
go
PRINT GETDATE()
--Now run SL Database Maint and
--Syncronize all Database Ownership and Security
--Update Views