While working on a SQL script to more reliably purge older copies of backup files than using
xp_delete_file, I noticed the following about the created vs. last modified dates:
- When a file is first created, the creation, last modified, and last accessed dates are all the same.
- When a file is copied to a new location, the creation date becomes the date and time that the file was copied to that new location while the last modified (or last written) date remains unchanged. (Note that the content did not change as a result of the move, just the location.)
- Renaming a file does not change the creation or last modified date.
- Viewing the properties of a file on a Windows drive does not change any of the associated dates.
- Viewing the properties of a file on a CIFS share on a non-Windows host changes the last accessed date.
These are important behaviors to consider when using the creation, modified, and last accessed dates in your scripts.