Prev: Default storage space - DB engine tuning advisor
Next: Need help with hint syntax in SQL Server 2000
From: FutureShock on 26 Jul 2008 11:50
I have been using SQL Server 2005 for a total of 2 days and am trying to
transfer table data from one server to another.

For whatever reason the original operator only gave us the DB in a BAK
file. The new server for security purpose won't accept that for a
restore unless it was made on their server.

I installed 2005 express and was able to restore on my computer. I was
then able to brute force recover the table 'structure' on the new server.


Now I need to recover the data however I need to do this with either a
brute force SQL insert or import a CSV.

So maybe if someone can help me with 2 questions. Is there a way to
output an SQL insert statement that includes the data? So far I only get
a template insert.

#2 I was able to get BCP and xp_cmdshell running but when I run the
samples I see on the net I only get the BCP syntax table in the pane,
which I think is telling me I am doing something wrong.

Here is the code I am using just to see if I can get it working.that I
got from the site:

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

**********************************************
declare @sql varchar(8000)

select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername

exec master..xp_cmdshell @sql
***********************************************

Thanks in advance for any help that may come my way.

Scotty
From: Eric Isaacs on 26 Jul 2008 13:44
There are some third party tools you might consider for helping with
the transfer of the data. Look into the Red-Gate Software's SQL
Compare tool. A trial version will probably do the trick:

http://www.red-gate.com/index2.htm

Yet another option might be to select another host that will allow you
to use an existing SQL Server database.

As far as the BCP syntax, you were missing a space after the uppercase
S. I was able to test this script successfully:

--------------------------------------------------------
DECLARE @sql VARCHAR(8000)

SELECT @sql = 'bcp master..sysobjects out c:\sysobjects.txt -c -t, -T -
S ' + @@servername

EXEC master..xp_cmdshell @sql
--------------------------------------------------------

-Eric Isaacs


From: Erland Sommarskog on 26 Jul 2008 15:27
FutureShock (futureshock(a)att.net) writes:
> I have been using SQL Server 2005 for a total of 2 days and am trying to
> transfer table data from one server to another.
>
> For whatever reason the original operator only gave us the DB in a BAK
> file. The new server for security purpose won't accept that for a
> restore unless it was made on their server.

I think you should look for a new hosting service, and tell this service
that you are no longer interested in them.

The absolutely best way to move a database between one server to another
is through backup/restore. To wit, this reduces the risk that something
gets mangled in the copying.

Security issues? Of course, the database could include that is malicious
to the server, if their procedures are open to SQL injection. But it
would be no different if you load the server from scripts.

If you want to pursue this operator, you can try the Database
Publishing Wizard,
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-
A410-371A838E570A&displaylang=en

It's not a bad idea to supplement with Red Gate's SQL Compare and SQL
Data Compare, to check that the wizard did not introduce changes.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: FutureShock on 27 Jul 2008 09:15
Eric Isaacs wrote:
> There are some third party tools you might consider for helping with
> the transfer of the data. Look into the Red-Gate Software's SQL
> Compare tool. A trial version will probably do the trick:
>
> http://www.red-gate.com/index2.htm
>
> Yet another option might be to select another host that will allow you
> to use an existing SQL Server database.
>
> As far as the BCP syntax, you were missing a space after the uppercase
> S. I was able to test this script successfully:
>
> --------------------------------------------------------
> DECLARE @sql VARCHAR(8000)
>
> SELECT @sql = 'bcp master..sysobjects out c:\sysobjects.txt -c -t, -T -
> S ' + @@servername
>
> EXEC master..xp_cmdshell @sql
> --------------------------------------------------------
>
> -Eric Isaacs
>
>
Thanks Eric

That seemed to have fixed my BCP problem and was able to recover most of
the tables. Some of the text in the remaining files have a mixture of
the delimitation marks so I will have to use the SQL insert statements
on those.
I learned a great deal in the last couple days.

Scotty
From: FutureShock on 27 Jul 2008 09:17
Erland Sommarskog wrote:
> FutureShock (futureshock(a)att.net) writes:
>> I have been using SQL Server 2005 for a total of 2 days and am trying to
>> transfer table data from one server to another.
>>
>> For whatever reason the original operator only gave us the DB in a BAK
>> file. The new server for security purpose won't accept that for a
>> restore unless it was made on their server.
>
> I think you should look for a new hosting service, and tell this service
> that you are no longer interested in them.
>
> The absolutely best way to move a database between one server to another
> is through backup/restore. To wit, this reduces the risk that something
> gets mangled in the copying.
>
> Security issues? Of course, the database could include that is malicious
> to the server, if their procedures are open to SQL injection. But it
> would be no different if you load the server from scripts.
>
> If you want to pursue this operator, you can try the Database
> Publishing Wizard,
> http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-
> A410-371A838E570A&displaylang=en
>
> It's not a bad idea to supplement with Red Gate's SQL Compare and SQL
> Data Compare, to check that the wizard did not introduce changes.
>
>
>
I agree with the changing of host but I cannot convince the owner of
such. I was able to use the BCP for most files I will have to explore
the SQL Scripts and wizard for the rest.

Thanks for your help and effort.

Scotty
 |  Next  |  Last
Pages: 1 2
Prev: Default storage space - DB engine tuning advisor
Next: Need help with hint syntax in SQL Server 2000