How to document an archived database? You've stored the backup file in a safe place, but what kind of info do you store with it?


database type (SQL Server)
compatibility level (SQL Server 2008)
application name
application owner 
application version


And ...?
I have designed  a database archiving process - which is a semi automated process. 

Below are my thoughts :

Note that you have to develop your custom solution based on your organizational needs


Create a server level trigger to track DROP database activity and log that activity in a table. Now if you are talking about only backup files, then you can do the same without a trigger once the backup is finished to update the table with relevant info.
Below is the possible table structure with columns


ID => this will be identity column
TRIGGER_NAME => the name of trigger that got fired in response to the event 
COMMENT => Friendly comment for the command that got issues e.g drop database was issued 
EVENT_TYPE => the event type that occured e.g. DROP_DATABASE
OCCURED_DATE => the datetime when the event occured
SERVER_NAME => the host name 
SERVER_INSTANCE_NAME => sql server instance name
PATCH_LEVEL => version info from serverproperty or you can just use @@version
LOGIN_NAME => Login that issued drop command
DATABASE_NAME => The database that was dropped 
DATABASE_VERSION => If sql 2005, 2008, etc or you can use the compatibility level as well
COMMAND_TEXT => The command that was used for the event e.g. drop database test_drop 
TYPE => I use this to differentiate between if the backup stored will be on DVD (if small) or TAPE. This is manual bit to be updated later.
TRACKING_NO => If a tracking number is assigned or a request number can be used e.g. JIRA request or HelpStar request or a TFS request number. This is manual bit to be updated later.
APPLICATION_VERISON => If your database is storing application specific version number e.g. BUILD_NUMBER or database schema version.
NOTIFIED => This will be default to 0 and will be updated to 1 once the DBA team is notified by an email. Once it is set to 1, the next time the job runs, it will just send out new records.
You can add more if needed !!



The key here is EVENTDATA() which has a lot of information. 

Remember that triggers (whatsoever) cannot be used for backups. For backups, you have to tailor your own custom solution, but the idea remains the same.