Перемещение базы данных (ru-RU)

В документации SQL Server описывается два метода для перемещения файлов базы данных.
Первый метод предлагает отсоединить базу данных, переместить файлы и затем присоединить базу данных. Второй метод предлагает привести базу данных в оффлайн режим, выполнить команду ALTER DATABASE для изменения места расположения файлов, перемещения файлов и затем вернуть базу данных в онлайн режим.
Оба метода содержат большое ограничение: база данных должна быть переведена в оффлайн режим. Второе ограничение в том, что администратор базы данных (DBA) должен иметь полный доступ к папкам, в которых находятся файлы базы данных. В большой организации у них такого доступа может не быть. Кроме того, метод отсоединения/присоединения может вызвать дополнительные проблемы, такие как необходимость изменить владельца базы данных и изменить опции базы данных на их значения по умолчанию. Для избежания всех этих проблем можно использовать другой метод перемещения файлов базы данных.
Например, можно создать новый файл используя команду ALTER DATABASE, затем переместить данные используя команду DBCC SHRINKFILE с опцией EMPTYFILE. Ну и после этого с помощью команды ALTER DATABASE удаляем пустой файл. Таким образом миграцию данных мы можем сделать без перевода базы данных в оффлайн режим.

Давайте рассмотрим этот метод на примере (код работает на SQL Server 2008 и SQL Server 2005). Сперва создадим базу данных с двумя файловыми группами. Во вторичной файловой группе создадим таблицу и заполним её данными.

USE master;  
GO  
-- создаем БД с двумя файловыми группами  
CREATE DATABASE test  
ON PRIMARY (NAME=test_primary_dat, FILENAME='c:\test\test_primary_dat.mdf'),  
FILEGROUP SECONDARY (NAME=test_secondary_dat,  
FILENAME='c:\test\test_secondary_dat.ndf') LOG ON (NAME=test_log,  
FILENAME='c:\test\test_log.ldf');  
GO  
USE Test;  
GO  
-- создаем таблицу во вторичной файловой группе  
CREATE TABLE dbo.TestData 
(testData_ID int identity(1000,2) NOT NULL PRIMARY KEY CLUSTERED,  
testData_Field1 int)  
ON [SECONDARY];  
GO  
-- заполняем таблицу данными  
DECLARE @counter int;  
SET @counter=1;  
WHILE (@counter<1000)  
BEGIN 
INSERT INTO dbo.testData(testData_Field1) VALUES (@counter);  
SET @counter=@counter+1;  
END 
GO

Затем создадим новый файл во вторичной файловой группе. Замечу, что этот файл создается в той же папке, что и изначально созданный файл. Это сделано лишь для демонстрации. В своей реальной системе вы можете создавать новый файл там, где вам угодно.

 

      USE Test;    
    GO    
    -- создаем новый файл во вторичной файловой группе   
      ALTER DATABASE test
ADD FILE  
      (    NAME    =test_secondary_dat_NEW, FILENAME=    'c:\test\test_secondary_dat_NEW.ndf'    )    
      TO FILEGROUP [SECONDARY];  
      GO    
      -- покажем размер файлов перед переносом    
      DBCC SHOWFILESTATS;    
      GO   
      -- очищаем старый файл    
      DBCC SHRINKFILE (    'test_secondary_dat'    , EMPTYFILE);    
      GO    
      -- смотрим на размер фалов после очистки файла    
      DBCC SHOWFILESTATS;    
      GO    
      -- удаляем пустой файл    
      ALTER DATABASE Test REMOVE FILE test_secondary_dat;  
      GO                       

FYI (к сведению)
Описанный метод имеет несколько ограничений. Во-первых, он более медленный, по сравнению с двумя способами, указанными в начале статьи. Но с другой стороны, он быстрее, т.к. не требует отключать базу данных и данные всегда доступны для пользователей. Во-вторых, этот метод нельзя использовать для работы с системными объектами. Но этого можно избежать если следовать рекомендации Microsoft о том, что системные файлы следует оставлять в первичной файловой группе, а для данных пользователя создавать отдельную файловую группу, как было сделано в этом примере. В-третьих, операция DBCC SHRINKFILE может вызывать фрагментацию индексов. Поэтому вам необходимо после такой операции провести обслуживание индексов.