August 23, 2009 at 1:41 am
Hi,
I just like to know that can we backup a single table in SQL Server 2008?
August 23, 2009 at 2:54 am
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 recoverability
August 23, 2009 at 5:40 am
you could do a logical save of data at the table level by using the bcp utility or an SSIS export.
---------------------------------------------------------------------
August 23, 2009 at 6:18 am
- 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
August 23, 2009 at 9:12 am
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.
August 25, 2009 at 7:24 pm
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