Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    
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
add comment

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.

1 Answer

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
add comment

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