April 19, 2010 at 11:52 pm
Hi Guys,
I am currently undergoing a process of archiving an area of an enterprise-scale database (100+ tables 500+ stored procs). The software I am using is SQL Server 2005 standard edition.
This process includes moving all items from a table (we'll call it "Task") that have a [field value] DueDate < 2006 (i.e., the are old) to the archive DB. There are about 12 tables that depend on Task and these tables (and their data) need to be shifted too.
The shifting consists of setting up schema in the new archive database and then moving old data from the active DB to the archive DB (for the Task table and dependant tables). Then when the shift is complete the old data that was moved copied from the active DB is deleted.
What is the best way to do this in your guys opinion? I am thinking generating a script to create the schema for the tables and I think I can write scripts for transfering the data to each table. I also need to copy over all other database objects that SELECT from Task (and dependant tables) to the new DB too, only objects that SELECT from Task and related tables as required as the archive database will be readonly.
Finding all stored procs that only select from the Task table will be the biggest pain as I am not so sure how to go about doing this.
Any ideas would be much appreciated 🙂
Thanks
April 20, 2010 at 3:14 am
Hi
For the sp that use the table you can run the following script.
select name from
sys.sql_modules join
sys.objects on sys.sql_modules.object_id =
sys.objects.object_id
where definition like'%tablename%'
order by name
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
April 27, 2010 at 9:23 pm
Cool thanks for that 🙂
I managed to write a massive script that did everything
:whew:
Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply