Can we backup a single table in SQL Server 2008?
-
Hi,
I just like to know that can we backup a single table in SQL Server 2008?
-
No.
SQL backups are database, filegroup and file. You could, technically, put the table into a filegroup by itself and back that up, but when restoring you'd still need the primary filegroup of the database
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
you could do a logical save of data at the table level by using the bcp utility or an SSIS export.
---------------------------------------------------------------------
-
- another alternative is to just copy that table into another database and create a full backup of that db.
I would not suggest to do it this way all the time, but just in some exceptional occasions.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I like ALZDBA's suggestion, and hadn't thought of that. It adds an extra step to backing up and restoring the db, but that might be a good way to track the backup and restore.
Note that the import/export wizard could help you here, either with moving the data to some flat file, or even moving it to a new database that you back up and restore.
-
Steve Jones - Editor (8/23/2009)
Note that the import/export wizard could help you here, either with moving the data to some flat file, or even moving it to a new database that you back up and restore.This would require an SSIS package to be executed. I believe that there are ways to do that from T-SQL but I've never tried them.
I would execute a small T-SQL script from the maintenance plan (SQL Server 2005 and later) using the "Execute T-SQL Statement Task".
[font="Courier New"]use [BackupDB]
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BackupDB].[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [BackupDB].[dbo].[MyTable]
select * into [BackupDB].[dbo].[MyTable] from [MyDB].[dbo].[MyTable][/font]
I think that this would be faster as well.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply