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

Question

Post reply

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

sqltung

Hall of Fame

Points: 3962
More actions
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.

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

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

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

sqltung Hall of Fame Points: 3962 More actions · Answer 3

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

;o)

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

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.