CODEWITHSUNDEEP
sql-server
mertie65
mertie65

Reputation: 3

Cleared SQL Server tables still retain some data

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?

Upvotes: 0

Views: 42

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

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.

Upvotes: 2

Related Questions