SQLTeam.com | Weblogs | Forums

Database refresh from Production DB to Test DB


#11

ok for starters so as not to swarm you with all of the details, let's take baby steps. I would expect you to do most of the leg work and try to understand what this script is doing.
Please save this as ps1 , powershell, modify the $SourceFolder and $DestinationFolder to your specific environment and let's try that out first part which is
Please try this and post back if it worked or not.

  1. copying from prod to dev server.
$SourceFolder = ("\\your_backup_server\e$\Backup"); 
$DestinationFolder = ("\\your_dev_server:\d$\RestoreDB");
$date = (Get-Date).AddDays(-1).ToString("yyyyMMdd");	
	

#Delete All Files
Get-ChildItem -Path $DestinationFolder -Include *.* -File -Recurse | foreach { $_.Delete()}

#List all folders excluding system databases and others
$BackupFiles=$(get-childitem "$SourceFolder" | Where-Object { ($_.Name -Like "*bak*") -and ($_.Name -NotLike "master") });
$Yesterday = (get-date).AddDays(-1).ToString("yyy_MM_dd")


foreach( $backupfile in $BackupFiles)
{
	$yesterdaybackupfiles = $(get-childitem $backupfile.FullName | Where-Object {$_.Name -match $Yesterday}); 
        
	foreach( $file in $yesterdaybackupfiles)
	{
        $destinationFileName =  $file.Name
        $cleanfilename = $destinationFileName.Substring(0, $destinationFileName.IndexOf("_backup"))
        robocopy $SourceFolder $DestinationFolder $file.Name
        Rename-Item $DestinationFolder\$destinationFileName "$cleanfilename.bak";
    }

}

#12

Thanks Yosiasz for detail steps, I will run it and let you know.
But meanwhile, i found the solution and i think this will also work as this is not ongoing refresh as it might be a once a while, please correct me.

  1. Take Backup from Production DBs (bunch of different DBs backups)
  2. create Login script from DEV server to avoid SID mis match, Run Transfer logins and passwords between instances of SQL Server script which will generate sp_help_revlogin procedure
  3. Restore Production Database Backups for each Database backup respectively in DEV server and execute that procedure "sp_help_revlogin" so user can have original login after refresh
  4. Will run DB related Change script if any
    Do i need to do anything with SSIS or other jobs?

#13

Yes your approach is very good! you dont need to do SSIS or SQL jobs if you will be running all this manually.


#14

Thanks.
Some of the tables we don't want to restore or we have to keep the some of the data from DEV we need to keep it so any better idea or just keep the deployment script and rerun again in DEV?


#15

Your after restore script should truncate those tables. Of course you should to see if there are fo references and all.so restore and scrub rinse repeat


#16

Thank you again.
if we implement TDE in sql server 2014 for Database at Rest, During Refresh from PRODUCTION to TEST, what happened?
How to take care of it?


#17

do you have TDE now implemented. and why would you want to implement TDE inthis refresh scenario.


#18

I don't have TDE but we will be implementing.


#19

Might it be overkill if you will be scrambling the data or will you not be doing that?
Why do feel it is necessary to implement that?
I try to keep all environments as identical as possible that way there are no surprises on the application end, one less thing to worry about.
Sonic you implement the in one environ u should in all environs


#20

It's company requirement that thinking in that direction.


#21

IF i will be implementing TDE then following steps looks fine?

  1. Backup all Prodcution Databases
    2)Create a database master key and a certificate in the PRODUCTION master database.

USE master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
3) Create self-signed certificate in the PRODUCTION master database.

CREATE CERTIFICATE TDEProdServerCert
WITH SUBJECT = 'Certificate to protect ProdTDE key'
GO

  1. Create a backup of the server certificate in the PRODUCTION master database.
    BACKUP CERTIFICATE TDEProdServerCert
    TO FILE = 'TDEProdServerCert'
    WITH PRIVATE KEY
    (
    FILE = 'SQLPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';
    );
    GO
    -- This will stores the backup of the certificate and the private key file in the default data location
    But I should also keep the key into another server/Drive, Right?

  2. Create a database encryption key into Production DB, that is protected by the server certificate in the master database.

USE CustomerPRodDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDEProdServerCert;
GO
ALTER DATABASE CustomerPRodDB
SET ENCRYPTION ON;
GO

6)Do for All PRODUCTION DBs

  1. Move or copy the backup of the server certificate and the private key file from the source server to the destination server.
    Is it Location matter like same location as production DB location for TEST?

-- Create a database master key on the destination instance of SQL Server - TEST SERVER.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.

  1. CREATE CERTIFICATE TDEProdServerCert
    FROM FILE = 'TDEProdServerCert'
    WITH PRIVATE KEY
    (
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = 'P@ssw0rd'
    );
    GO

#22

But I would not try this in production first at all. NEVER try anything in prod that you have NOT fully vetted in preprod.
What upstream processes and applications do the databases support? You NEED to test all of these in a preprod environment with encryption enabled. Then when fully tested (including backup and restore of encrypted databases) you can confidently proceed to prod.
What is your backup plan if encryption hoses your prod system databases?


#23

Didn't understand "What is your backup plan if encryption hoses your prod system databases?"
If i understand correctly, then I have to turn off the encryption,

  1. Restore Backup
  2. Drop Master Key
  3. Drop certificate
  4. turn off the encryption
  5. Restart

#24

Have you ever performed those specific steps in dev/stage?


#25

Nope


#26

so I would recommend you test it in preprod 1st then when tested and vetted fully deploy to prod.


#27

Sure, We will do first in TEST but need to just verify the steps.


#28

your steps are fine.
try those steps in preProd,
test all applications

when all is good
implement same steps in production


#29

Sure, Thanks Yosiasz for your help!
I have one question as we have our data center at other location where we replicate our data so if we implement TDE then what will happen with our data replication as Microsoft does not support For Data Encryption, right?


#30

Any other surprises under your sleeve? Lay all of your sins, confess