0

I made a custom application that is running from several years and is full of company data.

Now I need to replicate the application for another customer, so I set up a new server then i cloned the databases and empty all the tables.

Then I made a database and file shrink.

On the SQL Server side, the databases looks empty but if I run a grep search on the database files .mdf and .log I still can find recurrence of the previous company name also in system databases.

How do I really clean a SQL Server database?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mertie65
  • 3
  • 1

1 Answers1

2

Don't use backup/restore to clone a database for distribution to different clients. These commands copy data at the physical page/extent level, which may contain artifacts of deleted data, dropped objects, etc.

The best practice for this need is to create a new database with schema and system data from scratch using T-SQL scripts (ideally source controlled). If you don't already have these scripts, T-SQL scripts for schema/data can be generated from an existing database using the SMO API via .NET code or PowerShell. Here's the first answer I found with a search that uses the Microsoft.SqlServer.Management.SMO.Scripter class. Note you can include scripts data too (insert statements) by specifying the ScriptData scripting option for desired tables.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I have to "clone" 3 databases with hundreds of tables, constraints, triggers, stored procedures, permission etc. Is there a tool or a stored procedure to automate the process? – mertie65 Nov 21 '20 at 13:10
  • 1
    I suggest using SMO via PowerShell. Here's the first [answer](https://stackoverflow.com/questions/19434545/how-to-generate-sql-scripts-using-smo-scripter) I found with a search. Note you can script data too (insert statements) by specifying the `ScriptData` scripting option for desired tables. – Dan Guzman Nov 21 '20 at 13:17