How to document an archived database? [closed]

Question

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 ...?

Answer 1

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.