-1

what is the best way to transfer database copy as backed up file for outside maintenance on the application? But that copy should not have any sensitive data and it can only have dummy data. What is the efficient and best practice to erase all data in the tables and populate with dummy data? ( sql server 2019)

LukStorms
  • 28,916
  • 5
  • 31
  • 45
beniyam mitiku
  • 11
  • 2
    Buy a 3rd party tool and get it to do all the work for you? The expensive ones are probably *very* efficient at it, and have a lot of recommendations to get you to observe "best practice". – Thom A Jan 05 '22 at 15:46
  • Following @Larnu's advice, if it's a one-time thing you can probably find some tool with a free trial period that can help you to get it done. – Zohar Peled Jan 05 '22 at 16:03
  • This question is probably best for dba.stackexchange.com. – Paul Williams Jan 05 '22 at 16:20

1 Answers1

0

This is not a trivial task. A 3rd party solution would probably be easiest.

There are several answers available here that discuss copying objects in SQL Server Management Studio. Example: Backup SQL Schema Only?.

If you have access to SQL Server Integration Services, you can copy selected objects using the Transfer SQL Server Objects Task. I have tried this once a long time ago, so I have very little experience to describe how it works.

Another option is to create a job that runs a copy-only backup, restores the database, and then runs a manual series of SQL queries to clear or mask sensitive data.

Paul Williams
  • 16,585
  • 5
  • 47
  • 82