| Author |
Topic |
|
haneesh
Starting Member
7 Posts |
 Posted - 2009-04-22 : 07:31:15
|
| Hi,i want to do the bcp.I have one db in my server called Test1.I want to copy all the records in to my home computer.i'm using sqlserver.How to do this.??Plz explaine.. With eg |
|
|
pootle_flump
1064 Posts |
Posted - 2009-04-22 : 07:34:53
|
| Any reason you don't just backup and restore? Much easier than BCPing a whole database. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 07:38:18
|
| It's easier to do a backup and restore.If you want to do a bcp then you needexec master..xp_cmdshell 'bcp db..[tablename] out "c:\tablenamne.bcp" -N -Sservername'for each tablesoselect 'exec master..xp_cmdshell ''bcp mydb..[' + TABLE_NAME + '] out "c:\' + TABLE_NAME + '.bcp" -N -Smyserver'from information_schema.tablesFor the bcp in just change the out to in and the server/database.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-04-22 : 07:42:11
|
...and then you've got to create tables in the destination DB and BCP the data in. That's why Nigel and me recommend Backup & Restore  |
|
|
|
haneesh
Starting Member
7 Posts |
Posted - 2009-04-22 : 07:43:21
|
| I need to access these server remotely.. Then what will be the syntax?? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 07:45:56
|
| Depends what access you have.Easier to create the files on the server then copy them to your m/c.The bcp I gave you will do that and so would a backup.Something like ssis or bcp running on your m/c would create the files locally on your m/c.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
haneesh
Starting Member
7 Posts |
Posted - 2009-04-22 : 07:58:35
|
| sorry..i'm not understanding well..k.. Suppose , i have my pc in my home and other pc in my office.Unfortunately .. i'm not in both locations. I'm on another location called "C".Can i able copy all the data from my office machine to my home machine from another location "C", with the use of internet??plz explaine |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-22 : 08:14:40
|
| Thats your choice. Burn it on tapes, access it via web if you are allowed to have access to your office machine from home. Use whatever media and do a restore/bcp to populate your tables in your home pc. |
|
|
|
haneesh
Starting Member
7 Posts |
Posted - 2009-04-22 : 08:17:25
|
| thanx for u'r reply..i doubt is that if i'm using thru web, i need to give the ip of both servers.Then wat will be the syntax for bcp?? |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-22 : 08:22:27
|
| bcp it on your office drive-Copy the files to your home server-Then do a restore/bcp to your tables. |
|
|
|
haneesh
Starting Member
7 Posts |
Posted - 2009-04-22 : 08:29:21
|
| Thanx once again..plz tell me...in below command, whats the "c:\tablenamne.bcp"... what is "tablename.bcp" mean for...not understand its extension.. plz tell me in detail..exec master..xp_cmdshell 'bcp db..[tablename] out "c:\tablenamne.bcp" -N -Sservername' |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-22 : 08:41:00
|
| thats the name of the file where data from table will get copied. You can give it any other name if you want to. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-22 : 08:41:55
|
| you may/may not change the extension to txt,csv.. |
|
|
|
|
BCP