backup a single table ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

backup a single table ?

Author

Topic

ihadba
Starting Member

5 Posts

Posted - 2008-04-16 : 16:18:09

Is there a tool out there to backup only one table at time
in SQL Server 2005 ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-16 : 16:21:40

No really. There are lots of post on this issue. Use export/import wizard to transfer data to other table with different name. Apply constraint after that.

ihadba
Starting Member

5 Posts

Posted - 2008-04-16 : 16:22:11

quote:
Originally posted by ihadba

Is there a tool out there to backup only one table at time
in SQL Server 2005 ?

Sorry, What I need is a method to backup one table. I want to be able to place the backup in a CD or other server to be used when
the disk fails

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-16 : 16:26:06

I would do transactional replication if data has to real-time sync. Otherwise export the table to other server.

ihadba
Starting Member

5 Posts

Posted - 2008-04-16 : 16:55:01

So how do you do the export to another server.
What menu item should I start with ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-16 : 16:57:17

Read BOL. Database-tasks-export/import wizard. Do you know which database that table is going to in another server.?

ihadba
Starting Member

5 Posts

Posted - 2008-04-16 : 17:52:17

Here is what I did
1. Use Export followed the steps and created a comma delimitted
file. Worked fine! 
2.
Select * into backup_providers from providers 
3.Delete from backup_providers 
4.Use the import Wizard to import the data.
5.Hmm.. I get the following error:

Error 0xc0202004: Data Flow Task: The number of columns is incorrect.
(SQL Server Import and Export Wizard)

6. I made a copy of the exact table, then deleted the data
and tried to import using the flat file. Why does it fail ?

ihadba
Starting Member

5 Posts

Posted - 2008-04-16 : 17:56:03

Please ignore my previous reply
Here is what I did:
1. Use Export followed the steps and created a comma delimitted
file. Worked fine!
2.Select * into backup_providers from providers
3.Delete from backup_providers 
4.Use the import Wizard to import the data.
5.Hmm.. I get the following error:

Error 0xc0202004: Data Flow Task: The number of columns is incorrect.
(SQL Server Import and Export Wizard)

6.I made a copy of the exact table, then deleted the data
and tried to import using the flat file. Why does it fail ?

The backup table has the same structure as the original table used for creating the backup ( export ) file.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-16 : 17:56:27

quote:
Originally posted by ihadba

quote:
Originally posted by ihadba

Is there a tool out there to backup only one table at time
in SQL Server 2005 ?

Sorry, What I need is a method to backup one table. I want to be able to place the backup in a CD or other server to be used when
the disk fails

You should be backing up the entire database then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-16 : 22:18:37

>> What I need is a method to backup one table.

Put table in its own file group then backup that file group.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources