September 25, 2007 at 1:57 pm
We have identified some databases that can be retired (no more transactions will be performed against the database) and we are decomissioning the server.
However, we must retain the data should our legal department ever need this data. How would you retain this data?
Regular backup files?
Export to access?
...?
Any suggestions would be appreciated!
September 25, 2007 at 4:26 pm
September 26, 2007 at 3:18 am
in addition I would also export it to some text file(s) in case future sql versions won't support the old database format
September 26, 2007 at 3:02 pm
Jo Pattyn (9/26/2007)
in addition I would also export it to some text file(s) in case future sql versions won't support the old database format
wow I have never thought of that... But if someones data retention policies are "forever" it makes a lot of sence to use text files.
* Noel
September 26, 2007 at 4:15 pm
noeld (9/26/2007)
Jo Pattyn (9/26/2007)
in addition I would also export it to some text file(s) in case future sql versions won't support the old database formatwow I have never thought of that... But if someones data retention policies are "forever" it makes a lot of sence to use text files.
Sure does. I would also put the text files on tape and send it off-site.:)
September 27, 2007 at 4:45 am
IMHO the whole issue of data retention is something our industry is underestimating.
In the old days (before SOX, etc) it was relatively safe to copy old data to tape, send it offsite, and forget it. If the data was needed and the tape was readable, then good, but otherwise no-one was worried. In the same way if anybody still had the business knowledge to understand the data then good, but otherwise no-one was worried.
Nowadays people can get fined or imprisoned if data required by legislation is not available. The increased risks of this mean thought, time and money needs to be spent in mitigating these risks. Tapes often have a useful life of no more than 10 years, likewise CDs and DVDs, although longer-life media is available at extra cost. Business knowledge needed to understand the data is harder to retain. Sometimes data can only be pieced together with an application - but keeping old application install media probably also means keeping old OS install media and the skills to re-install it 🙁
Your business needs to have clear policies on how old data should be delt with. If these are not in place, mail your manager about every archive you do to point out the problems. It may not stop your business being penalised in the future, but it should move the problem away from you.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 27, 2007 at 6:03 am
I agree with Ed... sure, you should be sending backup tapes off site. Sure, you should save some text files and send them offsite. And, I agree... what if MS radically changes the format of the new databases and you haven't also backed up the origianl SQL Server disks, the operating system, etc, etc, etc.
What does it really cost to keep a read-only static server up and running? Practically nothing...
Why retire the server?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2007 at 11:25 am
One option might be to convert the physical machine to a virtual machine and then store the VM on tape for future use if needed? VMWare has a tool to do just that and their low end host/server product is free (http://www.vmware.com/products/converter/).
Joe
September 28, 2007 at 11:56 am
Thanks for the replies. My inital thoughts were back up the database and export it to access - but text is better! We have a system for storing our documents we must retain so tapes/cds are not an issue for me. I was just unsure what format to save the data in before I put it into our repository of retained documents/files.
It does cost money to keep the server around, it is quite a cost savings to retire the server (service agreements, space in the server room, electricity, etc) I agree that we could keep a virtual server, but their is no guarantee that we will ever need the data again and we have many databases that have data that would have to be retained and this wouldn't be a great solution in that case.
Thanks again, and any more thoughts would be appreciated.
October 5, 2007 at 8:18 am
Any idea how to move this data to text files easily. I see I can use the DTS export wizard, but it seems to do this for only one table at a time.
We are looking at a potential of 12 databases with at least 100 tables each, worse case scenario.
October 5, 2007 at 8:24 am
Cat (10/5/2007)
Any idea how to move this data to text files easily. I see I can use the DTS export wizard, but it seems to do this for only one table at a time.We are looking at a potential of 12 databases with at least 100 tables each, worse case scenario.
Nevermind. I remembered I had a tool called DB Artisan and it provides a feature that does this.
October 5, 2007 at 9:03 am
The VM idea is very cool. That's if you have the space for a VM of this size. If you have 100GB of data, not sure you'd do this.
I'd take a backup of the db, put it on 2 tapes and send it off site. In addition, take a copy of the SQL Server install CD, Service Packs applied, and Hot fixes and put those on the tapes. We had to pull back a 5 year old tape of a SQL 6.5 database for a legal matter and had issues finding SP3. Be sure you save the software with the db.
Not sure I like the text version. If the db's of any size, you lose the RI stuff, the procs, etc. That might be needed for legal. Save the backup and software.
October 5, 2007 at 9:25 am
This is where the experience of the DBA really shows and where our profession shines in all it's glory!!
October 11, 2007 at 10:17 am
BCP dump the tables
Save to DVD's
Save to CD's
Make copies of the MDF & LDF files
Pull the hard drives and put in anti static bags and them in Pelican Cases
Ship to a cool dry place off site
or just back up everything and ship the server to off site storage
then if you need it, just plug it in and hope it boots
October 12, 2007 at 3:56 am
Thor is correct - use DVDs or CDs, not tape. Tape deteriorates over time. Mainframe tape libraries normally have an annual process where archive tapes are re-written to overcome this, so unless you can put such a process in place it is better to use silver discs.
Also you should take multiple copies and preferably store them in different locations, and don't forget to test them before you completely trash anything.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply