Восстановление базы данных с использованием WITH MOVE

Очень часто приходится отвечать на вопрос "как восстановить базу данных в MS SQL 7.0" c уточнением, что при попытке выполнения комманды RESTORE DATABASE сервер ругается так:

Server: Msg 3156, Level 16, State 1
The file 'g:\MSSQL7\data\mydb_data.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.

или так

Server: Msg 3158, Level 16, State 1
Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.

Чтобы сэкономить время на написание писем, приведу решение.

Собственно на решение прозрачно намекают в обоих сообщениях об ошибке. Возможная причина возникновения ошибки - backup (dump) достался по наследству, и имя_файла_в_операционной_системе сохранённое в заголовке бэкапа не может быть использовано. Например, если на сервере, на котором производился бэкап, файлы данных и логов были размещены на диске G:\, а на сервере, на котором производится восстановление БД такого диска нет и в помине.

В приведённом ниже примере проводится восстановление БД myDB, файлы которой до бэкапа размещались на диске G:\, а после восстановления будут перемещены на диск D:\.

Для начала надо выяснить логические имена файлов данных и логов. Если мучительно вспоминать их неохота, выполняем следующий запрос:

use master
go
RESTORE FILELISTONLY 
FROM 
DISK = "D:\dumps\myDB20011003.bak" 

в полученном резалтсете нас особенно интересует первая колонка:

LogicalName PhysicalName                 Type FileGroupName Size     MaxSize      
----------- ---------------------------- ---- ------------- -------- --------------
myDB_Data   g:\MSSQL7\data\mydb_data.mdf D    PRIMARY       4653056  35184372080640
myDB_Log    g:\MSSQL7\data\mydb_log.ldf  L    NULL          22675456 35184372080640

(2 row(s) affected)

впрочем, если администратор БД, доставшейся по наследству, не использовал говорящих логических и физических имён для файлов данных и логов, нам пригодится третья колонка. D - файлы данных, L - файлы логов.

Теперь используем эти имена для восстановления БД с использованием опции MOVE:

use master
go
RESTORE DATABASE 
[myDB] 
FROM 
DISK = "D:\dumps\myDB20011003.bak" 
WITH  
MOVE 'myDB_data' TO 'd:\MSSQL7\data\mydb_data.mdf', 
MOVE 'myDB_log'  TO 'd:\MSSQL7\data\mydb_log.ldf'

Как видим, если знаешь что делать, ничего сложного нет :)

Восстановление логинов пользователей базы данных.

При выполнении команды RESTORE базы данных процесс восстановления данных и логов не может считаться заершённым, если не были восстановлены логины пользователей БД. Если операции backup/restore проводились на разных серверах, то связи пользователей БД с их логинами будут разорваны, и их нужно исправить. Для тех кто не хочет это делать при помощи Enterprise Manager, приведу простенький скрипт.

Сначала определим пользователей ущемлённых в их правах:

use myDB
go
sp_change_users_login "Report"

и в этом резалтсете нас будет интересовать первая колонка:

UserName  UserSID
--------  -----------------------------------
dummy     0x15DE8ABCD84DD511A262204C4F4F5020  

Если резалтсет пустой, то администратор может считать процесс восстановления завершённым, если нет, то для каждой строки резалтсета (или для каждого ущемлённого пользователя, что одно и тоже) необходимо выполнить скрипт аналогичный следующему:

use myDB
go
exec sp_change_users_login "Auto_Fix", 'dummy'

И это ещё не всё. Если хранимая процедура sp_change_users_login не нашла соответствия старому логину, она создаст новый с пустым паролем (что в общем-то логично), поэтому

Проверяем логин для пользователя dummy

use myDB
go
exec sp_helpuser "dummy"

в резалтсете смотрим в третью колонку

UserName  GroupName  LoginName     DefDBName UserID SUserID 
--------- ---------- ------------- --------- ------ ------- 
dummy     dummies    dummy_forewer myDB      6      9      

если логин новый, то меняем для него пароль:

use master
go
exec sp_password @new ="new password for dummy",  @loginame = "dummy_forewer"

Повторяем операцию для каждого нового логина, и после этого идём спокойно пить пиво.

P.S: я не претендовал на полноту описания процесса восстановления базы данных и рассмотрел только две наиболее часто встречающиеся проблемы. Поэтому если если к вашей ситуации решение не подходит, то почитайте в Books Online раздел RESTRORE DATABASE, и если не поможет, то задайте вопрос на нашем форуме. Отвечать же на каждое приходящее письмо с вопросами я просто физически не успеваю - мне хватает переписки с сослуживцами :)