October 18, 2007 at 7:01 am
Hello,
I could really use someones assistance with trying to archive / truncate a large database I have. Basically I want to be able to truncate by date every table in the database from the last 6 years (upto 2006). I also want to be able to take all of those records and have them in another accessible database in case the client needs to access it. Can anyone outline some of the steps its going to take?
Thanks in advance,
Gary
October 18, 2007 at 7:19 am
Gary
I take it all of your tables have a date column? Do your tables have foreign key relationships to each other? If so then you'll need to make sure you delete from child tables before deleting the parent data. What if your child data meets the deletion criteria but the parent data does not?
Once you've got all that sorted out, you'll want to create the tables in your archive database and then set up transactions to move the data - something like this:
BEGIN TRAN
INSERT INTO ArchiveDB..MyTable ( )
SELECT FROM ProdDB..MyTable
WHERE MyDate < @CutOffDate
DELETE FROM ProdDB..MyTable
WHERE MyDate < @CutOffDate
COMMIT TRAN
If you have a lot of data in your tables then you might want to move your data in batches so that your transaction log doesn't balloon and in order to minimise the risk of acquiring table locks. You should find quite a few threads on this site on how to do that.
Good luck!
John
October 18, 2007 at 7:24 am
Thanks for the response. All the tables do have a date but I am going to have to work on the foreign keys.
October 18, 2007 at 8:52 am
Ok so I am a novice when it commands to SQL commands so maybe you could help me with this one...
Is there a command that I can run to create my ARCHIVEDB and have it be setup the same as another DB? So all that I will have to do is select the data based on data and move from there?
October 18, 2007 at 9:00 am
Two relatively easy ways to do that:
- use DTS' "copy objects". It should duplicate most everything. Then - delete what records you don't want in the archive.
- go into query analyzer, find the table you want to clone. Right-click on it and pick "script to new windows as ..create". Change the name of the table to something new, and run the script.
I also sometimes resort to
select * into newtable from oldtable where 1=2
the WHERE statement would give me a new, empty table. That can then be populated with whatever you want.
the QA and SELECT INTO methods do single tables. The DTS would allow you to do a whole DB (or rather, all objects in DB.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 18, 2007 at 9:03 am
Gary
The easiest way to do it is to right-click on your existing database in Enterprise Manager and choose All Tasks, Generate SQL Scripts, and use the dialog box to choose the objects you want to have in your new database.
Then you can change the database name in the script, and run it to create the new database and tables, procs, views etc.
John
October 18, 2007 at 9:11 am
Thank you very much for the responses! Im going to try it right now and will let you know how it works out.
THANKS!
October 18, 2007 at 3:31 pm
John Mitchell (10/18/2007)
GaryThe easiest way to do it is to right-click on your existing database in Enterprise Manager and choose All Tasks, Generate SQL Scripts, and use the dialog box to choose the objects you want to have in your new database.
Then you can change the database name in the script, and run it to create the new database and tables, procs, views etc.
John
Whenever I do this it creates the Archive DB but when the job completes it doesnt create the same tables as the hostdb and deletes all of the data from the hostdb... I have tried a few different variations and have gotten the same result. I tried the other method of using DTS which creates the archive db and creates the tables but moves NO DATA into the tables... Im really confused now.
October 19, 2007 at 3:43 am
Gary
I hope you took a backup of hostdb before you started!
Please will you post all scripts you ran so that we can try to see what went wrong.
John
October 19, 2007 at 7:01 am
John Mitchell (10/19/2007)
GaryI hope you took a backup of hostdb before you started!
Please will you post all scripts you ran so that we can try to see what went wrong.
John
I can send in copies of the scripts, thats not a problem. The hostdb actually is a backup loaded onto another server so no worries about actually losing data right now. I will post one of my scripts in a little bit.
October 30, 2007 at 2:49 pm
Well Ive started back on this project and have gotten a descent amount futher. I have my archive db created with a cloned schema of the original. Now I am trying to move the data that I have between the 2 dbs tables and apparently I am missing something
BEGIN TRAN
INSERT INTO OplatesStagingArchive..ACHBatches ( )
SELECT * FROM OplatesStaging..ACHBatches
WHERE ProcessDate < '@1999-01-01'
If I try to put in a date like
WHERE ProcessDate < '@1999-01-01'
I get
Server: Msg 208, Level 16, State 3, Line 2
Invalid object name 'OplatesStagingArchive..ACHBatches'.
Dont understand thats a valid object.... Unless I have screwed up somewhere else and dont realize it.
October 31, 2007 at 3:36 am
Gary
I don't think those parentheses are valid syntax, although that doesn't explain the specific error that you got. Have you checked which user owns the table ACHBatches in the OplatesStagingArchive database? If it is, for example, MyUser, then you should qualify the table name like this: OplatesStagingArchive.MyUser.ACHBatches.
John
October 31, 2007 at 7:20 am
BEGIN TRAN
INSERT INTO OplatesStagingArchive.dbo.ACHBatches
SELECT * FROM OplatesStaging.dbo.ACHBatches
WHERE ProcessDate < '@1999-01-01'
This is what I got as a result. I had the same error with the ( ) in so I took them out and tried just to see what error I recieved.
Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'OplatesStagingArchive.dbo.ACHBatches' can only be specified when a column list is used and IDENTITY_INSERT is ON.
October 31, 2007 at 7:41 am
Ah, now that's a different error message. If a column in one of your tables has the IDENTITY property, that means that the value in that column is automatically generated when you insert a row. Therefore you can't specify the value in your INSERT statement. You have two options: first, you can remove the identity column from your INSERT statement. If you do this, bear in mind that this will invalidate any foreign key columns that reference this column. Second, you can use SET IDENTITY_INSERT statement to override the identity property. You'll find more information on this in Books Online.
Since this is unlikely to be the only table in your database with an identity column, you can identify the others with this query:
SELECT object_name(id) AS TableName, name AS ColumnName
FROM syscolumns
WHERE autoval IS NOT NULL
John
October 31, 2007 at 8:02 am
Running this command provided no results.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply