SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Can A File be Totaly Removed from an SQL Server Database


How Can A File be Totaly Removed from an SQL Server Database

Author
Message
JC-3113
JC-3113
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 620
Hi Folks

My administrators have asked how does sql server handle the "Total" removal of a file from a SharePoint SQL Server database.

Basically the scenario is something like this:

We have an unclassified sharepoint sql server database in which only unclassified documents are placed via SharePoiint
Ocassionaly, someone gets a classified document an unknowingly uploads it into Sharepoint.

The question posed to me is how can we ensure that the document is entirely removed from the SQL Server database since sql server stores files in the database itself ?

Thanks
Jim
GilaMonster
GilaMonster
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115226 Visits: 45518
Um.... With extreme amounts of difficulty. Given that it's sharepoint, even harder.

In a normal, non-sharepoint DB, one option would be something like
Delete the sensitive data (just marks the space as not needed, does not overwrite)
Export all remaining data and all objects
Detach the database that had the sensitive data in
Use a secure delete program to wipe those files off disk (and all backups that may have the sensitive data in them)
Recreate the DB.

But you can't do that with sharepoint, which makes the problem significantly harder

If you find out soon enough you could shut down SQL use a secure delete to erase the DB files then restore a backup that didn't have the sensitive data, but that means identifying the problem very soon, and still may result in data loss.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


JC-3113
JC-3113
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 620
Thanks Gail

I did nolt think it was going to be easy

This sounds like SharePoint is going to be a problem when it comes to deleting the files

Jim
GilaMonster
GilaMonster
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115226 Visits: 45518
Even without sharepoint it's an incredibly difficult process (and the bigger the DB the worse it gets). This is definitely one place where prevention (warning messages in sharepoint, training) is better than cure

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Scott D. Jacobson
Scott D. Jacobson
Right there with Babe
Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)

Group: General Forum Members
Points: 764 Visits: 1018
Maybe with something like this?

http://www.titus.com/software/sharepoint/index.php

If not to delete them after the fact but, as Gail suggested, keep them from uploading them in the first place.
JC-3113
JC-3113
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 620
Thanks Scott

i think tis is a product for securing and protiectin documents

I need the opposite to get rid of them

Jim
EdVassie
EdVassie
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7385 Visits: 3875
Use a secure delete program to wipe those files off disk


If you do this, make sure you use a secure delete process that is compatible with the storage you are using.

Any 'secure delete' program that simply works by multiple writes to the file as defined by NTFS will do nothing to the underlaying data on most SANS and all SSD storage.

This is because most modern storage gets it performance by only doing writes and reads, and never does updates. When the OS wants to update a buffer full of data, the storage grabs some space from a free space pool and does the write to that. The original slot for the data on the storage device is then put in a garbage collection pool for recycling. The recycling process typically just marks the slot as empty and puts it back into the free space pool.

A 'secure delete' program that writes zeroes 5 times to the file space as defined by NTFS will only succeed in writing 5 sets of zeroes to the storage, leaving the original data untouched except for what garbage collection does.

Therefore if you want to do any form of secure delete that removes any trace of the bit pattern of the original data, you need something that can tie up the NTFS view and the storage device view of where the data is, and make sure the storage slots that hold the data get securely cleared. Most SAN vendors will supply such a program, but only as an extra-cost item to the basic SAN package.

If you have data on commodity SSD storage (eg OCZ, Crucial, Kingston, etc) the best way to do a secure delete is to put your SSD storage device into a furnace or a shredder.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search