Problem with bcp

Hi all,
I'm using bcp from the command line to backup and
restore the data in a table.
To backup, I run a command like this:
bcp TestDatabase.dbo.tblTest out C:\test.txt -n
-S TestServer -U sa -P pwd
and this works fine. However, when I try to restore
the data that I have backed up by doing this
bcp TestDatabase.dbo.tblTest in C:\test.txt -n
-S TestServer -U sa -P pwd
I get the following error:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near 'varchar'.
Does anyone have any idea what the problem might
be?
TIA,
--
Akin
aknak at aksoto dot idps dot co dot ukuse the -n switch so you get a native dump.
Then, you'll have no trouble restoring as long as the schema is the same.
Otherwise, you've got to use format files.
(BTW, as a side not, BCP is not a backup utility).
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> Hi all,
> I'm using bcp from the command line to backup and
> restore the data in a table.
> To backup, I run a command like this:
> bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> -S TestServer -U sa -P pwd
> and this works fine. However, when I try to restore
> the data that I have backed up by doing this
> bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> -S TestServer -U sa -P pwd
> I get the following error:
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near 'varchar'.
> Does anyone have any idea what the problem might
> be?
> TIA,
>
> --
> Akin
> aknak at aksoto dot idps dot co dot uk
>
>|||Hello James,
Thanks for your reply.
If you see the command line I typed out, you will see that I
*am* using the -n switch, but the error still occurs.
Any other ideas?
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> use the -n switch so you get a native dump.
> Then, you'll have no trouble restoring as long as the schema is the same.
> Otherwise, you've got to use format files.
> (BTW, as a side not, BCP is not a backup utility).
> James Hokes
>
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > Hi all,
> >
> > I'm using bcp from the command line to backup and
> > restore the data in a table.
> >
> > To backup, I run a command like this:
> >
> > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > and this works fine. However, when I try to restore
> > the data that I have backed up by doing this
> >
> > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > I get the following error:
> >
> > SQLState = 37000, NativeError = 170
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near 'varchar'.
> >
> > Does anyone have any idea what the problem might
> > be?
> >
> > TIA,
> >
> >
> > --
> > Akin
> >
> > aknak at aksoto dot idps dot co dot uk
> >
> >
> >
>|||Doh!
Thanks for not flaming - chalk one up to 'reading too fast, eh?'
Now I'm well and fully stumped.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> Hello James,
> Thanks for your reply.
> If you see the command line I typed out, you will see that I
> *am* using the -n switch, but the error still occurs.
> Any other ideas?
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > use the -n switch so you get a native dump.
> > Then, you'll have no trouble restoring as long as the schema is the
same.
> > Otherwise, you've got to use format files.
> >
> > (BTW, as a side not, BCP is not a backup utility).
> >
> > James Hokes
> >
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > Hi all,
> > >
> > > I'm using bcp from the command line to backup and
> > > restore the data in a table.
> > >
> > > To backup, I run a command like this:
> > >
> > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > and this works fine. However, when I try to restore
> > > the data that I have backed up by doing this
> > >
> > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > I get the following error:
> > >
> > > SQLState = 37000, NativeError = 170
> > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect
> > > syntax near 'varchar'.
> > >
> > > Does anyone have any idea what the problem might
> > > be?
> > >
> > > TIA,
> > >
> > >
> > > --
> > > Akin
> > >
> > > aknak at aksoto dot idps dot co dot uk
> > >
> > >
> > >
> >
> >
>|||OK James, I figured it out. The problem was that I had
some fields in the table which had spaces between their
names, like '[First Name]' and I wasn't using the quoted
identifier switch (-q). I thought I only needed to use
this when the name of the *table* or *database* whose
data I wanted to import had a space, but this seems to
apply to fields to.
Cheers,
Akin
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Doh!
> Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> Now I'm well and fully stumped.
> James Hokes
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > Hello James,
> >
> > Thanks for your reply.
> >
> > If you see the command line I typed out, you will see that I
> > *am* using the -n switch, but the error still occurs.
> >
> > Any other ideas?
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > use the -n switch so you get a native dump.
> > > Then, you'll have no trouble restoring as long as the schema is the
> same.
> > > Otherwise, you've got to use format files.
> > >
> > > (BTW, as a side not, BCP is not a backup utility).
> > >
> > > James Hokes
> > >
> > >
> > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > Hi all,
> > > >
> > > > I'm using bcp from the command line to backup and
> > > > restore the data in a table.
> > > >
> > > > To backup, I run a command like this:
> > > >
> > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > and this works fine. However, when I try to restore
> > > > the data that I have backed up by doing this
> > > >
> > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > I get the following error:
> > > >
> > > > SQLState = 37000, NativeError = 170
> > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect
> > > > syntax near 'varchar'.
> > > >
> > > > Does anyone have any idea what the problem might
> > > > be?
> > > >
> > > > TIA,
> > > >
> > > >
> > > > --
> > > > Akin
> > > >
> > > > aknak at aksoto dot idps dot co dot uk
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sky Fly,
Hey, way to go! That was one that I had not thought of, but now,thanks to
you, I'll keep it in my bag of tricks.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs14nd$8dlel$1@.ID-18325.news.uni-berlin.de...
> OK James, I figured it out. The problem was that I had
> some fields in the table which had spaces between their
> names, like '[First Name]' and I wasn't using the quoted
> identifier switch (-q). I thought I only needed to use
> this when the name of the *table* or *database* whose
> data I wanted to import had a space, but this seems to
> apply to fields to.
> Cheers,
> Akin
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Doh!
> >
> > Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> >
> > Now I'm well and fully stumped.
> >
> > James Hokes
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > > Hello James,
> > >
> > > Thanks for your reply.
> > >
> > > If you see the command line I typed out, you will see that I
> > > *am* using the -n switch, but the error still occurs.
> > >
> > > Any other ideas?
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > use the -n switch so you get a native dump.
> > > > Then, you'll have no trouble restoring as long as the schema is the
> > same.
> > > > Otherwise, you've got to use format files.
> > > >
> > > > (BTW, as a side not, BCP is not a backup utility).
> > > >
> > > > James Hokes
> > > >
> > > >
> > > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > > Hi all,
> > > > >
> > > > > I'm using bcp from the command line to backup and
> > > > > restore the data in a table.
> > > > >
> > > > > To backup, I run a command like this:
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > and this works fine. However, when I try to restore
> > > > > the data that I have backed up by doing this
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > I get the following error:
> > > > >
> > > > > SQLState = 37000, NativeError = 170
> > > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect
> > > > > syntax near 'varchar'.
> > > > >
> > > > > Does anyone have any idea what the problem might
> > > > > be?
> > > > >
> > > > > TIA,
> > > > >
> > > > >
> > > > > --
> > > > > Akin
> > > > >
> > > > > aknak at aksoto dot idps dot co dot uk
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Problem with backup and restore

When I am backuping and after restoring the DB on another server, all Users
of this DB can't connect to this DB. I am getting teh error "login failed
for user 'somebody' ".
In DB Folder Users , this user exist.
Vaidas
Search on internet for two stored procedures ('sp_help_revlogin') provided
by MS to move logins with their original SID
"Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> When I am backuping and after restoring the DB on another server, all
> Users of this DB can't connect to this DB. I am getting teh error "login
> failed for user 'somebody' ".
> In DB Folder Users , this user exist.
>
|||Hi ,
use dts tranfer login task . It will tranfer all the logins to the
restored database server.
from
Doller
Uri Dimant wrote:[vbcol=seagreen]
> Vaidas
> Search on internet for two stored procedures ('sp_help_revlogin') provided
> by MS to move logins with their original SID
> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
|||yes but not with their oridinal SID
"doller" <sufianarif@.gmail.com> wrote in message
news:1130921760.840115.43300@.g14g2000cwa.googlegro ups.com...
> Hi ,
> use dts tranfer login task . It will tranfer all the logins to the
> restored database server.
> from
> Doller
> Uri Dimant wrote:
>

Problem with backup and restore

When I am backuping and after restoring the DB on another server, all Users
of this DB can't connect to this DB. I am getting teh error "login failed
for user 'somebody' ".
In DB Folder Users , this user exist.Vaidas
Search on internet for two stored procedures ('sp_help_revlogin') provided
by MS to move logins with their original SID
"Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> When I am backuping and after restoring the DB on another server, all
> Users of this DB can't connect to this DB. I am getting teh error "login
> failed for user 'somebody' ".
> In DB Folder Users , this user exist.
>|||Hi ,
use dts tranfer login task . It will tranfer all the logins to the
restored database server.
from
Doller
Uri Dimant wrote:
> Vaidas
> Search on internet for two stored procedures ('sp_help_revlogin') provided
> by MS to move logins with their original SID
> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> > When I am backuping and after restoring the DB on another server, all
> > Users of this DB can't connect to this DB. I am getting teh error "login
> > failed for user 'somebody' ".
> > In DB Folder Users , this user exist.
> >|||yes but not with their oridinal SID
"doller" <sufianarif@.gmail.com> wrote in message
news:1130921760.840115.43300@.g14g2000cwa.googlegroups.com...
> Hi ,
> use dts tranfer login task . It will tranfer all the logins to the
> restored database server.
> from
> Doller
> Uri Dimant wrote:
>> Vaidas
>> Search on internet for two stored procedures ('sp_help_revlogin')
>> provided
>> by MS to move logins with their original SID
>> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
>> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> > When I am backuping and after restoring the DB on another server, all
>> > Users of this DB can't connect to this DB. I am getting teh error
>> > "login
>> > failed for user 'somebody' ".
>> > In DB Folder Users , this user exist.
>> >
>sql

Problem with backup and restore

When I am backuping and after restoring the DB on another server, all Users
of this DB can't connect to this DB. I am getting teh error "login failed
for user 'somebody' ".
In DB Folder Users , this user exist.Vaidas
Search on internet for two stored procedures ('sp_help_revlogin') provided
by MS to move logins with their original SID
"Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> When I am backuping and after restoring the DB on another server, all
> Users of this DB can't connect to this DB. I am getting teh error "login
> failed for user 'somebody' ".
> In DB Folder Users , this user exist.
>|||Hi ,
use dts tranfer login task . It will tranfer all the logins to the
restored database server.
from
Doller
Uri Dimant wrote:[vbcol=seagreen]
> Vaidas
> Search on internet for two stored procedures ('sp_help_revlogin') provide
d
> by MS to move logins with their original SID
> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...|||yes but not with their oridinal SID
"doller" <sufianarif@.gmail.com> wrote in message
news:1130921760.840115.43300@.g14g2000cwa.googlegroups.com...
> Hi ,
> use dts tranfer login task . It will tranfer all the logins to the
> restored database server.
> from
> Doller
> Uri Dimant wrote:
>

Problem with "Point in time"- restore of database on new server

In short we are trying to restore a production database backup on a
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
Jan
Your RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>

Problem with "Point in time"- restore of database on new server

In short we are trying to restore a production database backup on a
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
JanYour RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>