Sql перенос базы на другой сервер: Перенос всех баз данных MS SQL Server на другую машину / Хабр

Перенос всех баз данных MS SQL Server на другую машину / Хабр

Недавно возникла необходимость переноса всех БД (>50 на одном экземпляре SQL Server) из dev-окружения на другой экземпляр SQL Server, который располагался на другом железе. Хотелось минимизировать ручной труд и сделать всё как можно быстрее.

Disclaimer

Скрипты написаны для одной конкретной ситуации: это dev-окружение, все базы в простой модели восстановления, файлы данных и журналы транзакций лежат в одной куче.

Всё, что написано дальше относится только к этой ситуации, но вы можете без особых усилий допилить их под себя (свои условия).

В скриптах не используются новомодные STRING_AGG и прочие приятные штуки, поэтому работать всё должно начиная с SQL Server 2008 (или 2008 R2, не помню где появилось сжатие бэкапов). Для более старых версий нужно убрать WITH COMPRESSION из команды бэкапа, но тогда разницы по времени с копированием файлов может уже и не быть.

Это не инструкция — «как надо» делать такой перенос. Это демонстрация того, как можно использовать метаданные в dynamic SQL.

Конечно, самым быстрым способом было бы просто переподключить полку с дисками к новому серверу, но это был не наш вариант. Detach — копирование — Attach рассматривался, но не подошёл, поскольку канал был довольно узким и перенос БД в несжатом виде занял бы довольно большой промежуток времени.

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

Так был написан «генератор скриптов»:

DECLARE @unc_backup_path AS varchar(max) = '\\newServer\backup_share\'
	, @local_backup_path AS varchar(max) = 'E:\Backup\'
	, @new_data_path as varchar(max) = 'D:\SQLServer\data\';

SELECT name	
	, 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command
	, 'ALTER DATABASE [' + name + '] SET OFFLINE

Как перенести пользователей Microsoft SQL на новый сервер

Предыстория: понадобилось мне перенести очень большое количество БД с одного сервера на другой в связи… (да не важно в связи с чем, просто была нужда). Но так как Баз Данных было очень много, а разрешений для этих БД было еще больше, да и логины/пароли забивать для этих учетных записей хотелось и того меньше, пришлось искать обходные пути, как упростить данное мероприятие. И оно нашлось! Да, проверено данное действо на Microsoft SQL Server 2012 и 2014. Предположительно будет работать и в более высоких версиях включая 2019, но это не точно.

Итак, что у нас есть:
1. Приаттаченные БД на новом сервере;
2. В наличии пока еще живой старый сервер SQL c с которого эти БД переносились.

Предварительно мы имеем подобные симптомы:
При переносе баз с одного экземпляра SQL Server на другой имена входа (логины) и пароли к ним не переносятся автоматически. Соответственно после переноса пользователи не смогут подключиться к своей базе данных и получат сообщение об ошибке.

Инструкция к действию:
1. На исходном сервере, с которого перенесены базы, выполняем следующий запрос на БД Master:

USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue VARCHAR (514) OUTPUT
AS
DECLARE @charvalue VARCHAR (514)
DECLARE @i INT
DECLARE @LENGTH INT
DECLARE @hexstring CHAR(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @LENGTH = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i < = @LENGTH)
BEGIN
  DECLARE @tempint INT
  DECLARE @firstint INT
  DECLARE @secondint INT
  SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint — (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
 
SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @TYPE VARCHAR (1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  VARCHAR (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string VARCHAR (514)
DECLARE @tmpstr  VARCHAR (1024)
DECLARE @is_policy_checked VARCHAR (3)
DECLARE @is_expiration_checked VARCHAR (3)
 
DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
 
      SELECT p. sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
  DECLARE login_curs CURSOR FOR
 
 
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs
 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @TYPE, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT ‘Имена не найдены.’
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (VARCHAR, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT »
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT »
    SET @tmpstr = ‘— Login: ‘ + @name
    PRINT @tmpstr
    IF (@TYPE IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group
 
      SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
        — obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        — obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys. sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
 
        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
      SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
      SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
      SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
  END
 
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @TYPE, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

2. Этот запрос создаст в базе master две хранимых процедуры — sp_hexadecimal и sp_help_revlogin.
Запускаем процедуру sp_help_revlogin:

USE master
GO
EXEC sp_help_revlogin

3. В результате мы должны получить список созданных ранее пользователей, с хэшами паролей и настройками доступа к приаттаченным ранее БД.
4. Копируем данный скрипт на новый сервер, выбирая тех пользователей которые нам необходимы и выполняем этот скрипт на базе master.

Исходник взят и переосмыслен с сайта Microsoft: How to transfer logins and passwords between instances of SQL Server

Да, еще есть не тру-метод, это найти вот такую программу, которая из master.mdf сама выдерет пароли/логины, но само собой, о правах на БД речи не идет.
И да, это не реклама. В своем уме платить за нее 50$ никакого желания нет.
SQL Server Password Changer

Перенос файла БД Microsoft SQL на другой компьютер

В большинстве случаев необходимо разрабатывать приложения, использующие в качестве базы данных Microsoft SQL Server. Наиболее рациональным решением является разработка базы данных в формате Microsoft SQL на рабочем компьютере с установленной локальной версией Microsoft SQL Server. При сдаче проекта заказчику возникает необходимость переноса базы данных с локального компьютера. Для переноса на другой компьютер нам потребуется скопировать два файла — саму базу данных BDTur_firmSQL.mdf и файл отчетов о транзакциях BDTur_firmSQL.ldf. Однако непосредственное копирование данных файлов невозможно, так как данные файлы используются сервером баз данных. Для того чтобы сделать файлы доступными для копирования, базу данных необходимо отсоединить от сервера (рис. 23).

Рис. 23. Отсоединение выбранной базы данных от сервера

Появляется диалоговое окно «Отсоединение базы данных». Подтверждаем отсоединение, нажимая кнопку «ОК», — и база отсоединена. Теперь нужные файлы доступны для копирования.

Для присоединения базы данных на другом компьютере запускаем SQL Management Studio, выделяем ветку «Базы данных» и в контекстном меню выбираем «Присоединить» (рис. 24).

В появившемся окне указываем расположение файла базы данных BDTur_firmSQL.mdf — файл отчетов присоединится автоматически — и нажимаем «ОК». Присоединившаяся база данных немедленно отображается в папке «Базы данных». Следует отметить, что после присоединения БД может потребоваться настройка пользователей БД и прав доступа.

Рис. 24. Присоединение базы данных

ОСНОВЫ ЯЗЫКА SQL

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

SQL (Structured Query Language), или Структурированный Язык Запросов, — это язык, который дает возможность работать с данными в реляционных базах данных. Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов), а также ISO (Международной организацией по стандартизации). Однако большинство коммерческих программ баз данных расширяют SQL, добавляя разные особенности в этот язык, которые, как они считают, будут полезны. Эти дополнения являются не стандартизированными и часто приводят к сложностям при переходе от одного сервера данных к другому.

Для обращения к базе данных используются запросы, написанные на языке SQL. Запросом называется команда, которая передается серверу базы данных, и которая сообщает ему, что нужно вывести определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера или терминала, хотя в большинстве случаев ее можно также передать на принтер, сохранить в файле (как объект в памяти компьютера) или представить как вводную информацию для другой команды или процесса.

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

Для выполнения SQL-запросов будем использовать SQL Management Studio. При запуске среды Management Studio появляется следующее окно (рис. 25).

Рис. 25. Задание параметров подключения

Задействуем подключение к серверу, находящемуся на локальном компьютере. Параметр «Проверка подлинности» задает аутентификацию при подключении к серверу — при выбранном значении «Проверка подлинности Windows» (Windows authentification) в качестве имени пользователя и пароля будут использованы системные параметры.

Если все сделано правильно, то появляется главное окно программы. Для перехода в режим запросов необходимо нажать кнопку «Создать запрос» (рис. 26).

Рис. 26. Переход в режим создания запросов

Отметим, что будут создаваться запросы, работающие с выделенной базой данных. После нажатия кнопки «Создать запрос» среда SQL Management Studio принимает вид, как показано на рисунке (рис. 27). Обратите внимание на кнопку «Выполнить», которая выполняет запросы, введенные в правом текстовом поле, и выводит результат их выполнения.

2.1. Базовая конструкция SQL-запроса

Основной операцией для описания запроса к БД в языке SQL является конструкция вида:

Select <список атрибутов>
From <список отношений>
Where <условие>

Читайте также:

Рекомендуемые страницы:

Поиск по сайту

Перенос tempdb на другой диск

Главная » IT

Небольшая заметка как действовать в случае когда вам необходимо перенести базу tempdb на другой диск. Такая ситуация может случится в результате сбоя диска, на котором она была размещена в рабочем состоянии и вам потребуется перенос tempdb на другой диск для того, чтобы запустить SQL Server.

Запланированный перенос базы данных

В случае если вам просто необходимо перенести рабочую базу данных, то в manegement studio необходимо выполнить запрос:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'disk:\new_path\new_file_name' )

Где:

• database_name — имя базы данных, которую необходимо перенести;
• logical_name — логическое имя файла;
• disk:\new_path\new_file_name — новый путь к файлу данных.

Такую команду необходимо выполнить для каждого файла данных перемещаемой БД. После чего следует остановить службу MSSQL и переместить файлы данных в новое расположение. При перемещении не забудьте скопировать и права доступа на папку и файлы данных. Затем вновь запустите службы SQL Server.

Перемещение базы данных в случае сбоя

Такая ситуация может возникнуть, если восстановить базу данных в прежнее место невозможно, а без этой базы данных SQL сервер не запускается. Например, как я уже писал в начале, вышедший из строя диск с базой tempdb приведет к остановке MSSQL и невозможности его запуска.

Процедура действий в данном случае почти такая же как и при запланированном переносе. Все операции производим через командную строку cmd с правами администратора. Для начала необходимо запустить SQL сервер в режиме восстановления:

NET START MSSQLSERVER /f /T3608

Затем запустив консольную команду sqlcmd выполнить все те же команды по указанию нового пути к файлам данных для БД. Например для tempdb будут примерно такие команды:

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'c:\tempdb\tempdb.mdf' )  
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = 'c:\tempdb\templog.ldf' )

После того как введены все SQL запросы в интерактивном режиме sqlcmd необходимо ввести команду GO, чтобы выполнить этим самые запросы, а затем EXIT, чтобы выйти из интерактивного режима sqlcmd. Папка c:\tempdb (или та куда вы переносите базу данных tempdb) должна быть заранее создана. Если вы восстанавливаете не tempdb, а любую другую БД, то необходимо в эту папку так же положить файлы БД из резервной копии с именами совпадающими с теми, что указаны в SQL запросах.

Теперь можно перезапускать MSSQL сервер в стандартном режиме:

net stop MSSQLSERVER

net start MSSQLSERVER

Если используется не экземпляр по умолчанию, а именованый, то необходимо заменить MSSQLSERVER на MSSQL$instancename, где instancename — наименование экземпляра MSSQL.

Если все сделано верно, то службы MSSQL запустятся и продолжат работу в штатном режиме.

Как быстро перенести сайт на другой сервер

В последнее время столько раз переносил сайты с одного сервера на другой, что решил всё делать через командную строку, так быстрее. Хотя есть не намного медленнее альтернативы, если совместно использовать командную строку и графический интерфейс, но решил делать одним способом. Описываю процесс.

Удобно создать себе команды в каком-нибудь защищенном менеджере паролей и оттуда в случае необходимости копировать:

или в таком формате, где-нибудь в заметках, для каждого из своих сайтов:

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

При переносе менять можно всего лишь ip адрес сервера, а пути оставлять прежними. Если вы везде пользуетесь одними инструментами, например, ISP Manager или Vesta, то пути и не меняются. Это очень удобно.

Перенос сайта

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

На старом сервере

Сначала перенесем файлы сайта.

Заходим на сервер через ssh, удобно логиниться через быстрые ярлыки. А если более профессионально и безопасно, то через SSH ключ.

Переходим в директорию, которую будем архивировать:

cd /home/admin/web/ploshadka.net/public_html/

Запаковываем в .tar.gz (сохраняем только папку сайта, без полного пути):

tar -cvvzf site.tar.gz ./

Переносим архив сайта на другой сервер:

scp site.tar.gz ssh [email protected]:/home/admin/web/ploshadka.net/public_html/

При первом подключении к стороннему серверу появится сообщение о том, что надо внести уникальный отпечаток, чтобы в последствии доверять этому серверу. Надо ответить yes.

The authenticity of host ‘111.11.11.11 (111.11.11.11)’ can’t be established.
ECDSA key fingerprint is 01:al:39:bm:19:ds:32:59:ns:49:fm:ds:59:18:38:23.
Are you sure you want to continue connecting (yes/no)?

Затем вводим пароль доступа от нового сервера:

Warning: Permanently added ‘111.11.11.11’ (ECDSA) to the list of known hosts.
[email protected]’s password:

После чего файл будет скопирован на новый сервер.

У меня при команде scp показывает ошибку:

ssh: No such file or directory

Но, если перед ней указано что-то вроде:

100%  600MB  10.5MB/s   00:57

Значит, что наш резервный файл был скопирован удачно и в нужное место.

Я не знаю с чем связано уведомление No such file or directory, когда при этом всё копируется. Может баг такой. Если вдруг директории такой и в самом деле нет, то в этом случае никакого копирования не произойдет.

Удаляем бакам на старом месте. Он нам больше не нужен.

rm -f /home/admin/web/ploshadka.net/public_html/site.tar.gz

На новом сервере

Заходим на новый сервер. А затем переходим в директорию в которую хотим распаковать архив:

cd /home/admin/web/ploshadka.net/public_html/

Распаковываем в эту папку все файлы:

tar -xvvzf /home/admin/web/ploshadka.net/public_html/site.tar.gz

Для запоминания этого набора букв, будет полезным знать их назначения.

xvvzf расшифровывается как:
x — распаковка, vv — подробный вывод результатов, z — формат файла gz, f — файл.

Удаляем наш бэкап файл:

rm -f /home/admin/web/ploshadka.net/public_html/site.tar.gz

Все команды вместе

На старом сервере:

cd /home/admin/web/ploshadka.net/public_html/
tar -cvvzf site.tar.gz ./
scp site.tar.gz ssh [email protected]:/home/admin/web/ploshadka.net/public_html/
(ввести пароль от нового сервера)
rm -f /home/admin/web/ploshadka.net/public_html/site.tar.gz

На новом:

cd /home/admin/web/ploshadka.net/public_html/
tar -xvvzf /home/admin/web/ploshadka.net/public_html/site.tar.gz
rm -f /home/admin/web/ploshadka.net/public_html/site.tar.gz

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

Теперь пришла очередь перенести базу данных сайта.

На старом сервере

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

cd /home/admin/web/ploshadka.net/public_html/

Экспортируем и архивируем базу данных. В USER и DBNAME вводим название конкретной базы данных и название пользователя для неё, которую хотим экспортировать. Вместо PASS вводим пароль от этой же базы данных.

mysqldump -u USER -pPASS DBNAME | gzip > /home/admin/web/ploshadka.net/public_html/bd.sql.gz

Переносим на новый сервер:

scp bd.sql.gz ssh [email protected]:/home/admin/web/ploshadka.net/public_html/

Вводим пароль от нового сервера. Всё перенеслось, если в конце будет подобное:

100%  162KB 162.4KB/s   00:00

Удаляем файл со старого сервера:

rm -f /home/admin/web/ploshadka.net/public_html/bd.sql.gz

На новом сервере

Импортируем базу данных. Вместо BDNAME — название нашей базы данных. Само слово root — не меняем.

zcat /home/admin/web/catavto.com/public_html/bd.sql.gz | mysql -u root -pПароль_от_рутдоступа_mysql BDNAME

Обратите внимание пароль от root доступа к MySQL. Для панели Vesta его можно найти здесь.

Удаляем файл с нового сервера:

rm -f /home/admin/web/ploshadka. net/public_html/bd.sql.gz

Все команды вместе:

На старом:

cd /home/admin/web/ploshadka.net/public_html/
mysqldump -u USER -pPASS DBNAME | gzip > /home/admin/web/ploshadka.net/public_html/bd.sql.gz
scp bd.sql.gz ssh [email protected]:/home/admin/web/ploshadka.net/public_html/
(ввести пароль от нового сервера)
rm -f /home/admin/web/ploshadka.net/public_html/bd.sql.gz

На новом:

zcat /home/admin/web/catavto.com/public_html/bd.sql.gz | mysql -u root -pПароль_от_рутдоступа_mysql BDNAME
rm -f /home/admin/web/ploshadka.net/public_html/bd.sql.gz

Перенос планов обслуживания (maintenance plans) на другой сервер

Вы когда-нибудь задавались необходимость перенести встроенные планы обслуживания (maintenance plans) на другой сервер? Эта задача оказалась не тривиальной. Сегодня я бы хотел поделиться с вами относительно простым способом решения данной задачи.

Если вы вдруг не знаете что такое планы обслуживания (maintenance plans), то это ничто иное, как пакет SQL Server Integration Services (далее SSIS), который можно создать в рамках визуального интерфейса SQL Server Management Studio (SSMS). На скриншоте вы можете увидеть, что созданный мною план обслуживания на самом деле находится на SSIS:

Я создал план обслуживания (Test_Maint) на моём SQL Server и подключился к локальному SSIS. Обратите внимание, что если у вас установлен SQL Server с экземпляром не по-умолчанию, то вам необходимо отредактировать SSIS, чтобы он подключался к нужной msdb. Для этого необходимо найти файл MsDtsSrvr.ini, обычно он находится в папке «<Program Files Installation>\Microsoft SQL Server\90\DTS\Binn. В нём необходимо поменять <ServerName>.</ServerName>, на необходимый вам сервер\экземпляр. После сохранения изменений необходимо перезапустить службу SSIS.

Продолжим, как вы видите, SSIS пакеты хранятся в базе данных msdb (они могут храниться и на файловой системе, если подключить их к SSIS особым образом). Теперь нам необходимо перенести наш план обслуживания (Test_Maint) на другой сервер, для этого необходимо выделить план > нажать правую кнопку мышки > выбрать «экспорт пакета» > в новом окне необходимо указать сервер, на который будем переносить план обслуживания и куда необходимо поместить пакет на том сервере > после всех настроек необходимо нажать «Ок». Если после нажатия «Ок», вам не выдало ошибки, значит план обслуживания успешно перенесён.

Можно сделать перенос через файловую систему, для этого нам необходимо в окне переноса выбрать «Файловую систему» и указать путь сохранения:

Теперь на сервере назначения нам так же нужно подключиться к SSIS, скопировать выгруженный план обслуживания на новый сервер и подключить его через «импорт пакета»:

С наилучшими пожеланиями.

Facebook

Twitter

Вконтакте

Google+

Передача логинов SQL для пользователей с большим количеством логинов с аутентификацией SQL

Имена входа в SQL Server — это учетные данные, которые позволяют пользователям подключаться к экземпляру компонента Database Engine. Имена входа в систему SQL различаются в зависимости от типа метода аутентификации: аутентификация Windows, аутентификация SQL Server, сертификат и асимметричный ключ. Один вход в систему SQL может быть сопоставлен только одному пользователю в каждой базе данных.

Пользователи базы данных представляют идентификатор входа в систему SQL Server при подключении к базе данных.Пользователь базы данных сопоставляется с существующим именем входа в SQL Server, за исключением автономных баз данных, когда пользователи могут подключаться к базе данных без аутентификации имени входа на уровне компонента Database Engine. Из-за изоляции от компонента Database Engine содержащиеся базы данных можно легко перенести на другой экземпляр SQL Server.

В автономной базе данных при выполнении миграции SQL с одного экземпляра SQL Server на другой необходимо также выполнить миграцию учетных записей SQL, чтобы предотвратить потерю пользователей базы данных, настроенных для этой базы данных.Пользователь-сирота — это пользователь без соответствующего входа в систему SQL.

Для доступа к SQL Server требуется вход в систему SQL. Вход в систему SQL без соответствующего пользователя в конкретной базе данных не может подключиться к базе данных, хотя он может подключиться к серверу, а потерянный пользователь не может подключиться к серверу.

Информация о сопоставлении имени входа SQL Server пользователю базы данных включает имя пользователя базы данных и идентификатор безопасности (SID) соответствующего входа в систему SQL:

Мастер импорта и экспорта нельзя использовать для копирования учетных записей из одного экземпляра SQL Server в другой.Кроме того, при резервном копировании и восстановлении базы данных данные входа в систему не копируются, поскольку имена входа SQL хранятся вне базы данных.

Передача имен входа SQL с помощью мастера копирования базы данных

Чтобы использовать мастер копирования базы данных для переноса имен входа SQL:

1. Подключение к исходному и целевому экземплярам SQL Server в SQL Server Management Studio

2. Запустите агент SQL Server на конечном экземпляре SQL Server:

3. Щелкните правой кнопкой мыши базу данных на исходном экземпляре SQL Server, из которого необходимо перенести учетные записи SQL, выберите Tasks и щелкните команду Copy Database , чтобы запустить Copy Database Wizard :

4. Добро пожаловать в мастер копирования базы данных отображается как первый шаг мастера, поэтому нажмите кнопку Далее :

5. На шаге Select a Source Serve r укажите исходный экземпляр SQL Server и нажмите кнопку Next :

6. На шаге Select a Destination Server укажите целевой экземпляр SQL Server и нажмите кнопку Next :

7. Выберите метод передачи. Шаг позволяет пользователям выбрать метод передачи, и в этом примере выбран вариант Использовать метод отсоединения и присоединения и нажмите кнопку Далее :

8. На шаге Select Databases проверьте, какие базы данных необходимо переместить или скопировать, и нажмите кнопку Next :

9. На шаге Настроить целевую базу данных укажите имена файлов базы данных и необходимость перезаписи существующей базы данных в месте назначения и нажмите кнопку Далее :

10. На шаге Select Server Objects выберите, какие объекты копировать, и, как показано, логины выбираются по умолчанию:

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

11. Настроить пакет На шаге создается пакет службы интеграции со свойствами, которые можно указать ниже, или оставить значения по умолчанию и нажать кнопку Далее :

12. Запланировать пакет. Шаг позволяет пользователю Запустить немедленно или Запланировать пакет службы интеграции , созданный на предыдущем шаге.В этом примере выбрана опция Запустить немедленно и нажаты кнопки Далее :

13. Последний шаг — это Завершение мастера , в котором отображается полная сводка выбранных действий в мастере копирования базы данных и, если все в порядке, нажмите кнопку Завершить , чтобы начать копирование базы данных:

Передача учетных записей SQL с помощью метода входа в скрипт как

Использование сценария в качестве функции в SQL Server Management Studio — это простой способ воссоздать аутентифицированные учетные записи SQL для Windows.

Чтобы создать сценарий для передачи имени входа SQL в новый экземпляр:

1. На панели Object Explorer разверните Security -> Logins node

2. Щелкните правой кнопкой мыши имя входа SQL, которое необходимо перенести, и выберите Script Login как -> CREATE To -> New Query Editor Window Команда :

Это создает сценарий в новом окне запроса, который можно запустить на конечном экземпляре SQL Server.R | » î_ƒK¤_¶qù [(‘, DEFAULT_DATABASE = [AdventureWorks2017],
DEFAULT_LANGUAGE = [us_english],
CHECK_EXPIRATION = OFF, CHECK_POLICY = ON
ИДТИ

ИЗМЕНИТЬ ВХОД [dnnuatuser] ОТКЛЮЧИТЬ
GO

В этом случае необходимо будет сбросить пароли, связанные с входами в систему SQL, прежде чем пользователь, связанный с входом в систему, сможет подключиться. Кроме того, необходимо включить вход в систему, потому что даже после того, как участник сброса пароля, назначенный для входа, не сможет войти в целевой экземпляр:

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

Передача учетных записей SQL с помощью сценария, опубликованного в статье базы знаний Майкрософт KB918992

Microsoft предоставляет сценарий, который создаст две хранимые процедуры в базе данных master: sp_hexadecimal и sp_help_revlogin. Чтобы экспортировать имена входа SQL из экземпляра SQL Server, сначала выполните хранимую процедуру sp_help_revlogin:

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

Если исходный экземпляр SQL Server нечувствителен к регистру, а конечный экземпляр SQL Server чувствителен к регистру, пользователи должны вводить пароли заглавными буквами после передачи имен входа и паролей SQL в экземпляр на целевом сервере. В обратном случае пользователи не могут войти в систему, используя логины и пароли, переданные экземпляру на целевом сервере, если исходные пароли не содержат букв или если все буквы в исходных паролях не являются заглавными.

Если учетные записи SQL передаются в целевой экземпляр SQL Server в другом домене, сценарий необходимо отредактировать. Обратите внимание, что для интегрированных учетных записей, которым предоставлен доступ в новом домене, не будет того же SID, что и для учетных записей в исходном домене, а пользователи базы данных для этих учетных записей будут потеряны. Кроме того, имя входа, которое уже существует в конечном экземпляре SQL Server, может иметь тот же SID, что и SID в выходном сценарии. В этом случае, когда выходной скрипт выполняется в экземпляре назначения, он сообщит следующее сообщение:

Msg 15433, уровень 16, состояние 1, строка 6
Поставляемый параметр sid используется.

Выходной сценарий процедуры sp_help_revlogin необходимо тщательно проверить перед выполнением в целевом экземпляре.

Передача учетных записей SQL с помощью сценария ApexSQL

ApexSQL Script — это инструмент миграции базы данных SQL Server, который преобразует объекты базы данных и данные в один или несколько сценариев SQL, решения C # или исполняемые программы установки. Используя сценарий ApexSQL, вы можете создать сценарий SQL для миграции выбранных пользователей базы данных со всеми назначенными именами входа SQL.

Чтобы передать логины SQL с помощью ApexSQL Script , выполните следующие действия:

1. Запустите ApexSQL Script, и откроется окно Новый проект . Укажите SQL Server, нажмите кнопку Connect и отметьте нужную базу данных:

2. Перейдите на вкладку Options и в разделе Structure отметьте Login with user option и нажмите кнопку Load :

3. После загрузки базы данных в представлении Структура прокрутите вниз по типам объектов, найдите пользователей для миграции и нажмите кнопку Сценарий , чтобы запустить мастер сценариев :

4. На первых двух шагах мастера сценариев выберите режим сценария Structure и тип вывода сценария SQL :

5. На шаге Dependencies просто нажмите кнопку Next , так как в этом случае не будет никаких зависимых объектов:

6. На последнем шаге мастера сценариев , шаг параметров сценария SQL , выберите параметр Открыть сценарий в редакторе и нажмите кнопку Создать :

7. Скрипт создает и пользователя, и соответствующий логин:

8. Чтобы выполнить сгенерированный сценарий на целевом экземпляре SQL Server, нажмите кнопку Connect , откроется диалоговое окно Connect to SQL Server , в котором необходимо ввести поля Server , Authentication type и Database , а затем нажмите кнопку Connect :

9. После подключения к SQL Server и базе данных нажмите кнопку Execute , и сообщения о выполнении сценария будут отображаться на панели сообщений :

Выполнение этого сценария SQL в другом домене не требует изменений в сценарии, поскольку сценарий ApexSQL позволяет пользователям выбирать только пользователей и имена для входа. Однако пароли для входа в систему с аутентификацией SQL Server необходимо будет сбросить вручную. Пользователи находятся во включенном состоянии и могут войти в целевой экземпляр:

28 апреля 2014 г.

TMS SQL Database Migration с одного SQL Server на другой SQL Server

Введение

В этом документе описывается, как перенести базу данных TMS SQL с одного сервера SQL на другой.

Предварительные требования

Требования

Cisco рекомендует знать следующие темы:

• TMS (Cisco TelePresence Management Suite)
• Инструменты TMS
• SQL-сервер
• SQL Server Management Studio

Используемые компоненты

Информация в этом документе основана на следующих версиях программного и аппаратного обеспечения:

• ТМС 15.3
• SQL-сервер 2012
• SQL Server Management Studio

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

Справочная информация

Вам понадобится консольный / RDP-доступ к серверу TMS и учетные данные SQL с ролью сервера sysadmin .Процесс займет минимум 1 час простоя.

Настроить

Выполните шаги, чтобы успешно перенести базу данных TMS SQL с одного сервера SQL на другой.

Схема сети

На следующем изображении показан пример процесса миграции:

Шаг 1. Остановите службы приложений TMS, включая IIS и WWW

Чтобы найти и остановить службы, обратитесь к серверу TMS через консоль или соединение RDP и выполните команду services.msc в Command Propmpt . В списке услуг найдите те, которые находятся ниже, и щелкните каждую из них правой кнопкой мыши, чтобы остановить ее:

• TMSDatabaseScannerService
• TMSLiveService
• TMSPLCMDirectoryService
• TMSSchedulerService
• TMSServerDiagnosticsService
• TMSSnmpService
• Служба публикации в Интернете (W3SVC)
• IISADMIN (необязательно)

Внимание : Прежде чем двигаться дальше, убедитесь, что все необходимые службы остановлены.

Шаг 2. Сделайте резервную копию базы данных TMSNG с существующего SQL Server

Чтобы создать резервную копию текущей базы данных SQL, выполните процедуру.

1. Войдите в SQL Server Management Studio с учетными данными sa (или используйте учетные данные, которые имеют доступ системного администратора к базе данных).

2. Найдите базу данных, для которой вы хотите создать резервную копию (например, tmsng , tmspe ) и щелкните ее правой кнопкой мыши, чтобы выбрать Задачи> Резервное копирование .

3. Откроется новое окно. Вы должны удалить существующий путь и добавить новый путь, где будет сохранен новый файл резервной копии. Тип резервной копии должен быть установлен как Полная .

• Нажмите кнопку Удалить , чтобы удалить существующий путь

• Нажмите кнопку Добавить , чтобы добавить новый путь к новому файлу резервной копии

• Перейдите в новое место и введите имя для Имя файла .Нажмите ОК

• Новое сообщение появится после успешного завершения резервного копирования

4. Выполните тот же процесс для базы данных TMSPE (необязательно).

Шаг 3. Восстановите резервную копию до нового SQL Server

Чтобы применить резервную копию базы данных к новому серверу SQL, выполните описанную ниже процедуру.

Внимание : Убедитесь, что на сервере TMS и новом сервере SQL настроено одинаковое время (часовой пояс).

1. Откройте SQL Server Management Studio и щелкните правой кнопкой мыши Базы данных . Выберите Восстановить базу данных , и откроется новое окно.

2. Вы должны выбрать исходное устройство. Нажмите кнопку справа от поля Device и нажмите Добавить в новом всплывающем окне. Выберите папку и файл резервной копии, а затем нажмите ОК .

Шаг 4. Измените настройку подключения со старого SQL-сервера на новый SQL-сервер.

1.Получите доступ к серверу TMS через консоль или соединение RDP.

2. Откройте Инструменты TMS и перейдите к Конфигурация > Подключение к базе данных Cisco TMS .

3. В поле Сервер базы данных \ экземпляр вы должны ввести данные нового соединения для SQL-сервера.

• Пример перед изменением деталей подключения старого SQL-сервера

• Пример после изменения деталей подключения к новому серверу SQL

Шаг 5.Запустить все службы, которые были остановлены ранее на шаге 1

Чтобы найти и запустить службы, обратитесь к серверу TMS через консоль или соединение RDP и запустите команду services.msc в командной строке . В списке услуг найдите те, которые находятся ниже, и щелкните каждую из них правой кнопкой мыши, чтобы запустить ее:

• TMSDatabaseScannerService
• TMSLiveService
• TMSPLCMDirectoryService
• TMSSchedulerService
• TMSServerDiagnosticsService
• TMSSnmpService
• Служба публикации в Интернете (W3SVC)
• IISADMIN (необязательно)

Проверить

После успешного изменения сведений о подключении для отображения нового сервера SQL вы увидите сообщение «Параметры подключения к базе данных TMS были успешно изменены» зеленого цвета.

Чтобы увидеть новую информацию о сервере SQL, перейдите в веб-интерфейс TMS> Администрирование> Обслуживание сервера TMS и разверните раздел Файлы базы данных и информация о размере .

Устранение неполадок

В настоящее время для этой конфигурации нет специальной информации по поиску и устранению неисправностей.

Подключение к нескольким серверам в окне запроса с помощью SQLCMD

Включив режим SQLCMD в SQL Server Management Studio, вы можете добавить некоторые полезные дополнительные функции сценариев, которые
доступно с T-SQL.Одна особенно полезная функция — возможность переключаться между различными серверами SQL в окне запроса. Без
SQLCMD, обычный способ сделать это — открыть окно «Подключиться к ядру СУБД» (например, щелкнув правой кнопкой мыши и выбрав
«Изменить соединение»). Это может стать довольно утомительным, особенно если необходимо подключиться к нескольким серверам SQL. После включения SQLCMD
это можно написать в окне запроса.

Включение режима SQLCMD

Чтобы включить режим SQLCMD (по умолчанию он отключен), просто выберите «Режим SQLCMD» в меню запросов в SSMS, как показано ниже:

Использование SQLCMD для переключения серверов в Management Studio

Все команды SQLCMD начинаются с двоеточия, а команда для смены серверов — «CONNECT».Чтобы перейти на локальный сервер, используя
localhost используйте следующую команду в окне запроса базы данных:

Обратите внимание: если SSMS intellisense не делает фон серым, скорее всего, вы не включили режим SQLCMD.

В качестве альтернативы для подключения к определенному серверу (в данном случае SQLSERVER1) используйте:

Или для экземпляра не по умолчанию (в данном случае INSTANCE1 на сервере SQLSERVER1):

: ПОДКЛЮЧИТЬ SQLSERVER1 \ INSTANCE1

Вышеупомянутые команды предполагают, что вы используете проверку подлинности Windows, если вы хотите использовать проверку подлинности SQL Server и указать логин и пароль, вы можете использовать следующий синтаксис:

: ПОДКЛЮЧИТЬ SQLSERVER1 -U MyLoginName -P MyPassword

Практический пример

В качестве примера описанный выше метод можно использовать для получения списка всех баз данных на каждом экземпляре SQL Server.В моем случае у меня есть три SQL-сервера, и они называются SQLMATTERS1, SQLMATTERS2 и SQLMATTERS3. Я хочу подключиться к каждому серверу, используя
Проверка подлинности Windows и выполните следующий запрос:

ВЫБРАТЬ * ИЗ sys.databases

Я могу сделать это в SQL Server Management Studio, используя описанные выше методы. SQL и результаты показаны в
снимок экрана ниже, который, надеюсь, не требует пояснений:

Последнее примечание — Использование разделителя партий «GO»

При подключении к нескольким серверам важно завершать каждый набор выполняемых операторов с помощью «GO».
терминатор пакета перед переключением сервера с
Заявление CONNECT.Если вы этого не сделаете, запрос может быть запущен не на правильном сервере.

Как экспортировать базу данных MS SQL Server в сценарий SQL (из базы данных в файл sql) с помощью Microsoft SQL Server Management Studio 17

Большинство разработчиков не чувствуют себя комфортно с файлами .bak , созданными с помощью опции экспорта резервной копии SQL Server, по крайней мере, для не очень больших баз данных. По этой причине вам может потребоваться иногда экспортировать базу данных в формате скрипта (.sql). Однако попытка экспортировать его в этом формате может стать головной болью для некоторых людей, которые не знакомы с SQL Server Managemente Studio.

В этой статье мы кратко объясним, как легко экспортировать структуру и данные из базы данных, хранящейся в SQL Server, в файл .sql .

Создание файла .sql вашей базы данных с данными

SQL Server Management Studio предоставляет возможность создавать сценарии для создания баз данных, таблиц, хранимых процедур, функций, представлений и вставки данных. Для небольших объемов данных этот метод можно использовать для отправки структур таблиц и / или данных в службу поддержки Scribe для целей тестирования.

Чтобы продолжить, откройте SSMS и получите доступ к ядру базы данных с аутентификацией Windows по умолчанию (или через соединение, к которому вы хотите получить доступ):

Подключитесь к серверу и исследуйте базы данных в проводнике объектов. В нашем случае мы хотим экспортировать базу данных my_database , которая, как видите, доступна в каталоге Databases:

Щелкните правой кнопкой мыши базу данных и выберите «Задачи», а затем «Создать сценарии»:

Откроется диалоговое окно «Создание и публикация сценариев».В качестве первого шага определите, какие таблицы базы данных вы хотите экспортировать, при желании вы можете создать скрипт для всей базы данных и объектов:

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

Наконец, начнется экспорт, и вы увидите сообщение об успешном завершении:

, и если вы отредактируете сценарий создания .sql , вы увидите простой текст SQL:

  ИСПОЛЬЗОВАТЬ [master]
 ИДТИ
 / ****** Объект: База данных [my_database] Дата сценария: 12 марта-19 19:07:12 ****** /
 СОЗДАТЬ БАЗУ ДАННЫХ [my_database]
 СОДЕРЖАНИЕ = НЕТ
 НА ПЕРВИЧНОМ
 (NAME = N'SICitas ', FILENAME = N'C: \ Program Files \ Microsoft SQL Server \ MSSQL14.SQLEXPRESS \ MSSQL \ DATA \ my_database.mdf', SIZE = 24512KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 ВОЙТИ
 (ИМЯ = N'SICitas_log ', ИМЯ ФАЙЛА = N'C: \ Program Files \ Microsoft SQL Server \ MSSQL14.SQLEXPRESS \ MSSQL \ DATA \ my_database_1.LDF ', SIZE = 32448KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 ИДТИ
 ALTER DATABASE [my_database] SET COMPATIBILITY_LEVEL = 100
 ИДТИ
 ЕСЛИ (1 = FULLTEXTSERVICEPROPERTY ('IsFullTextInstalled'))
 начать
 EXEC [my_database]. [Dbo]. [Sp_fulltext_database] @action = 'disable'
 конец
 ИДТИ
 ALTER DATABASE [my_database] ВЫКЛЮЧИТЬ ANSI_NULL_DEFAULT
 ИДТИ
 ALTER DATABASE [my_database] ВЫКЛЮЧИТЬ ANSI_NULLS
 ИДТИ
 ALTER DATABASE [my_database] ВЫКЛЮЧИТЬ ANSI_PADDING
 ИДТИ
 ALTER DATABASE [my_database] ВЫКЛЮЧИТЬ ANSI_WARNINGS
 ИДТИ
 ALTER DATABASE [my_database] ВЫКЛЮЧИТЬ ARITHABORT
 ИДТИ
 ALTER DATABASE [my_database] SET AUTO_CLOSE ON
 ИДТИ
.
No related posts.