0

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.

Improve this question
8
  • Anything wrong with a backup and restore of the database with a different name? You could even create a agent job which does all of this for you. Would the test database be on a separate or the same instance on the same server? Commented Mar 5, 2018 at 15:51
  • So you are saying to use that powershell script in an agent job? That would be the next step to make it a recurring process. Backup and restore is fine and yes, they would be on the same server and same instance Commented Mar 5, 2018 at 15:56
  • 1
    You could go the Powershell script route in an agent job. I personally would simply create a simple agent job with the backup in the task (using t-sql), then the next step, simply restore that database (using t-sql). Commented Mar 5, 2018 at 16:11
  • @rvsc48 - you should turn that comment into an answer. Comments get deleted. Commented Mar 5, 2018 at 16:43
  • 1
    Are you sure you want it on the same server? What happens to production if you test something new and it consumes 100% of the CPU? Or fills tempdb? SQL Developer Edition is free for most uses of a test environment, can you use that on a different server? Commented Mar 5, 2018 at 18:20

1 Answer 1

Reset to default
1

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
Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.