Anyone have experience with SYSFILES?

According to Microsoft, sysfiles.status:

0x80 = File has been written to since last backup.

Unfortunately, this query is not returning what I expect. That is, if I have inserted/updated/deleted any records, I belive that some underlying file must be written to.

So, I tried:

Code Snippet

select * from sysfiles where status & 0x80 <> 0

I updated several fields in my database, doubled and tripled the size etc. I can't get the above query to return any results.

Does anyone have any experience with this particular flag? I was hoping to check the sysfiles and know if a backup was needed or not.

Thanks mucho.

Interesting. I just tried this with 7.0, 2000, and 2005, and that bit is never set. And 6.5 uses sysdevices rather than sysfiles. I wonder if somewhere in the development cycle they decided not to implement that, and it was never taken out of the documentation drafts. Either that or it's a very long standing, little-known bug. ;-)

There might be some ways to take advantage of the differential changed map to determine if a database has changed since the last backup, but the only way I know to read it isn't well suited for inclusion in a batch job.

Code Snippet

DBCC TRACEON(3604)
DBCC PAGE(databasename, 1, 6, 3)

Granted, a database larger than about 4 GB is probably going to have more than one dcm page, and I'm not entirely sure how to determine where subsequent pages are located.

Any Good Book Recommendations?

I know basic SQL and have passed two Oracle Exams, SQL and PL/SQL Programming. Ironically, my first (and only job unfortunately) was in MS SQL Server 2000. I want to know of a good overall SQL Book that is beyond beginner level. Something that answers questions like "How do you get rid of duplicate records in a table?", etc and other practical, real world issues. Is there such a text? It can be Oracle or MS or whatever as long as the theory is sound. Thanks.

ddaveWhile not a book, here are some good SQL sites:

Introduction to Structured Query Language
http://w3.one.net/~jhoffman/sqltut.htm
"Comprehensive SQL tutorial."

SQL Intrepreter & Tutorial With Live Practice Database
http://www.sqlcourse.com

SQL Tutorial
http://www.w3schools.com/sql/

aboutSQL by John Ashenfelter
http://www.oreillynet.com/pub/ct/19
"Provides information on SQL keywords, functions, tricks, and hacks"

SQLTeam.com
http://www.sqlteam.com
Site is mostly about SQL Server and includes forums.|||paul, the jim hoffman tutorial used to be the only decent sql tutorial on the web

but jim hoffman disappeared, and so did his site, a couple years ago

you can still get it at archive.org (http://web.archive.org/), where the latest version is November 2001, 4.76 (http://web.archive.org/web/20011116021648/http://w3.one.net/~jhoffman/sqltut.htm)

ddave, you may find some more tutorials on my SQL Links (http://r937.com/sqllinks.cfm) page