January 28, 2003 at 2:47 pm
What is the best way to backup a table or table(s) in a database rather than backing up the whole database?
Thank you.
January 28, 2003 at 2:51 pm
Suppose DTS to Access, text, comma delimited.
If its a small table there are some sps on web to script the data from a table, even with a if exists on primary key.
Example insert tablename etc.
January 28, 2003 at 4:30 pm
Using bcp is also an option. Probably faster than DTS. However, it's not as intuitive and you'll want to test your scripts to backup and restore before relying on them.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 28, 2003 at 6:06 pm
If it's ad hoc, DTS. Regularly, use BCP.
Steve Jones
January 30, 2003 at 3:54 pm
One method I regularly use -- I have a database called DBObjCopy on each server. When I need to do an ad hoc backup of a table I'll simply do a select * into. For example:
SELECT * INTO DBObjCopy.dbo.mytable_013003 FROM dbo.mytable
I append a date to the object name and then clean up the object once a month. The reason I put it in a different database is to keep the real database tidy.
January 31, 2003 at 9:41 am
An another option is, but this is more tricky, is to separate your table to backup on separate filegroups (and therefore separate files).
So you can backup and restore your database files separatly.
Of cours it makes only sense if you dióon't have to many tables or if the tables are big enough to do this.
But in this case (if your tables are huge) you can even have a performence improvement by separating the most used tables on separate physical disks.
Bye
Gabor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply