MS SQL data export/import

[Sabina]

Подскажите, пожалуйста, хорошие линки по следующему поводу:

Нужно написать скрипты для экспорта данных и последущего импорта в заново созданную базу. Мы еще на том этапе когда и dbschema changes не такая уж редкость, а данные терять уже не хочется.

В частности хотелось бы не потерять данные из таблиц, структура которых поменялась. Можно ли это сделать средствами MS SQL built-in Import/Export или нужны дополнительные tools?

Cпасибо,
Сабина

[Dmitry67]

Вам скорее всегдо будет достаточно DTS (он же Import,export)
Но если преобразования сложные, типа нормализации или денормализации, то легче обе базы положить на один сервер и писасть SQL который типа

insert into NewDb.dbo.TAB (collist)
select collist,constants,subqueries etc from oldDb.dbo.TAB ,and joins ???

Сразу натолкну на то что может Вам понодовится
set identity_insert on/off

[Sabina]

Вам скорее всегдо будет достаточно DTS (он же Import,export)

Большое спасибо. И еще вопрос по ходу, где можно посмотреть примеры скриптов с использованием bcp utility? Я пыталась найти sample codes, что идет в комплекте с SQL Server, но на MS сайте непонятно написано с какого диска его ставить. Это SP3? Если да, то в каком из трех install file-ов?
Если это install самого сервера, то видно я не ту версию пробую, потому что там только English Query и еще какой-то другой компонент к серверу, а Sample Code нет.

Похоже проще просто воспользоваться примерами из Интернета.

Cабина

[Sabina]

когда я запускаю

----------------------------------------------------------------
declare @bcpcmdline nvarchar(4000)
declare @sqlfilename varchar(100)

select @sqlfilename = 'C:\Equipment.txt'

select @BCPCMDLINE = 'bcp "select * FROM Equipment" queryout'
+ @SQLFILENAME + ' -S -U [user] -P [password] -c -E'

EXEC MASTER..XP_CMDSHELL @BCPCMDLINE
------------------------------------------------------------------------

получаю

------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
---------------------------------------------------

username и password мои в полном порядке и я db owner

Если подставить после -S @@servername или собственно имя сервера, та же ошибка.

Если запускать DTS wizard, все работает нормально, но я хочу писать stored procedure, типа "BCP out a table" at http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=4

[Niky]

А так не пробовали?

Code: Select all

select @BCPCMDLINE = 'bcp "select * FROM Equipment" queryout' 
+ @SQLFILENAME + ' -S' + @@servername + ' -U [user] -P [password] -c -E' 

[Sabina]

А так не пробовали?

Code: Select all

select @BCPCMDLINE = 'bcp "select * FROM Equipment" queryout' 
+ @SQLFILENAME + ' -S' + @@servername + ' -U [user] -P [password] -c -E' 

Так тоже пробовала , все равно пишет

Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

Сабина

[Sabina]

[quote="Sabina"]
------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
---------------------------------------------------
[quote]

I wonder if it has something to do with Net-Libraries?
Server is Advanced 2000, my client is Win XP.

Sabina

[Niky]

А с командной строки на сервере у вас BCP работает?

[Sabina]

А с командной строки на сервере у вас BCP работает?

Спасибо Niky, что помогаете невзирая на предпраздничный вечер
Попробовала запустить с сервера, тот же ответ

И с разными user accounts та же история. Правда admin password я не знаю, но вроде тут admin account как раз и не обязателен

Cабина

[Dmitry67]

1. надеюсь в реальной программе [user] и [password] заменены на реальные ?
2. Пoпробуйте trusted connection
3. Не является ли имя сервера 'хитрым' - то есть содержащим минусы или еще какие не вполне допустимые символы (но минусы просто обожают )
4. наконец попробуйте BULK INSERT, он еще быстрее bcp
5. DTS тоже можно запускать из sp так:

exec master.dbo.xp_cmdshell 'DTSrun dtsname /qualifiers see bol'

[Sabina]

2. Пoпробуйте trusted connection

Такое ощущение что я(user accounts, которые я пробовала) не могу запускать xp_cmdshell. Он вроде не из тех, что могут запускаться только admin-ом.

3. Не является ли имя сервера 'хитрым' - то есть содержащим минусы или еще какие не вполне допустимые символы (но минусы просто обожают )

Точно, есть там минус. А как тогда быть? Сервер переименовывать?

4. наконец попробуйте BULK INSERT, он еще быстрее bcp

Мне пока хочется попробовать таблицу в файл скинуть, bulk insert вроде этого не делает.

5. DTS тоже можно запускать из sp так:

exec master.dbo.xp_cmdshell 'DTSrun dtsname /qualifiers see bol'

Спасибо, попробую в понедельник. У нас тут выходные, индюшек все поедают

[IA72]

Подскажите, пожалуйста, хорошие линки по следующему поводу:

Нужно написать скрипты для экспорта данных и последущего импорта в заново созданную базу. Мы еще на том этапе когда и dbschema changes не такая уж редкость, а данные терять уже не хочется.

В частности хотелось бы не потерять данные из таблиц, структура которых поменялась. Можно ли это сделать средствами MS SQL built-in Import/Export или нужны дополнительные tools?

Cпасибо,
Сабина

Как тут уже неоднократно писали, можно DTS, если аккуратно.
Ну а для страховки очень рекомендую правильный инструмент по типу http://www.red-gate.com/sql/summary.htm

[Dmitry67]

1
Такое ощущение что я(user accounts, которые я пробовала) не могу запускать xp_cmdshell. Он вроде не из тех, что могут запускаться только admin-ом.

2
Точно, есть там минус. А как тогда быть? Сервер переименовывать?

3
У нас тут выходные, индюшек все поедают

1 Как раз их тех. Ведь это одна из самых опасных процедур
Вы можете сделать xp_cmdshell('del C:\чтото важное')

2 Попробуйте заклычить сервер в двойные кавычки

3 Всего Вам...

[Sabina]

1 Как раз их тех. Ведь это одна из самых опасных процедур
Вы можете сделать xp_cmdshell('del C:\чтото важное')

Я неточно выразилась. Хотела сказать "не только дефолтовый sa". Тот эккаунт, который я пробовала тоже aдминский, правда я не в курсе про fixed server role, etc.

"only members of the sysadmin fixed server role can execute this
extended stored procedure. However, permissions can be granted to other
users to execute stored procedures and extended stored procedures.
You can find out the current permissions by running 'sp_helprotect' stored
procedure, which will tell, who else..."

А bcp тоже не все могут запускать? Я даже example на Northwind с самого сервера запустить не могла.

2 Попробуйте заклычить сервер в двойные кавычки

Слава богу, что можно поробовать "малыми жертвами" обойтись

[Sabina]

С утра на тот же скрипт сервер меня порадовал новым error message

Msg 50001, Level 1, State 50001
xpsql.cpp: Error 997 from GetProxyAccount on line 604

Which makes me think I am not authorized to run xp_cmdshell.

However 'sp_helprotect' gives me a list of 52 records including sysmembers and sysusers that I am certain I am a part of.

Единственный человек который имеет full access придет после обеда.
Имеет смысл продолжать что-то пытаться с этим сделать или уже расслабиться и делать что-нибудь другое до ее прихода ?

Спасибо IA72 за совет по поводу SQL tools (который я почему-то поздно заметила ). Мне вообще-то нужен скрипт, который можно будет потом вставить в скрипты для QA. Чтобы когда мы меняем db schema, их данные не пропадали. Этот тул генерирует код, который потом можно слямзить?

Cабина

[IA72]

Спасибо IA72 за совет по поводу SQL tools (который я почему-то поздно заметила ). Мне вообще-то нужен скрипт, который можно будет потом вставить в скрипты для QA. Чтобы когда мы меняем db schema, их данные не пропадали. Этот тул генерирует код, который потом можно слямзить?
Cабина

Да

[SkyWalker]

По умолчанию только пользователь с административными привилегиями может выполнять подобные запросы. В SQL Server Agent properties на закладке Job System Вы найдете раздел Non-SysAdmin job step Proxy Account.

Ошибка, которую Вы указали выше обычно означает что у Вас либо SQL Agent не может определить Proxy Account из-за недостатка прав, либо, если стоит опция по умолчанию, нужно использовать только пользователя с административными правами.

[Sabina]

[deleted]

[Sabina]

Всем большое спасибо, все скрипты написала.
А с эккаунтом, stupid me was trying to use SQL server account instead of NT one.

Cабина

[Sabina]

Всем большое спасибо, все скрипты написала.

Ну вот старалась-старалась и все псу под хвост
Написанные скрипты для DTs забраковали по причине того, что нужно создавать DTS для каждой customer DB, даже если их структура идентична. А им хочется универсальный скрипт, чтобы имя базы там было переменной.

А bcp у меня не распознает schema changes, ну или по крайней мере я не знаю как ее это заставить сделать. То есть если вставить новую колонку bcp на импорте ругается.

Кто-нибудь наверняка делал подобное, подскажите как тут быть, плиз.

Сабина

[Dmitry67]

Не обязательно вроде
DTS можно параметризировать. Но я сам это решил не использовать
Мне было легче вынести сложные вещи по подготовке .txt файлов к загрузке в Perl, вызываемый xp_cmdshell

Я так и не понял
Речь идет об экспорте, так ? Из разных баз причем структура их может отличаться ?
А на сколько сильно отличаться ?

Может малой кровью и не обойтись
Универсальный способ - писать чтото что вызывается xp_cmdshell, коннектится к базе и само пишет файл. Я такие вещи писал на Perl. Пишутся в несколько строчек.

[f_evgeny]

Не обязательно вроде
DTS можно параметризировать. Но я сам это решил не использовать
Мне было легче вынести сложные вещи по подготовке .txt файлов к загрузке в Perl, вызываемый xp_cmdshell

Я так и не понял
Речь идет об экспорте, так ? Из разных баз причем структура их может отличаться ?
А на сколько сильно отличаться ?

Может малой кровью и не обойтись
Универсальный способ - писать чтото что вызывается xp_cmdshell, коннектится к базе и само пишет файл. Я такие вещи писал на Perl. Пишутся в несколько строчек.

Офтопик. Вы использовали ActivePerl?

[tengiz]

А bcp у меня не распознает schema changes, ну или по крайней мере я не знаю как ее это заставить сделать. То есть если вставить новую колонку bcp на импорте ругается.

Если Вам нужно импортировать данные в таблицу с другой структурой - используйте форматные файлы, или делайте импорт во view, содержащий только старые колонки. А также можно сделать bcp in во временную таблицу со стабильной структурой и уже оттуда вставлять в окончательную таблицу с новой структурой. Данных, кстати, много? А то если не очень много (тысячи строк), то и мета-скрипт можно было бы написать, который нарисует из реальной таблицы другой скрипт с кучей insert into...values... который загрузит собственно данные.

[Dmitry67]

Универсальный способ - писать чтото что вызывается xp_cmdshell, коннектится к базе и само пишет файл. Я такие вещи писал на Perl. Пишутся в несколько строчек.

Офтопик. Вы использовали ActivePerl?[/quote]
Да
Но собственно ничего Active и нестандартного там не было кроме

use OLE;

[Sabina]

Не обязательно вроде
DTS можно параметризировать. Но я сам это решил не использовать
Мне было легче вынести сложные вещи по подготовке .txt файлов к загрузке в Perl, вызываемый xp_cmdshell
.

Спасибо, я поискала по ключевым словам "DTs parameters" и уже есть от чего оттолкнуться.
Как комбинировать Perl и SQL Server я не совсем представляю и буду очень признательна за линки . Вплоть до того куда например например принято совать perl скрипты А то я создала DTSfiles папку на сервере в shared SQL директории и по-моему меня "не очень поняли". Впрочем, я DBA-ские обязанности выполняю не по своему выбору, так что уж как могу.

Я так и не понял
Речь идет об экспорте, так ? Из разных баз причем структура их может отличаться ?
А на сколько сильно отличаться ?
.

И да и нет.
Есть несколько custome-ров (можно назвать их beta customers), у каждого база одинаковой структуры на наших серверах (web service trial).
К примеру мы выпустили новый релиз и для этого пришлось слегка поменять несколько таблиц (в основном колонки вставить). И надо внести изменения в базы клиентов, при этом не теряя их данные.
В идеале такого быть не должно, базу надо сразу нормально дизайнить, но я просто исхожу из того, что имеем.
То что я сделала не годится, ибо придется создавать DTS на каждом конкретном сервере, а хотят скрипт, который можно прогнать подставив server name и db name.
C bcp у меня не получилось импортировать, но я ее запускала со стандартными опциями. Не понимает она изменения структуры таблиц.

Может малой кровью и не обойтись
Универсальный способ - писать чтото что вызывается xp_cmdshell, коннектится к базе и само пишет файл. Я такие вещи писал на Perl. Пишутся в несколько строчек.

1) Буду копать параметры DTS.
2) Если кто подкинет побольше информации про perl (линки, sample code), покопаюсь и в этом. Я когда-то форматировала файлы на awk и gawk.
В общем посмотрим какой кровью.

Сабина

PS. Не по теме, Дима а вы в какой части Парижа живете/работаете?
Я работала под Парижем в Massy, было довольно-таки тоскливо cмотреть на южный пригород после толкания в RER каждое утро. А вот знакомый работал в La Defense, говорит, совсем другое восприятие жизни
А во Finac ходите по выходным ?