Перенос базы данных на другие системы

Резервное копирование всех пользовательских баз данных при помощи инструкций T-SQL

Хотя действия, описанные для создания полной резервной копии, позволяют выполнить этот процесс за один раз, автоматизировать процесс нелегко. Следующий код, включенный в файлы примеров под именем BackupAllUserDBs.sql, позволит добиться большей гибкости при планировании операций резервного копирования и восстановления. (В "Использование отсоединения и присоединения для переноса данных" об автоматизации и планировании рассказывается более подробно).

Данный код создает резервные копии всех пользовательских баз данных. Измените этот сценарий так, чтобы он лучше удовлетворял вашим требованиям. Обратите внимание на то, что некоторые строки разбиты из-за их длины.

declare @DatabaseName varchar(300) 
        ,@BackupSQL varchar(8000) 
        ,@Timestamp varchar(30) 
        ,@DirectoryPath varchar(2000) 
        ,@FullPath varchar(2500) 
        ,@RecoveryModel int
 set @DirectoryPath = "D:\MSSQL\BACKUP\"
 -создаем временную метку для имени файла резервной копии
 set @TimeStamp = convert(varchar, getdate(),112) +
     replace(convert(varchar, getdate(),108),':','')
 -извлекаем только пользовательские базы данных
 declare Database_Cursor cursor for
 select d.name
   from sys.databases d
   where d.name not in("master",'tempdb','model','msdb')
 
 open Database_Cursor
 
 fetch next from Database_Cursor 
 into @DatabaseName 
 while @@fetch_status = 0 
   begin
 
     set @FullPath = ""
     set @FullPath = @DirectoryPath + @DatabaseName
 
     exec sys.xp_create_subdir @FullPath
 
     set @BackupSQL = ""
     set @BackupSQL = @BackupSQL + "BACKUP DATABASE "+ 
                      @DatabaseName + "TO DISK = N'"   + 
                      @FullPath + "\" + 
                      @DatabaseName + "_" + 
                      @TimeStamp + ".bak"' 
                      WITH NOFORMAT, NOINIT, SKIP'
      
      exec (@BackupSQL)
      
      -создаем резервные копии журнала транзакций
      select @RecoveryModel = d.recovery_model 
        from sys.databases as d 
        where d.name = @DatabaseName
      -только резервные копии журналов транзакций тех баз данных, для которых выбрана модель восстановления Full
      if @RecoveryModel = 1 
        begin
          set @BackupSQL = ""
          set @BackupSQL = @BackupSQL + "BACKUP LOG "+ 
              @DatabaseName + "TO DISK = N'" + @FullPath + "\" + 
              @DatabaseName + "_" + @TimeStamp + ".trn"' 
              WITH NOFORMAT, NOINIT, SKIP' 
          exec(@BackupSQL) 
        end
      
      fetch next from Database_Cursor 
      into @DatabaseName
    
    end
  
  close Database_Cursor 
  deallocate Database_Cursor

Полная резервная копия, разностная резервная копия и резервные копии журнала транзакций

При использовании разностных резервных копий или резервных копий журнала транзакций необходимо начать с полной резервной копии. Выполните первые пять действий описанной выше процедуры, чтобы создать исходную полную резервную копию. Не продолжайте процесс восстановления до завершения. После создания полной резервной копии выполните следующие действия.

Создаем разностную резервную копию

1. Повторите действия с 1 по 5 для создания полной резервной копии, но в пункте 3 задайте Backup Type (Тип резервной копии) Differential (Разностная). В разностной резервной копии будут отражены все изменения, которые были сделаны после создания последней полной резервной копии базы данных.
2. Следующие действия, которые надо выполнить для восстановления данных из полной резервной копии, совпадают с описанными выше (пункты 6-13), с единственным отличием - необходимо выбрать для Recovery State (Состояние восстановления) вариант RESTORE WITH NORECOVERY (пункт 12). Благодаря этому база данных останется в неиспользуемом состоянии. В панели Object Explorer (Обозреватель объектов) база данных будет помечена зеленой стрелкой и словом "(Restoring)" (Восстановление из копии) Эту базу данных нельзя будет использовать до завершения процесса восстановления работоспособности.
3. Далее, восстановим данные из разностной резервной копии базы данных, щелкнув правой кнопкой мыши на восстанавливаемой базе данных и выбрав из контекстного меню команды Task, Restore, Database (Задачи, Восстановить, База данных). В открывшемся диалоговом окне Restore Database (Восстановление базы данных) выберите только что созданную разностную резервную копию и параметр состояния восстановления RESTORE WITH RECOVERY. Нажмите кнопку OK, и база данных будет готова к использованию по завершении операции восстановления.

Создаем резервную копию журнала транзакций

1. Как и раньше, начните с описанного выше создания полной резервной копии базы данных. При желании можно использовать также разностную резервную копию. Убедитесь, что выбрана модель восстановления FULL. Дополнительную информацию о моделях восстановления можно найти в лекциях 4-5.
   Совет. Резервные копии журнала транзакций можно использовать только в том случае, если для базы данных выбрана полная модель восстановления или модель восстановления с неполным протоколированием. При простой модели восстановления журнал транзакций периодически подвергается усечению, поэтому нельзя создать резервную копию журнала транзакций.
2. Снова откройте диалоговое окно Backup Database (Резервное копирование базы данных), как описано в пункте 2 раздела "Создание резервной копии через интерфейс SQL Server Management Studio" этой лекции. Здесь нужно изменить Backup Type (Тип резервной копии) на Transaction Log (Журнал транзакций). Остальные настройки такие же, как и в предыдущем примере.
   Совет. Обычно для полных и разностных резервных копий используется расширение имени файла .bak, а для резервных копий журнала транзакций - расширение имени файла .trn.
3. Как правило, приходится сохранять более одной резервной копии журнала транзакций. Эти журналы также обобщенно называются "цепочкой журналов". Так и для этого примера мы создадим еще одну резервную копию журнала транзакций. Выполните процедуру, опиисанную выше, но проследите за тем, чтобы имя второй резервной копии журнала явно свидетельствовало о том, что это вторая резервная копия в цепочке журналов.
4. Затем восстановите данные из полной резервной копии. Как и при восстановлении данных из разностной резервной копии, нужно выбрать для состояния восстановления вариант RESTORE WITH NORECOVERY.
5. Теперь восстановите данные из первой резервной копии журнала транзакций. Это можно сделать, щелкнув правой кнопкой мыши на базе данных, которая находится в процессе восстановления, и выбрав из контекстного меню Tasks, Restore, Transaction Log (Задачи, Восстановление, Журнал транзакций). При этом откроется диалоговое окно Restore Transaction Log (Восстановление журнала транзакций), показанное на рисунке.
   

   
   

   Как видите, это диалоговое окно не отличается от обычного окна Restore (Восстановление). Единственная дополнительная настройка – это Restore To (Восстановить в) в нижней части диалогового окна. Для всех примеров используйте вариант Point In Time (На момент времени). Выделите первую резервную копию в цепочке журналов, задав для Recovery State (Состояние восстановления) параметр RESTORE WITH NORECOVERY. Нажмите кнопку ОК, чтобы восстановить данные из этой резервной копии.

   Совет. Если у вас не одна, а более резервных копий журнала транзакций, продолжайте восстановление журнала в порядке выполнения их создания до тех пор, пока не будут восстановлены данные последней из резервных копий журнала транзакций в цепочке журналов. Можно также в любой момент прервать восстановление в любой точке цепочки журналов. После восстановления всех нужных файлов перейдите к следующему действию.
6. Теперь восстановим вторую резервную копию журнала транзакций. На этот раз для Recovery State (Состояния восстановления) выберем параметр RESTORE WITH RECOVERY, благодаря чему база данных после восстановления будет в рабочем состоянии.

Предупреждение. Вам не обязательно будет известно, какие именно данные сохранены в конкретной резервной копии. Оценивать, какие данные могли войти в резервную копию, придется по времени ее создания. Даже при использовании резервного копирования журнала транзакций, сохраняются сведения только о завершенных транзакциях.

Совет.Существует несколько инструментов сторонних разработчиков, которые сжимают резервные копии. Это замечательная возможность, если в ваших базах данных сосредоточены существенные объемы данных по всей сети, особенно, если речь идет о широкомасштабной сети.