August 9, 2011 at 9:17 am
Hi,
I am newby in the table partitioning, and just working on to create a strategy in this area. We have a big table which we will partition by months. We will have a separate partition for each month. The old data in the table is never updated. We cannot have all of the data online due to disk space limitations, so we would like to archive the old data to tape. We keep only 3 months online. Of course partition related tasks should happen with minimized data movement. However, once we probably will need to get back the old archived data into the database again.
The database is in Full recovery mode. Most of the data (99%) in this DB is in the partitioned table. The DB is used 24/7. SQL Server is Enterprise Edition.
It is clear how to partition a table, how to create the staging table for switch out, but all the documentation I found does not contain instructions about what to do AFTER the switch out happened?
Could you please help me:
Is it possible at all to restore the archived data back into the live database, or do we need to maintain a dedicated DB for this purpose?
What is the recommended backup and restore strategy for this scenario for the filegroup containing the staging table and for the database itself?
Many thanks for your comments and recommendations:)
August 10, 2011 at 5:57 am
ah - that's the one limitation with the switching - I always wish you could switch to another database! You're still stuck with the delete/copy.
What you need is a backup for each month you want to delete. So it's painful but - backup your database - restore it somewhere else - drop all the tables except your month to archive - backup the database again and store.
I also use page compression on archived partitions / partitions I don't use very often - but that depends upon your data of course.
I have a paper on partitioning you might find interesting, based on my work and a presentation I did
http://www.grumpyolddba.co.uk/gonein60ns/GoneIN60nsWeb.pdf
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 10, 2011 at 7:24 am
The pdf link works but nothing downloads.
August 10, 2011 at 7:35 am
For me the URL was working after copying to the browser (clicking was not wokring).
It is quite disappointing if there is no other (faster and easier) way to archive and switch back efficiently the switched out partition/staging table.
But I hope maybe somebody else can give a hint or workaround.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply