December 4, 2007 at 7:25 am
I have a database which essentially consists of tables that are event logs, i.e., each row represents an event along with the time of its happening. Now, over a period time these tables start to contain millions of rows, and hence my reports/queries based on these tables get slower and slower. Hence, I'm implementing a backup procedure which will backup rows between a given time period to a disk file. I also want an option to restore the data in the disk file into the database as and when required. To achieve this, I have the following strategies in mind -
1. A custom made application which queries the database, writes the data to the file, then deletes the rows written to the file.
2. Use bcp to out the data from the table according to a specific query, and then use bcp in to restore it back.
I feel that the second option would be efficient and faster, but I do not want the disk file to be ASCII, as they would become bulky. Hence, I prefer the native format. But, is there any way to know the structure of the table from a native data file? Because, otherwise I would not be knowing what was the table structure like when the data was bcp'ed out. One way would be to create a format file also during the out operation. Any other suggestion? Also, once the data is bcp'ed out, how what is the fastest way to delete those rows from the database, without filling up the transaction log? Isn't there a BULK DELETE???
My question is that are there any better and faster ways to achieve this requirement of mine? My primary target would be databases running on the Express Edition, hence size and performance do matter!
December 4, 2007 at 7:29 am
Haven't you looked at partitioning data in tables and indexes?
--Ramesh
December 4, 2007 at 7:35 am
See the below link, it may help to you:
Microsoft SQL Server Database Publishing Wizard:
------------------------------------------------
🙂
December 4, 2007 at 7:49 am
TRUNCATE command
is the fastest way to delete ALL the rows from a TABLE, without filling up the transaction log.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply