Posted: 9/20/2011 7:20:20 AM
Is there any process from which we can take backup of only one table from complete database in sql server 2000/2005/2008.
Posted: 9/20/2011 8:33:28 AM
As far as I know it's not possible....
The best way is to use
SELECT * INTO [NEW_TABLE] FROM [BACKUP_TABLE]
Please let me know incase it's possible by some other means....
Posted: 9/20/2011 8:33:30 AM
Posted: 9/20/2011 9:50:53 AM
But i am searching for proper backup of one table and restore as new database.
Posted: 9/20/2011 10:23:19 AM
Move your single table to a new filegroup and backup the filegroup and while restoring restore only the filegroup.
Posted: 9/20/2011 10:49:11 AM
Thanks a lot but can you please explain the above in points.
I would like to know how to change the filegroup of a single table.
Thanks in advance.
Posted: 9/20/2011 4:27:08 PM
As per my understanding, we can do this..................
Query for that -->
Create a blank database "NewDbName"
Create Database NewDbNameSelect * into NewDbName.dbo.TblName from CurrentDbName.dbo.tblName
After executing this query, backup database NewDbName which have only one table you need.
Hope This Solution will work for you.
Posted: 9/21/2011 4:55:14 AM
-- File Group and FileUSE [master]GOALTER DATABASE [Training] ADD FILEGROUP [FG_Secondary]GOALTER DATABASE [Training] ADD FILE ( NAME = N'FileOnSecondary', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\DATA\FileOnSecondary.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG_Secondary]GO-- Create table in specific file groupCreate table test (i int) on FG_SecondaryNow whne you take backup of FG_Secondary file group, backup of table test will be taken and you can restore the backup of file group to your target database.
NOTE: [Training] is the database name. Your table has to be in specific file group. If your database does not has multiple file groups then you need to alter the database and add file group and files. HTH!Rakesh
Posted: 10/27/2011 5:00:08 PM
can you please check this with an example, i think this is not possible: " you can restore the backup of file group to your target database"
Let me re-phrase what you said (pls. correct me if i am wrong)
1. Create Database
2. Add FG
3. Add DATAFile
4. Create Table in User defined FG
5. Backup User Defined FG.
6. Restore another Database Say DB3 and now Restore User Defined FG of DB1 in DB3?
I think i am not at the same page.
Sarabpreet Singh Anand
All code is provided "AS-IS" with no warranties.