October 23, 2001 at 8:06 am
Hello all,
I have a SQL Server 7 database that has several tables in it. They all reside in the primary filegroup. My question is this: 4 of the tables are dropped and recreated daily and do not need to be backed up. What is the best plan of action for doing this? Should I move these 4 tables to a different filegroup and just backup the primary filegroup?
Thanks,
Jason Delaune
October 23, 2001 at 11:35 am
Is backing them up taking a lot of time? I avoid filegroups (just a personal preference) but if you can afford to back them up, why not? Another option would be to put those tables in a separate db, change the logging option.
Just curious - why do you drop and rebuild rather than just truncate?
Andy
October 23, 2001 at 11:51 am
Andy,
The backups aren't taking a lot of time to do. They are 4 semi-large tables that hold read-only data from the mainframe. We have tossed around the idea of putting them in another db, but the developer didn't like that idea (don't know the reasons).
Why the tables are being dropped and recreated is also a thing I don't understand. Like you said, I would just truncate the table. However, since I'm new to the department, the script to do this has already been in place for some time.
Jason
October 23, 2001 at 11:58 am
We have a similar system for mainframe to sql tables in place within our organization. We use bulk inserts to load the data into staging tables and then drop constraints, drop the daily use tables and rename the staging tables to the daily use. This loads about 400mb in a mater of less than 5 minutes. Our web users see no impact. Also, the process of the staging tables keeps a failed load from impacting the user until all is successful.
In the near future I will be implementing filegroup usage for these tables as well because it does afford the luxury of backing up just filegroups vs the whole database. We will capture them at the EOM cycles.
Jody
October 23, 2001 at 4:35 pm
Jody - not bad, staging tables are always handy, although depending on your data source I'd be concerned about the dropping the constraint part. I understand why you do it, but you risk having bad data that will bite you later. My two cents!
Jason, if you've got to do some reconfiguring anyway, I think it makes more sense to move the tables to a different db. Wont take you a lot of effort to move to new db, put a view in your current db to each of those tables and set permissions, then truncate/load. The downside of filegroups (aside from my irrational dislike) is that you'll have to use the same log settings - why log the transactions at all if you're prepared to lose the data?
Andy
October 23, 2001 at 4:59 pm
Thanks Andy,
The reason for the constraint drop is that the staging version of the tables we want built with relational integrity. If the data from the external source does not line up, we fail the load. We have been bitten a few times when the constraints were not dropped and committed before dropping the tables. I think sysdepends got in the way with only a portion of the dependencies having been updated.
The filegrouping allows the settings of read only for one group of tables which is an option we want to manage at the database level but without moving the tables to another database in this instance.
As for the reluctance to move tables to another database, I would suspect that there is a discomfort level within you group, Jason, that doesn't want to cross db join tables. (We have that in our group as well and requires lots of negotiations when we are planning a new structure to implement.)
Jody
October 24, 2001 at 4:17 am
Not entirely unrelated question. We have a db that the developers/users drop once a week, recreate, and add new data to the recreated table.
One of my concerns is the permissions required to do this. As I understand it one has to be a member of Sysadmins to do this. This is not a situation that I'm happy with. Is there a way around this?
October 24, 2001 at 10:10 am
To truncate the table, you must be a dbo. For creating/dropping tables or databases, there are server roles for this, you do not have to be a sysadmin.
I wouldn't use 2 databases for one reason. If you edcide to duplicate this same structure (say another app or client), then you ahve to maintain two codebases, one for each set of databases. Why, can't have two "stage" dbs. You'd have to have "stage" and "stage2". So anything that referenced across databases would have to be maintained as a separate codebase.
I try to avoid different filegroups unless it is for performance reasons, but you can easily place your data on a separate filegorup and not back it up. Be careful that you only have these tables on the filegroup and the separate filegroup is not the default.
If you are reloading the tables daily, why not truncate them before the backup? If you can't, then I'd use the 2nd filegroup approach.
Steve Jones
October 24, 2001 at 12:42 pm
Steve,
Would you please explain the concept of "stage" databases. Also, I thank everyone for their input. I'm glad I'm not the only one who has to deal with these scenarios.
Jason
October 25, 2001 at 5:11 am
Thanks Steve, I found it, DBCREATOR. None of the books I have states explicitly that dbcreator can drop databases. I found it in BOL after soom searching.
David
October 25, 2001 at 8:02 am
Another note on truncate from BOL (SQL2K):
"TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable."
From SQL 7 BOL:
"TRUNCATE TABLE permissions default to the table owner and are not transferable."
Andy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply