Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

closed as primarily opinion-based by Mark Storey-Smith, Paul White, Max Vernon, RolandoMySQLDBA, Shawn Melton Dec 11 '13 at 4:53

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

How about the date of the backup? The version(s) of the data schema(s)? Info on references to linked databases? Any DDL/DML scripts needed to get it operational after performing a restore? – FrustratedWithFormsDesigner Dec 10 '13 at 16:29
I can think of server login(s) related to database users – Joy Walker Dec 10 '13 at 17:26

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.