February 5, 2009 at 4:54 am
Hi,
I've looked around for a while now, and I can't find anything about selectively picking tables for transaction logging.
The problem I have, is that I import a vast amount of data from several sources and manipulate this data into some locally stored tables. I also have some application specific tables, users, comments, configuration etc... It is these tables I'd like to maintain under transaction logs, but as for the source system tables I'm not too bothered about as I can reimport the data from the source - obviously this could be time consuming, but the issue with tracking every change has meant the trns log is growing too quickly.
For now we have the database in simple mode with a daily backup. If a database failure did occur during the day, then we'll lose the data entered/modified during the day. not great!
Not sure if anyone has any ideas? I was wondering if it is possible to select tables for transaction logging. Some ideas I've had:
1. Partition tables/log - if a log could be created for specific tables (maybe via partitioning) maybe truncating the other log is doable??? by using:
BACKUP LOG [MYDB] WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE([MYDB],0,TRUNCATEONLY)
But I think this looks at the whole database
2. Move tables required for logging into a new database - only prob with this would be the queries in place would have to change for linked db querying
3. Replicate data from required tables into a separate db - and perform trns logging there and get these backed up. I guess this is possible...
February 5, 2009 at 6:59 am
Your best bet would be to move them to another database that you have a full recovery model on.
To help with preventing code changes you could actually use a synonym to reference the tables in the other database. Synonyms are pretty damn awesome, they've had them in Oracle for ages, to get them in SQL is huge.
http://msdn.microsoft.com/en-us/library/ms177544(SQL.90).aspx
February 6, 2009 at 6:47 am
Thanks Nic,
Totally forgot synonyms, still got my 2000 hat on. Makes logical sense.
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply