Post reply

Drop it like a hot potato! / Best practices for nixing old databases

  • September 21, 2005 at 2:51 pm

    #187812

    I'm looking for some advice on getting rid of old, unused databases.

    We have 3 main SQL Servers here. Two are used for production, 1 for development.

    There are at least a couple of old databases on the dev server. I've been told they can be deleted, but I want to keep them around just in case.

    I'm thinking about doing something like this:

    1 - backup database

    2 - shrink/compress database (how is this normally done? IS this normally done?)

    3 - take database offline

    4 - detach database

    5 - burn backups AND db files onto media (CD/DVD or external drive)

    Does this make sense? Is this ideal? Any suggestions?

    Thanks,

    Greg M.

  • September 22, 2005 at 5:27 am

    #592147

    Sounds like a good plan of action, although I wouldn't do it all in one go. I've been caught like that before.

    One day: "Yes, yes, yes. Get rid of that database we don't need it anymore"

    Following day: "Ahhh ... have you still got a copy of that database? We forgot that theres some stored procedure code we need."

    My advice take the database offline for a few days. If no-one comes running and nothing fails archive backup files, or the database to media, you don't need both.

     

    --------------------
    Colt 45 - the original point and click interface

  • September 22, 2005 at 2:05 pm

    #592391

    Here's my process:

    • make a full backup
    • script out the schema (sometimes even bcp out the data table by table - accounting departments love this)
    • stick the database in 'read only' mode for a week if a development server
    • stick the database in '/read only/single user' for a few days if a production server (you'll know pretty quickly if it's needed)
    • make another full backup
    • detach the database (leaving the mdf/ldf files present)
    • wait 1 week
    • talk to the backup person and get the tapes
    • do a test restore from each backup
    • keep the backup tapes for a 'standard' archival retention period (in our case 7 years)
    • then blow it all away

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 22, 2005 at 2:47 pm

    #592408

    Rudy, I like your answer, but could I start at the bottom of your list, and work my way back up?

    ;o)

  • September 23, 2005 at 8:13 am

    #592622

    The only little problem with taking databases offline is if you have automated backups such as Veritas where you've checked off  "Backup all databases".  You'll get an error with the database that's off line.  Not really a problem, but it can be annoying to look at.

  • September 23, 2005 at 9:14 am

    #592637

    It sounds like you are using the Veritas SQL Agent and not doing 'true' backups. At our shop, and many in the pasy that I have worked at, we have always backed up to disk and let Veritas snatch up the disk files. Also, I'd be wary ... In the past I've evaluated a number of 'agent' type backups and was almost always able to achieve a 50% failure rate on restore !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 23, 2005 at 9:42 am

    #592640

    Yes, we're using Veritas SQL Agent for most of our backups, but haven't had any problems with restores, not that we've done that many. I am doing SQL backups to disk as well for our critical production databases, and then dumping those .BAK files to tape afterwards.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply