SQL Server 2014 – Create/Update Test Database from Production

powershellsql serversql server 2014ssis

I want to create a test environment database that is refreshed from the production database every month or as needed (they would both be on the same server). It would not just be data refreshing because if I make changes to the schema I would like that to be updated as well. What would be the best way?
I have tried some powershell commands but most of them are for one time duplications like this. If powershell is the way to go I would like it to look for that database and if it exists I would like it to update.

Best Answer

Here is a very quick TSQL prototype that you could put into a SQL Agent job

USE MASTER
GO

--IMPORTANT - Use COPY_ONLY when backing up production to prevent messing up recovery for production
--Backup production
BACKUP DATABASE zzz TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzz.bak'
WITH NOFORMAT
    ,INIT
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
    ,COPY_ONLY

--Restore to new database name using the backup from the previous command
RESTORE DATABASE zzzNew
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzz.bak'
WITH FILE = 1
    ,MOVE N'zzz' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzzNew.mdf'
    ,MOVE N'zzz_LOG' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzzNew_LOG.ldf'
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 10
    ,RECOVERY

--optional command to put test database into simple recovery mode, if
--production had been in FULL and you don't need the test database in FULL
--ALTER DATABASE zzzNew SET RECOVERY SIMPLE WITH NO_WAIT
Related Question