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?
    – rvsc48
    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
    – SQLserving
    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).
    – rvsc48
    Mar 5, 2018 at 16:11
  • @rvsc48 - you should turn that comment into an answer. Comments get deleted.
    – Hannah Vernon ♦
    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?
    – Greg
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.