0

We have two virtual machines that one of them is live version of the system and one of them is the development environment. The problem is, when developers want to back up and restore the database from live machine to development machine, there are some sensitive data such as name, surname, father's name, date of birth, address etc. I don't want to backup user's sensitive data on certain table because of the GDPR.

So how can I backup and restore the database under this situation? Let's say the table name is Users. I just want to restore the all database apart from Users table's data. However, the developer accounts also store there, so I need to backup certain ids on Users table. I basically backup and restore the database through GUI on SQL Server Management Studio however I need to write some scripts for it.

BACKUP DATABASE [DB_NAME] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\DB_NAME_Date.bak' WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Mehmet Taha Meral
  • 3,315
  • 28
  • 30

3 Answers3

0

There is no such option in native MSSQL to backup and restore selective tables. However you can write scripts (although it can be complex) to move the data from prod->dev. Such as:

There is a commercial product that has object level restores, but it's not free: https://documentation.red-gate.com/sbu7/object-level-recovery I've used that in the past and it does work well.

sniperd
  • 5,124
  • 6
  • 28
  • 44
  • In addittion: a) using FILEGROUPS , b) replication. But anyway it's not an easy task – Alex Yu Jun 15 '18 at 13:37
  • Interesting about FILEGROUPS, I contemplated including that but it would be quite the challenge to put which tables into what groups :) I didn't think about replication, I'll add that in, thanks :) – sniperd Jun 15 '18 at 13:38
  • @sniperd thanks for your reply. I was just wondering is there anyway to that quickly. Otherwise Using Tasks->Export/Import, Generating Table Script with data or SELECT INTO are quite tricky and waste of time. I'd prefer to backup and restore the db manually and delete the data from table with Delete From Table name, and insert the developers information again :) – Mehmet Taha Meral Jun 15 '18 at 14:48
0

You can script data changes to run post-restore. Create some test data and write a script to replace production data with your test data as part of the restore job.

There are third party backup tools that do table level backup and restore such as Commvault. You can look into those as well.

I know for a fact that Commvault does table level restore. There is also Netbackup and Veeam that I know about. I think the recent versions of Netbackup may do object level backup/restore but not sure. Never used Veeam backup, but i've heard good things about them and their other products are very good. I've used Commvault, but not the table level restore option. Otherwise Commvault is great.

Alen
  • 182
  • 7
0

We did accounting system for telecoms and we had a need for a database in a lot of aspects similar to production database.

I can explain how we solved such problem.

Non-techical aspect:

We had a MUST: total agreement with our customer.

Their security and their DBAs reviewed our process.

Our process:

  • Step 1: Partial BACKUPs - we had no need for years of call history

  • Step 2: RESTORE on customer server

  • Step 3: Anonymizing scripts

  • Step 4: Review of anonymized data

  • Step 5: BACKUP of created db

Anonymizing process:

  1. Changing abonent names, phone numbers, social ids, other sensitive information
  2. Shuffling abonent IDs
  3. Random duplication of abonent data and associated call and billing history
  4. Altering call history: shuffling calls in bill periods, adding random not-existent calls, deleting random calls
  5. Altering billing history

Final thoughts:

I did not heard about ready solution for such tasks.

If you will find a tool that could do anonymizing similar to what we did - please share your knowledge.

That's all what I can say on this topic.

Decide yourself how it fits your requirments.

Alex Yu
  • 3,412
  • 1
  • 25
  • 38