backup of only one table from Database

Who is online?  122 guests and 0 members
home  »  forums   »  microsoft data platform   »  sql server & bi   » backup of only one table from Database

backup of only one table from Database

Topic RSS Feed

Posts under the topic: backup of only one table from Database

Posted: 9/20/2011 7:20:20 AM

Lurker 69  points  Lurker
  • Joined on: 6/4/2011 11:39:17 AM
  • Posts: 12

HI Friends,

Is there any process from which we can take  backup of only one table from complete database in sql server 2000/2005/2008.

 

Regards,

mkumar

 


Posted: 9/20/2011 8:33:28 AM

Lurker 10  points  Lurker
  • Joined on: 3/15/2011 8:55:50 AM
  • Posts: 5

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

Lurker 10  points  Lurker
  • Joined on: 3/15/2011 8:55:50 AM
  • Posts: 5

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 9:50:53 AM

Lurker 69  points  Lurker
  • Joined on: 6/4/2011 11:39:17 AM
  • Posts: 12

Thanks NepSter,

But i am searching for proper backup of one table and restore as new database.

 


Posted: 9/20/2011 10:23:19 AM

Starter 605  points  Starter
  • Joined on: 3/14/2011 4:21:32 PM
  • Posts: 90

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

Lurker 10  points  Lurker
  • Joined on: 3/15/2011 8:55:50 AM
  • Posts: 5

Hi Sachin,

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

Lurker 2  points  Lurker
  • Joined on: 3/16/2011 9:53:13 AM
  • Posts: 1

Hi Friends,

As per my understanding, we can do this..................

Query for that -->

Create a blank database "NewDbName"

Create Database NewDbName
Select  *  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.

 

Thanks

Vikas Singhal


Posted: 9/21/2011 4:55:14 AM

Lurker 237  points  Lurker
  • Joined on: 6/7/2011 12:17:24 PM
  • Posts: 11

-- File Group and File
USE [master]
GO
ALTER DATABASE [Training] ADD FILEGROUP [FG_Secondary]
GO
ALTER 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 group
Create table test (i int) on FG_Secondary


Now 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

Contributor 1209  points  Contributor
  • Joined on: 3/15/2011 5:38:06 AM
  • Posts: 77
answered  Answered

Hi Rakesh,

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.


Page 1 of 1 (9 items)