Restoring Datawarehouse DB from one SQL Server to DEV & UAT on a different SQL Server

SQLLover21 201 Reputation points
2020-11-30T15:30:04.603+00:00

Currently in our Production server there is a webservice call being made. The call works fine on PROD, but not in UAT and DEV. This is due to inconsistencies in the third party managed database.

Every month, there is a excel spreadsheet we have to grab from an external source (website) that will update old records or insert new records to SQL Server.

Apparently they are already uploading the information from the excel sheet to the data warehouse on a schedule, so we could potentially be ingesting it directly from DW instead of needing to manually process a spreadsheet. However, when I checked the data inside the tables, they are repeated and inconsistent.

So from my understanding, it sounds like I can backup the DB and restore it on to DEV and UAT.

My questions:

  1. Before starting the restore process, what are some considerations I need to discuss with my team members?
  2. What is the process of cleaning up the data?
  3. How to automate process monthly?
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,144 questions
Accepted answer
  1. Robbie Varn 261 Reputation points
    2020-11-30T19:23:21.067+00:00

    My thoughts on your questions based on what you have said:

    1. Before starting the restore process, what are some considerations I need to discuss with my team members?.
      Answers - How current does the data need to be? This will determine how often to perform the restore. The user of the data should be able to tell you this.
    2. What is the process of cleaning up the data?
      Answer - This depends on what the data is being used for. What data is not needed (age?) You could write a cleanup script to purge data from the table based on some kind of timestamp. The application owner and/or users can tell you this.
    3. How to automate process monthly?
      Answer - There are a different ways of automating a database restore to another Sql Server. One way would be to script/schedule it in a Sql agent job.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jeffrey Williams 1,881 Reputation points
    2020-11-30T22:14:46.313+00:00

    Before starting the process - you need to identify the logins/users of the database that need to exist in UAT/DEV. For those logins/users that do not already exist in production you need a script to add them after the restore.

    1 person found this answer helpful.
  2. Lukas Yu -MSFT 5,796 Reputation points
    2020-12-01T02:55:23.973+00:00

    Hi,

    Not directly answer your question, but are you trying to have your UAT and DEV database to be the same as PROD database?
    Not fully knowing your environment and requirement, but I think Database Synchronization could also be an option for this.

    Lukas

    1 person found this answer helpful.