Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
inancgumus
Starting Member
40 Posts |
 Posted - 2004-08-18 : 06:36:23
|
| I have partitioned a table into ~50 mini tables each has ~50 millions of records. I want to make BACKUPs for each mini table into their own files. And after BACKUP I would put them in tapes and will drop each table (or detach, is there any trick?) from the database to save space. Then if I need one of them (each mini table represents one distinct concept, and has a constraint on some foreign key) later for the reporting purposes I would just do RESTORE on the one that I have interested into the production database without making a FULL RESTORE. Is this possible ?Additional Note: I cannot use filegroups cause there is a limit 256 max and neither they do not permit to put the database tables into their specific files. I would have more than 50 mini tables in the future (I think it would be ~400).""Also"", if I make it with filegroups BACKUP/RESTORE, it would suck big time because the size of the tables which filegroup would be pointed! So, I have to prevent it with storing tables in their own files and doing backup/restore on them as the same logic with attaching/detaching databases. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-18 : 06:40:39
|
| I would use BCP to copy out the contents of each table - this would be the quickest option and should be easy enough to implement. Once you've bcp'd the data out you can drop the table.To re-create all you need to do is run the create table script then the bcp / bulk insert command to get the data back in.You shouldn't need to resort to Backup/Restore for this. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-18 : 08:11:39
|
| Why not put each "mini table" into it's own database for Backup / Restore.You can have up to 32767 databases...But I would probably just backup the database and then drop it.Restore on a "need to report" basis./rockmoose |
|
|
|
|
|
|
Attach/detach tables