September 27, 2007 at 6:39 am
Hello Folks,
New to SQL still learning have a question which i can't seem to find anywhere, I am looking for a application or script that will work with SQL to preform data-wiping of a database with out having to take the databases offline is this something that can be preformed on a SQL database.?
Thanks
JB--
September 28, 2007 at 2:56 am
Wiping data is just a process to achieve an objective. What is your objective? What is the justification for the objective?
Sometimes managers say they want data wiped so no-one can ever read it again. This is increasingly hard to achieve, unless you physically shred or melt your disk drives.
Writing binary zeroes many times to a track using normal applications will not remove all traces of the data that was there. There will be residual magnetism around the data tracks that someone with the right tools can access and use statistical analysis to rebuild large portions of the data that was on the disks. You can get specialised applications that issue drive-specific commands to place the write head to the left and right of the track centre line to try to clear the residual magnetism, but this is a specialised job (and not always successful) and it is normally cheaper to physically destroy your disk drives.
If your managers are concerned about unauthorised access to data, the industry best-practice approach is encryption. Choose an algorithm appropriate to your needs (stronger encryption = more time encrypting and decrypting). This way if anybody does get access to the data but without the encryption key all they see is a random mix of 1s and 0s.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
September 28, 2007 at 5:05 am
If you just wanted an empty database you could use dts / ssis to import the objects and not the data to a new database.
However, if you want your database left operational (not sure why that matters if you dont want anything in it).
You should be able to disable all constraints (search this site for a script. I know was one posted at one time.)
Then loop over all the tables doing delete from ....
You could end up with data integrity problems if people are inserting to the database while you are doing this.
Or you can script out dropping all your foreign keys.
Loop over your tables doing truncate table ... (less logging)
Then recreate your foreign keys.
September 28, 2007 at 7:42 am
usually you want to delete data in certain tables, but not "lookup tables".
the script below will delete/truncate every table in foreign key hierarchy order, but you should add to the NOT IN ('') Section.
NOCOUNT ON
DECLARE @level tinyint
SET @level = 0
CREATE TABLE #tables (
id INT NOT NULL PRIMARY KEY CLUSTERED,
TableName VARCHAR(255) NOT NULL,
LEVEL tinyint NOT NULL)
INSERT INTO #tables (id, TableName, LEVEL)
SELECT id, '[' + USER_NAME(uid) + '].[' + RTRIM(name) + ']' AS TableName, 0
FROM sysobjects
WHERE xtype = 'U'
AND status > 0
WHILE @@rowcount > 0
BEGIN
SET @level = @level + 1
UPDATE rt SET LEVEL = @level
FROM #tables rt
INNER JOIN sysreferences fk ON fk.rkeyid = rt.id
INNER JOIN #tables ft ON ft.id = fk.fkeyid
WHERE ft.LEVEL = @level - 1
END
PRINT 'USE ' + DB_NAME() + '
'
SELECT 'TRUNCATE TABLE ' + TableName
FROM #tables
WHERE LEVEL = 0
AND TableName NOT IN('')
SELECT 'DELETE ' + TableName
FROM #tables
WHERE LEVEL > 0
ORDER BY LEVEL
AND TableName NOT IN('')
DROP TABLE #tables
Lowell
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply