September 11, 2008 at 10:03 pm
I need to build a db so that I can store the drive, path, file, size, last modified date of files that have been saved/backed up. I then will want to compare this data to current files to determine which files have changed or have been added so an incremental backup be made. Performance will be important!
I think that SQL Express will do the job, but is it the best choice for a low/no cost db for this task? This may seem pretty straight forward, but I haven't got experience with this db and I want to get some feedback before I barge ahead with an attempt at a solution.
I would think that one table with the above info would be a good start. But, what is the best way to index the data and are there any alternatives to one file - one query type of structure? If I follow that type of path I am guessing that it could be pretty slow. Any feedback would be appreciated!
Regards,
TH
September 12, 2008 at 12:06 am
Hello,
As you have posted to an MS SQL Server forum you will get a slightly biased answer as to your choice of DB Server. I would of course recommend MS SQL Server - I hope Bill reads this 😉
For the DB Design, you could consider separate tables for Drive, Path and File, with One-to-Many relationships from Drive to Path and from Path to File. That would be considered more "normalised". It would hopefully also improve query performance e.g. if you just wanted to search for files in a specific path (assuming you have clustered indexes on the tables).
I would suggest using a surrogate key for the Path and probably also the File tables e.g. an Identity column, as indexes on lenghty character columns reduce performance.
That's my penny's worth.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 13, 2008 at 3:56 pm
Thanks for the feedback. I think that you are right about the structure and will plan accordingly. Could you give me a little more info on the indexing part-maybe a web reference or something where I can learn some more. Thanks for your help!
Tom Hall
September 13, 2008 at 8:06 pm
Had a project using Windows 2000 which was using an ACCESS DB rather than SQL server, performing much the same task as you have outlined. One problem was discovered when here in the United States we had that eventfully day when we switched from Standard Time to Daylight Savings Time.
By gosh the time portion of the date modified was altered on all existing files on the computer. Creating a large backlog of files to be processed. Now this was a few years back but believe that Microsoft still uses the same technique to present the time portion of the last modified date/created date for a file. Suggest you check this. If still true then if you intended to store the modified date/time may I suggest using the SQL Server function GetUTCDATE and save its output in your table and when comparing current data to your saved data use it to convert the currently read information to UTC date before comparison with your saved data.
From Books Online (BOL) - bold text my editing
Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.
September 14, 2008 at 6:10 am
Hello Tom,
I would suggest first looking at a few topics in SQL Server's Books Online e.g. "Using Clustered Indexes". You (almost) always want one of these on each table; more often than not on the table's natural key e.g. Filename in the File table.
Also the topics "PRIMARY KEY Constraints" and "FOREIGN KEY Constraints" will show you how to implement the One-to-many relationships.
You could also take a look at MS's SQL Best Practices Page for general help on design topics: http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx
Regards,
John Marsh
PS: I did also have one more thought - that you might want to consider a separate "Server" table if you currently have, or expect to cater for, a large number of Servers. If not then storing the Server name as part of the Drive table shouldn't degrade performance.
www.sql.lu
SQL Server Luxembourg User Group
September 14, 2008 at 6:41 am
Once again thanks! I am already reading/researching these areas, but your suggestions give me specifics and sources that will help.
Regards,
Tom Hall
September 14, 2008 at 6:44 am
That is one "got ya" that I hadn't considered. Thanks for the heads up and the solution. This forum is the "BEST"!
Regards,
Tom Hall
September 14, 2008 at 8:26 am
Late responding, but SQL Express should work fine.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply