March 11, 2010 at 12:24 pm
Please Help....
I have a database that I inherited that is about 800GB and is currently spread across 24 data files to keep it somewhat manageable... The DB is used as an archive for online application logs. We are currently trying to implement a data retention policy on this data but that decision is slow getting through our senior management and now we are running into disk space issues.
That being said, I wanted to know if there is a way to detach files from the database so I can archive them someplace and if needed re-attach the files at a later time?
I've tried a couple different scenarios but nothing has really worked the way that I would like.
Scenario 1 - I tried to detach the database, move the files that I don't want to re-attach and then re-attach the db only specifying the files I want to attach and it errors. The Primary file still holds this info and it can't find the files.
Scenario 2 - I tried detaching the db, copying the files that I want to archive, re-attaching the db with all files and then removing the files the I don't want (Alter DB T-SQL). That works but I can't re-attach the files because they have been removed from the database. The only option is to restore from a backup.
Scenario 3 - I tried to restore the files from a backup that I took but the db is set to simple recovery due to its size. I can't set it to full recovery because of the database size and the amount of data that is written to this db everyday.
Any ideas????
Kevin
March 11, 2010 at 1:06 pm
Yes, you cannot attach without primary file group.
This is what i would do
1: Create new database with same objects and schema.
2: If you have BI team ask them to create SSIS package to archive data from current database to archive database.
3: Truncate the data in original database after you had moved data to archive database, set up this as a job run once a week or BI team can run this every night after archiving data.
March 11, 2010 at 1:33 pm
This database is the Archive database. There is data in the archive database back to 1998...That's why we are working on a retention plan. My issue is temporarily removing the really old data files and then restore them if they are needed until the retention plan is in place.
By temporarily removing them I can free up some disk space. This database is still growing daily as data is archived here. Not to mention this should help to get this data purged once the retention plan is in place. Because I really doubt anyone looks at what we logged in 1998 anyway....the systems that logged it aren't even in place anymore
No one wants to delete data...you know what I mean...
Kevin
March 11, 2010 at 2:08 pm
Then i would just expand drive space, Ask Server Team it wont be hard to expand drive for them, Disk is the cheapest resource among all other resources.
Do you have any unused space on this database?
March 12, 2010 at 6:49 am
Unfortunately it isn't an option at this time. We are in the process of upgrading our SAN but that is still months away. So...I guess there isn't an option to detach single files.
I guess I could try moving some files to a different shared drive on the SAN.
Kevin
March 12, 2010 at 8:03 am
What about the unused space on this database?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply