Refresh SQL Server Database

[APANetworks]

We have a production SQL Server SDE Database called APA_GIS_PROD and a test environment called APA_GIS_UAT. The data is edited in ArcGIS 10.2.1 with ArcFM. Our production SQL Server is using SQL Server 2008 and the UAT using SQL Server 2014. 

We want to refresh the UAT environment with the data from production but have just discovered that we can’t just copy the tables across using SQL Server tools as the database names need to stay the same.

Is there a way to copy all the data from our production environment into the test environment keeping the database names separate?

[JakeSkinner]

You can accomplish this using geodatabase replication:

Working with geodatabase replicas—ArcGIS Help | ArcGIS for Desktop

For example, you can have a one-way replica from your production to UAT geodatabase.

[APANetworks]

Thanks Jake, We will ultimate head down the replication path but at this stage I just want to copy all the data that is in PROD into UAT but have discovered that SDE databases can't be copied using a different name and although I could have a database call DatabaseProd in UAT it could get confusing.

[BirajaNayak]

Hi APA,

Can you please elaborate the requirement.

When you say refresh data from production to UAT, do you mean update everyday edits from production to UAT. If that is the requirement then Jake already provided solution using Geodatabase Replication.

Thanks,

Biraja

[APANetworks]

Thank you for your reply. At this stage we are not wanting the databases linked through replication, although that might end up being the ultimate result, but we want to be able to test business processes in our UAT environment using a copy of the latest data. The data in our UAt environment is getting out dated so I wanted to copy the information from our production database into the test database. We discovered that SDE databases have to keep their original names if you copy the whole database i.e. DatabaseProd can be called DatabaseUAT as their are internal pointers within SDE that mess with the connection. We don't really want a database sitting in UAT called DatabaseProd as this could be confusing.

[BirajaNayak]

Hi APA,

In that case, create a new sqlserver instance in UAT and restore backup from production or copy data using sqlserver tools.

Thanks,

Biraja

[APANetworks]

Hello Biraja, that is what we tried but unfortunately there is an issue with SDE databases changing names, so using SQL Server tools move the data but we are not able to connect to it any more.

[BirajaNayak]

Hi APA,

As you can not rename geodatabase name to access by SDE, hence I suggested to create another sqlserver instance and create same name geodatabase in UAT.  So basically UAT will have 2 separate sqlserver instance and one has UAT instance and another will have production copy geodatabase.

Thanks,

Biraja

[APANetworks]

Thanks Biraja,

Unfortunately we don't want two UAT databases, but just want the PROD one with a different name sitting in UAT.

Regards

Dean

[JakeSkinner]

You can create your other database at UAT and then replicate the entire PROD geodatabase.  When replicating an entire geodatabase, it's best to use the option 'Register existing data' when creating the replica:

For example, you could perform the following steps:

1. Export the PROD geodatabase to an XML
2. Import the XML to the geodatabase at UAT
3. Create the replica using the option to 'register existing data'