5

I'm looking at backup routine which allows our production database to be backed up with sensitive data stripped out of certain columns within the database to be exported to our testing server.

The routine should require the least human intervention and hopefully just be a simple customisable SQL script without taking the production database offline.

Database server is SQL Server 2008.

Mike737
  • 836
  • 5
  • 16
  • standard or enterprise? I'm asking because enterprise has a feature called transparent data encryption which basically hides the data from users anyway, so you don't have to remove it before you backup... – M.R. May 11 '11 at 03:52
  • 1
    I'm not sure there is any simple way to do this: if the data you are storing is sensitive, it should be stored encrypted in the first place.... and the backups itself can be encrypted.... – M.R. May 11 '11 at 03:56
  • It's standard edition of SQL Server and the data is encrypted, but it still needs to be removed. We cannot let any sensitive data encrypted or unencrypted be exposed off our production server – Mike737 May 11 '11 at 04:45
  • How many tables are you talking? Could you make a series of views that accurately reflect the structure of the tables but hide the data desired and then use an SSIS package to regularly export the view as a dataset to your test server? – RThomas May 11 '11 at 06:20
  • Have you tried any [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) software such as [AWS Glue](https://aws.amazon.com/glue/developer-resources/)? It seems that this kind of tool is able to solve the problem. – Brian Aug 09 '18 at 09:31

2 Answers2

2

I've run into similar requirements before, and the only sure solution I know of is to use a copy of your production database. You can mask/delete data on the copy and run backups from there. Yes it's ugly and a waste of resources, but to date I haven't found a solid alternative for this particular problem.

As for the copy method, you do have some options:

  • Replication
  • Scheduled DB copy
  • Backup/restore from production

So while I admit this solution is pretty cringe-worthy, it can be automated and serve your purposes. If you can find productive uses for the database copy that don't require your deleted information (e.g. reports, testing, development) then this can actually be a less-than-terrible solution. It can be a nice security boon to have a slightly out-of-date version of your production database with sensitive data removed.

ajk
  • 4,473
  • 2
  • 19
  • 24
  • I've selected your answer as it's the best solution anyone has come up with and it's what I've implemented. – Mike737 May 26 '11 at 10:32
-1

If you want to take a backup then Just type

BACKUP DATABASE Dbname

If you want to specify offline or anything else then you can do it. The backup file will generate on the default path of the SQL SERVER 2008.

Sanjay
  • 342
  • 2
  • 9