Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
Mark12
Member

Откуда:
Сообщений: 4
Тема обсуждалась не единожды, но, как я понял, топик стартеры, задав вопрос и получив ответ не публиковали результат и решение, которое сработало. Я не особо силен в SQL, без "магического пендаля" в нужном направлении не получается. Итак, вопрос.
Есть установленный несколько лет назад SQL2000, на нем пяток баз от 500 МБ до 4 ГБ каждая. Задача - унести с него базы на виртуальный сервер на котором SQL 2008R2. Как я планировал это сделать:
1. создать backup каждой базы
2. восстановить (restore) базы на новом сервере
3. перенастроить клиентов

Что получлось на практике.
1. backup проходит нормально.
2. restore проходит нормально (делал как с предварительным созданием новой пустой базы и ее перезаписью, так и просто восстановлением)
3. настройка источника данных в ODBC успешно, тест проходит
4. при попытке запуска клиента получаю ошибку "неверный пароль или база данных недоступна", как я понимаю на первом же обращении к базе. Хорошо, я предположил, что дело в сопоставлении Logins сервера и Users в базе. В принципе логично такое сопоставление выполнить для восстановленной базы. На новом 2008-м иду в свойства логина qwe (пользователь в базе есть) и сопоставляю его базе, которую только что restor'ил. Естественно, получаю ошибку с уведомлением, что такой пользователь в базе существует. Ладно, пробую прибить пользователя в базе, естественно не получится, пока он числится владельцем схемы. Схему удалить не выходит (это вообще возможно?), зато можно сменить ей владельца. После этого можно удалить пользователя. Логин тоже удаляю. Опять иду в Logins, создаю логин qwe, ставлю ему соответствие базе, поле schema не заполняю (скользский момент, т.к. не совсем понимаю смысл самих схем и их отношения с "владельцами" от 2000-й версии). Вижу созданный логин qwe и пользоавтеля в базе. Схема пользователю назначается dbo, автоматом. Я читал, что, если, к примеру, таблица не находится в схеме пользователя, или обращение к таблице не содержит указания схемы, то она ищется в схеме dbo. Но смотрю в таблицы базы и вижу, что все таблицы принадлежат схеме qwe (потому, что qwe был владельцем базы на 2000-м?), кроме системных таблиц, которые принадлежат схеме dbo. В то же время,у других баз которые живут на этом виртуальном сервере, все таблицы принадлежат схеме dbo и при этом работают.
Вот тут обсуждалась весьма и весьма похожая тема:
[url=]https://www.sql.ru/forum/942814/vosstanovlenie-bd-iz-ms-sql-2000-v-ms-sql-2008?hl=??????? ????? 2008[/url]
Возможно что вот такой вариант оттуда и есть решение проблемы:
select 'ALTER SCHEMA dbo TRANSFER ['+schema_name(schema_id)+'].['+name+']'
from sys.objects 
where 
    is_ms_shipped=0 and 
    schema_name(schema_id)<>'dbo'

Если я правильно понял, то это просто скрипт смены схемы для всех объектов базы. Возможно есть другое решение, с использованием графического интерфейса?
PS не судите строго, базы нам читал человек, ничего в них не понимавший.
6 дек 13, 12:32    [15252051]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
Mark12,
вместо вот этого "неверный пароль или база данных недоступна"
приведите, плиз, оригинальное сообщение сервера об ошибке.
ну или из error log-а его сюда скопируйте.
похоже, что он и на сервер-то не попадает.
или "пароль не тот", или с его дефолтной базой проблемы
6 дек 13, 12:40    [15252132]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
aleks2
Guest
sp_change_users_login [ @Action = ] 'action' 
    [ , [ @UserNamePattern = ] 'user' ] 
    [ , [ @LoginName = ] 'login' ] 
        [ , [ @Password = ] 'password' ]

>>иду в свойства логина qwe
Это твоя главная ошибка.
6 дек 13, 12:42    [15252147]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
Glory
Member

Откуда:
Сообщений: 104751
Mark12
Возможно есть другое решение, с использованием графического интерфейса?

есть
Заходить вручную в desing каждой таблицы и менять там имя схемы
6 дек 13, 12:43    [15252156]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
люди, a объясните мне, какой нафиг пароль может быть при доступе к _базе_?
у меня конечно сервер по-русски не пишет, но если нет доступа к базе,
ODBC напишет: Cannot open database bla-bla-bla requested by login.
а студия выдаст
The server principal co-co-co is not able to access the database "bla-bla-bla" under the current security context.
6 дек 13, 12:51    [15252239]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
Glory
Member

Откуда:
Сообщений: 104751
o-o
люди, a объясните мне, какой нафиг пароль может быть при доступе к _базе_?
у меня конечно сервер по-русски не пишет, но если нет доступа к базе,
ODBC напишет: Cannot open database bla-bla-bla requested by login.
а студия выдаст
The server principal co-co-co is not able to access the database "bla-bla-bla" under the current security context.

Клиентское приложение может выдать какой угодно текст
Что собсственно и происходит у ТСа - "4. при попытке запуска клиента получаю ошибку "неверный пароль или база данных недоступна","
6 дек 13, 12:53    [15252260]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
aleks2
Guest
Mark12
На новом 2008-м иду в свойства логина qwe (пользователь в базе есть) и сопоставляю его базе, которую только что restor'ил.


Вот чо пудрите чайнегу мозг?
Надо СУЩЕСТВУЮЩЕМУ В БАЗЕ пользователю сопоставить ЛОГИН НОВОГО СЕРВЕРА.
6 дек 13, 12:56    [15252296]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
руки поотрывать переводчикам оригинальных сообщений об ошибках вольным текстом.

я вот вообще дальше этого сообщения не берусь читать, мало-ли, если это qwe, простите,
не виндовский, а сиквельный логин, мог он на одном компе один пароль иметь, а на новом другой?
что с того, что есть одноименный логин.
вот на моем сервере есть логин Vasja, и на вашем есть Vasja.
че, у них уже и пароли совпали?

автор, Вы сами под тем логином, для начала, на сервер попадаете или нет?
6 дек 13, 13:06    [15252399]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
вообще нет.
если источник ODBC и тест прошел успешно, то при сиквельном логине это подтверждает,
что пароль туда вшили нужный.
тогда да,

USE db;
ALTER USER qwe WITH login = qwe;
6 дек 13, 13:18    [15252516]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Стратегия переноса баз с 2000 на 2005/2008/2008R2:
1. бэкап/рестор или деаттач/аттач БД
2. перенос логинов -> http://support.microsoft.com/kb/246133/ru
3. связывание пользователей с логинами
можно так
DECLARE @DB sysname, @SqlCmd nvarchar(4000)
DECLARE All_UserBDs CURSOR
FOR
SELECT name FROM sys.databases
WHERE database_id > 4 AND state = 0 AND is_distributor = 0

OPEN All_UserBDs
FETCH NEXT FROM All_UserBDs INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlCmd = 'USE [' + @DB + '] ' +
'
DECLARE @sysusers_name varchar(500), @sysusers_sid varbinary(85), @sysusers_default_schema_name sysname

DECLARE stdsqlusers_cursor CURSOR
FOR SELECT name, default_schema_name, [sid] from sys.database_principals
WHERE [type] = ''S'' AND principal_id > 4
ORDER BY name

OPEN stdsqlusers_cursor
FETCH NEXT FROM stdsqlusers_cursor INTO @sysusers_name, @sysusers_default_schema_name, @sysusers_sid
IF @@FETCH_STATUS = 0 BEGIN PRINT ''USE [' + @DB + ']'' PRINT '''' END
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT name FROM master..syslogins WHERE name = @sysusers_name)
BEGIN
PRINT ''MSG ****: Please add the Login : '' + @sysusers_name + '' using sp_addlogin.''
END
PRINT ''ALTER USER ['' + @sysusers_name + ''] WITH LOGIN = ['' + @sysusers_name + ''], DEFAULT_SCHEMA = ['' + @sysusers_default_schema_name + '']''

FETCH NEXT FROM stdsqlusers_cursor INTO @sysusers_name, @sysusers_default_schema_name, @sysusers_sid
END

CLOSE stdsqlusers_cursor
DEALLOCATE stdsqlusers_cursor
PRINT ''''
'
EXEC (@SqlCmd)
FETCH NEXT FROM All_UserBDs INTO @DB
END

CLOSE All_UserBDs
DEALLOCATE All_UserBDs
4. перенос линкованных серверов
5. перенос джобов
6. настройка прочтового клиента

ЗЫ: схему - контейнер для др объектов, удалить можно предварительно очистив от всех объектов
владельцы схем имеют все права на объекты находящиеся в ней
6 дек 13, 13:48    [15252839]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
Mark12
Member

Откуда:
Сообщений: 4
Скрин ошибки прилагаю.
Спасибо большое за все ответы, и отдельное спасибо NickAlex66. Я нашел нечто похожее, вот тут http://support.microsoft.com/kb/918992
во второй половине.
У меня нет необходимости тащить много пользователей из старой базы, даже думаю, что можно прибить этого пользователя в старой базе, забэкапить, отресторить на новом сервере и уж здесь создать нового пользователя с таким же именем и назначением соответствующих прав и схем.
Как думаете, пройдет такой вариант?

К сообщению приложен файл. Размер - 61Kb
6 дек 13, 14:51    [15253425]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
кто бы догадался.
все у вас в порядке с логинами/юзерами,
у Вас обращение о несуществующем объекте.
нет никакого BSMAIL_DSP в базе:

РЕПРО:

select *
from abrakadabra
------------------
Msg 208, Level 16, State 1, Line 1
Invalid object name 'abrakadabra'.

-----------------------------------------
укажите явно СХЕМУ объекта, может и проканает.
может, оно qwe.BSMAIL_DSP
6 дек 13, 15:00    [15253513]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
"обращение о несуществующем объекте"
понимать как AVG("обращение к несуществующему объекту", "сообщение о несуществующем объекте")



...а руки переводчикам сообщений таки надо пообрывать!
6 дек 13, 15:03    [15253553]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
Mark12
Member

Откуда:
Сообщений: 4
после restore схема как раз и указана явным образом и эта схема qwe. Схема dbo остается только у системных таблиц. Но, опять же, как я понял из майкросовтовских хэлпов, софт обращается по пути База.схема.таблица если он написан под 2005+ SQL, у меня софт не известно как обращается, полагаю База.таблица, ибо все работало на 2000-м, а там еще схем не было. Если в обращении схема не указана, то таблица ищется в схеме dbo. (а у меня она по умолчанию при ресторе становится qwe) Значит, если опять же я правильно понял, мне нужно либо по-честному тащить за собой пользователя из базы, либо наоборот, прибить пользователя еще до бэкапа, а на новом месте создать его по-новой.
Логично? Должно сработать удаление до бэкапа и создание после рестора?
6 дек 13, 15:21    [15253751]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
кто тут из нас слепой, простите?
на Вашей картине НЕТУ никакой схемы в запросе,
равно как и сервер, хоть Вы там и позамазывали требуемый объект,
пишет, что не находит BSMAIL_DSP, а не qwe.BSMAIL_DSP.
и кто позаключал все в кавычки?

---------------------------------------
"Если в обращении схема не указана, то таблица ищется в схеме dbo"

нет. сперва ищется в дефолтной схеме юзера.
и в 2000 это соответствовало имени юзера.
с терминами могу наврать, называлось скорее "овнер".
короче, если до этого без указания схемы для юзера qwe объект искался сперва в qwe, а потом в dbo,
то сейчас, если Вы не задали этому qwe дефолтную схему, ищется сразу в dbo.
так что указывайте явно схемы либо укажите юзеру qwe дефолтную схему qwe

К сообщению приложен файл. Размер - 39Kb
6 дек 13, 15:53    [15254086]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
короче. Вас спасет вот это:

ALTER USER qwe 
     WITH DEFAULT_SCHEMA = qwe


простите, что ору.
тут у нас "восстание больных в дурдоме".
похоже, сегодня на форуме больше не появлюсь,
разве что они возьмут "музыкальную паузу", тогда может забегу на секундочку
6 дек 13, 16:03    [15254178]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
Mark12
Member

Откуда:
Сообщений: 4
кто тут из нас слепой, простите?

Я, конечно, тут двух мнений быть не может. В одном месте замазал, в другом - нет :Р
o-o
на Вашей картине НЕТУ никакой схемы в запросе,

Это-то я разглядел :)
o-o
равно как и сервер, хоть Вы там и позамазывали требуемый объект,
пишет, что не находит BSMAIL_DSP, а не qwe.BSMAIL_DSP.

o-o
и кто позаключал все в кавычки?

Софт, вероятно. Как он там написан я не в курсе.
o-o
---------------------------------------
"Если в обращении схема не указана, то таблица ищется в схеме dbo"

нет. сперва ищется в дефолтной схеме юзера.
и в 2000 это соответствовало имени юзера.
с терминами могу наврать, называлось скорее "овнер".

Спасибо за существенное уточнение.
o-o
короче, если до этого без указания схемы для юзера qwe объект искался сперва в qwe, а потом в dbo,
то сейчас, если Вы не задали этому qwe дефолтную схему, ищется сразу в dbo.
так что указывайте явно схемы либо укажите юзеру qwe дефолтную схему qwe


Либо передать все объекты схемы qwe в схему dbo оптом. (главное чтоб не в розницу, много их там)
6 дек 13, 16:10    [15254251]     Ответить | Цитировать Сообщить модератору
 Re: Перенос базы с sql2000 на sql2008 R2, backup->restore, нет доступа к базе  [new]
o-o
Guest
Mark12

Либо передать все объекты схемы qwe в схему dbo оптом. (главное чтоб не в розницу, много их там)


а если другой софт поумнее использует qwe перед объектами в явном виде?
лучше постараться свести все к тому, как было раньше.
а в 2000-ом создавая юзера, Вы автоматом получали одноименную схему,
хоть это и называлось иначе.
поэтому мой совет -- вернуть дефолтные схемы юзерам.
----------------------------------------------------------------

Since the dawn of time, SQL Server have permitted a four-part notation of objects, and it has usually been presented as

server.database.owner.object


But in SQL 2005 this changed to

server.database.schema.object


You may ask, what is this schema? The answer is that schema has always been there, but up to SQL 2000, schema and owner was always the same. In SQL 2005 owner and schema are two different entities.

The preferred way to create a user since SQL 2005 is:

CREATE USER newuser [WITH DEFAULT_SCHEMA = someschema]


There are two system procedures to create users, sp_adduser and sp_grantdbaccess. They are both deprecated and will be removed eventually. There is an important difference between CREATE USER and the two system procedures: CREATE USER creates a user whose default schema is dbo, unless you specify otherwise. On the other hand, sp_adduser and sp_grantdbaccess for compatibility reasons perform the corresponding to:

CREATE SCHEMA newuser
go
CREATE USER newuser WITH DEFAULT_SCHEMA = newuser
go
ALTER AUTHORIZATION ON SCHEMA::newuser TO newuser
6 дек 13, 16:38    [15254494]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить