How to completely delete any trace of data from a SQL2000 data fil

Hi all. I'm attempting to generate policies and procedures for dealing with
the possibility of classified data contamination in a SPS 2003 deployment,
which uses SQL 2000 for storage of all content. By "classified data
contamination", I mean that a user accidentally puts classified data into an
unclassified system, which is a major no-no. This is purely a SQL 2000 issue
- just mentioning SPS 2003 for background info.
Basically, I need to make sure that once a content item is deleted via SPS
2003 (which deletes the associated rows for the content item from the SQL
2000 content DB - yes, I have checked), that it's extremely difficult to
retrieve the content item or any residual data from it.
I conducted the following test series:
1. Upload a test "classified" word document into SPS 2003.
2. Delete the test "classified" word document via SPS 2003 (at this point
all the DB rows associated with the document are gone - can't see them in
EM/Query Analyzer). Text of data file still contains the text of the document.
3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
the text of the document.
4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
file still contains the text of the document.
Only idea I've found is scripting out the DB and rebuilding it, but this is
DML-based, which would void support for SPS. Any other ideas?How are you verifying that ' Text of data file still contains the text of the
document'?
"BAG" wrote:
> Hi all. I'm attempting to generate policies and procedures for dealing with
> the possibility of classified data contamination in a SPS 2003 deployment,
> which uses SQL 2000 for storage of all content. By "classified data
> contamination", I mean that a user accidentally puts classified data into an
> unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> - just mentioning SPS 2003 for background info.
> Basically, I need to make sure that once a content item is deleted via SPS
> 2003 (which deletes the associated rows for the content item from the SQL
> 2000 content DB - yes, I have checked), that it's extremely difficult to
> retrieve the content item or any residual data from it.
> I conducted the following test series:
> 1. Upload a test "classified" word document into SPS 2003.
> 2. Delete the test "classified" word document via SPS 2003 (at this point
> all the DB rows associated with the document are gone - can't see them in
> EM/Query Analyzer). Text of data file still contains the text of the document.
> 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> the text of the document.
> 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> file still contains the text of the document.
> Only idea I've found is scripting out the DB and rebuilding it, but this is
> DML-based, which would void support for SPS. Any other ideas?|||Didn't have to do anything fancy to do the verification, actually. Using a
text editor robust enough to allow me to view the large data files (I've used
TextPad, UltraEdit 32, and Boxer on various occasions), I did the following:
1. Opened the file.
2. Used the Find function to search for marker text strings I placed in the
document.
3. Found the marker text strings. These were embedded in the full text of
the document, which was present in its entirety.
-B
"Absar Ahmad" wrote:
> How are you verifying that ' Text of data file still contains the text of the
> document'?
> "BAG" wrote:
> > Hi all. I'm attempting to generate policies and procedures for dealing with
> > the possibility of classified data contamination in a SPS 2003 deployment,
> > which uses SQL 2000 for storage of all content. By "classified data
> > contamination", I mean that a user accidentally puts classified data into an
> > unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> > - just mentioning SPS 2003 for background info.
> >
> > Basically, I need to make sure that once a content item is deleted via SPS
> > 2003 (which deletes the associated rows for the content item from the SQL
> > 2000 content DB - yes, I have checked), that it's extremely difficult to
> > retrieve the content item or any residual data from it.
> >
> > I conducted the following test series:
> > 1. Upload a test "classified" word document into SPS 2003.
> > 2. Delete the test "classified" word document via SPS 2003 (at this point
> > all the DB rows associated with the document are gone - can't see them in
> > EM/Query Analyzer). Text of data file still contains the text of the document.
> > 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> > the text of the document.
> > 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> > file still contains the text of the document.
> >
> > Only idea I've found is scripting out the DB and rebuilding it, but this is
> > DML-based, which would void support for SPS. Any other ideas?|||Are you opening 'data files' of the SQL Server Database?
I am sorry if my question looks stupid.
"BAG" wrote:
> Didn't have to do anything fancy to do the verification, actually. Using a
> text editor robust enough to allow me to view the large data files (I've used
> TextPad, UltraEdit 32, and Boxer on various occasions), I did the following:
> 1. Opened the file.
> 2. Used the Find function to search for marker text strings I placed in the
> document.
> 3. Found the marker text strings. These were embedded in the full text of
> the document, which was present in its entirety.
> -B
> "Absar Ahmad" wrote:
> > How are you verifying that ' Text of data file still contains the text of the
> > document'?
> >
> > "BAG" wrote:
> >
> > > Hi all. I'm attempting to generate policies and procedures for dealing with
> > > the possibility of classified data contamination in a SPS 2003 deployment,
> > > which uses SQL 2000 for storage of all content. By "classified data
> > > contamination", I mean that a user accidentally puts classified data into an
> > > unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> > > - just mentioning SPS 2003 for background info.
> > >
> > > Basically, I need to make sure that once a content item is deleted via SPS
> > > 2003 (which deletes the associated rows for the content item from the SQL
> > > 2000 content DB - yes, I have checked), that it's extremely difficult to
> > > retrieve the content item or any residual data from it.
> > >
> > > I conducted the following test series:
> > > 1. Upload a test "classified" word document into SPS 2003.
> > > 2. Delete the test "classified" word document via SPS 2003 (at this point
> > > all the DB rows associated with the document are gone - can't see them in
> > > EM/Query Analyzer). Text of data file still contains the text of the document.
> > > 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> > > the text of the document.
> > > 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> > > file still contains the text of the document.
> > >
> > > Only idea I've found is scripting out the DB and rebuilding it, but this is
> > > DML-based, which would void support for SPS. Any other ideas?|||Exactly. In this case, the DB in question just has one data file (.mdf) and
one log file
(.ldf). I found the text of the document in the .mdf even after I confirmed
it was deleted in the tables.
-B
"Absar Ahmad" wrote:
> Are you opening 'data files' of the SQL Server Database?
> I am sorry if my question looks stupid.
> "BAG" wrote:
> > Didn't have to do anything fancy to do the verification, actually. Using a
> > text editor robust enough to allow me to view the large data files (I've used
> > TextPad, UltraEdit 32, and Boxer on various occasions), I did the following:
> >
> > 1. Opened the file.
> > 2. Used the Find function to search for marker text strings I placed in the
> > document.
> > 3. Found the marker text strings. These were embedded in the full text of
> > the document, which was present in its entirety.
> >
> > -B
> >
> > "Absar Ahmad" wrote:
> >
> > > How are you verifying that ' Text of data file still contains the text of the
> > > document'?
> > >
> > > "BAG" wrote:
> > >
> > > > Hi all. I'm attempting to generate policies and procedures for dealing with
> > > > the possibility of classified data contamination in a SPS 2003 deployment,
> > > > which uses SQL 2000 for storage of all content. By "classified data
> > > > contamination", I mean that a user accidentally puts classified data into an
> > > > unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> > > > - just mentioning SPS 2003 for background info.
> > > >
> > > > Basically, I need to make sure that once a content item is deleted via SPS
> > > > 2003 (which deletes the associated rows for the content item from the SQL
> > > > 2000 content DB - yes, I have checked), that it's extremely difficult to
> > > > retrieve the content item or any residual data from it.
> > > >
> > > > I conducted the following test series:
> > > > 1. Upload a test "classified" word document into SPS 2003.
> > > > 2. Delete the test "classified" word document via SPS 2003 (at this point
> > > > all the DB rows associated with the document are gone - can't see them in
> > > > EM/Query Analyzer). Text of data file still contains the text of the document.
> > > > 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> > > > the text of the document.
> > > > 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> > > > file still contains the text of the document.
> > > >
> > > > Only idea I've found is scripting out the DB and rebuilding it, but this is
> > > > DML-based, which would void support for SPS. Any other ideas?

How to completely delete any trace of data from a SQL2000 data fil

Hi all. I'm attempting to generate policies and procedures for dealing with
the possibility of classified data contamination in a SPS 2003 deployment,
which uses SQL 2000 for storage of all content. By "classified data
contamination", I mean that a user accidentally puts classified data into an
unclassified system, which is a major no-no. This is purely a SQL 2000 issue
- just mentioning SPS 2003 for background info.
Basically, I need to make sure that once a content item is deleted via SPS
2003 (which deletes the associated rows for the content item from the SQL
2000 content DB - yes, I have checked), that it's extremely difficult to
retrieve the content item or any residual data from it.
I conducted the following test series:
1. Upload a test "classified" word document into SPS 2003.
2. Delete the test "classified" word document via SPS 2003 (at this point
all the DB rows associated with the document are gone - can't see them in
EM/Query Analyzer). Text of data file still contains the text of the document.
3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
the text of the document.
4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
file still contains the text of the document.
Only idea I've found is scripting out the DB and rebuilding it, but this is
DML-based, which would void support for SPS. Any other ideas?
How are you verifying that ' Text of data file still contains the text of the
document'?
"BAG" wrote:

> Hi all. I'm attempting to generate policies and procedures for dealing with
> the possibility of classified data contamination in a SPS 2003 deployment,
> which uses SQL 2000 for storage of all content. By "classified data
> contamination", I mean that a user accidentally puts classified data into an
> unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> - just mentioning SPS 2003 for background info.
> Basically, I need to make sure that once a content item is deleted via SPS
> 2003 (which deletes the associated rows for the content item from the SQL
> 2000 content DB - yes, I have checked), that it's extremely difficult to
> retrieve the content item or any residual data from it.
> I conducted the following test series:
> 1. Upload a test "classified" word document into SPS 2003.
> 2. Delete the test "classified" word document via SPS 2003 (at this point
> all the DB rows associated with the document are gone - can't see them in
> EM/Query Analyzer). Text of data file still contains the text of the document.
> 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> the text of the document.
> 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> file still contains the text of the document.
> Only idea I've found is scripting out the DB and rebuilding it, but this is
> DML-based, which would void support for SPS. Any other ideas?

How to completely delete any trace of data from a SQL2000 data fil

Hi all. I'm attempting to generate policies and procedures for dealing with
the possibility of classified data contamination in a SPS 2003 deployment,
which uses SQL 2000 for storage of all content. By "classified data
contamination", I mean that a user accidentally puts classified data into an
unclassified system, which is a major no-no. This is purely a SQL 2000 issue
- just mentioning SPS 2003 for background info.
Basically, I need to make sure that once a content item is deleted via SPS
2003 (which deletes the associated rows for the content item from the SQL
2000 content DB - yes, I have checked), that it's extremely difficult to
retrieve the content item or any residual data from it.
I conducted the following test series:
1. Upload a test "classified" word document into SPS 2003.
2. Delete the test "classified" word document via SPS 2003 (at this point
all the DB rows associated with the document are gone - can't see them in
EM/Query Analyzer). Text of data file still contains the text of the documen
t.
3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
the text of the document.
4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
file still contains the text of the document.
Only idea I've found is scripting out the DB and rebuilding it, but this is
DML-based, which would void support for SPS. Any other ideas?How are you verifying that ' Text of data file still contains the text of th
e
document'?
"BAG" wrote:

> Hi all. I'm attempting to generate policies and procedures for dealing wit
h
> the possibility of classified data contamination in a SPS 2003 deployment,
> which uses SQL 2000 for storage of all content. By "classified data
> contamination", I mean that a user accidentally puts classified data into
an
> unclassified system, which is a major no-no. This is purely a SQL 2000 iss
ue
> - just mentioning SPS 2003 for background info.
> Basically, I need to make sure that once a content item is deleted via SPS
> 2003 (which deletes the associated rows for the content item from the SQL
> 2000 content DB - yes, I have checked), that it's extremely difficult to
> retrieve the content item or any residual data from it.
> I conducted the following test series:
> 1. Upload a test "classified" word document into SPS 2003.
> 2. Delete the test "classified" word document via SPS 2003 (at this point
> all the DB rows associated with the document are gone - can't see them in
> EM/Query Analyzer). Text of data file still contains the text of the docum
ent.
> 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> the text of the document.
> 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> file still contains the text of the document.
> Only idea I've found is scripting out the DB and rebuilding it, but this i
s
> DML-based, which would void support for SPS. Any other ideas?

How to compare SQL server stored procedures from two separate databases?

Is there a straight forward way or a tool to compare SQL server database objects, such as stored procedures located in separate databases?

Well, the way that I always do it is to use the RedGate SQL Compare tool. www.red-gate.com. It works very nice and will compare everything, including permissions.

There are other ways, and Buck Woody (who I have seen around the forums) has an article here that covers it pretty well:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=210&rl=1

|||You can use Visual Studio Team System(VSTS) for Database professional. It is covered all the features of readgate and something extra.|||

I use the easiest/dumbest/cheapest way

Get SPs from both databases, and use free version of examdiff to compare them (or you can use Visual SourceSafe, etc..)

|||

There are several free tools that do this. The one that I use is SQL Effects Clarity CE.

It compares tables, stored procedures, views, functions, users, roles, etc.

You can download it here: http://www.sqleffects.com/sfxClarityDetails.html

Hope this helps.

How to comair DB objects in Development server with Production server

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksThere are many database comparison tools in the market. For example:
redgate.com
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||http://www.aspfaq.com/2209
Also, think about WinDiff if you have Visual Studio installed...
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?

How to comair Database objects

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
Thanks
Hi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>
|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>

How to comair Database objects

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>

How to comair Database objects

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>>I have couple of Stored procedures and UDFs and Tables which I generated
>>an script from our development server and our database admin has run that
>>script on the production server.Unfortunately because of some changes I
>>had to generate the script two more times and now I'd like to make sure
>>that the objects in the producation are same as development.Is there a way
>>to compaur tham and to see what the potential differences are?
>>
>> Thanks
>