kutsoff.ru. Перевести sql базу в однопользовательский режим


Установка однопользовательского режима базы данных

 

В этом разделе описывается, как установить однопользовательский режим в SQL Server 2016 при помощи среды SQL Server Management Studio или Transact-SQL. Однопользовательский режим указывает, что одновременный доступ к базе данных получает только один пользователь. Это в основном используется для операций обслуживания.

В этом разделе

  • Перед началом работы выполните следующие действия.

    Ограничения

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

    Безопасность

  • Установка однопользовательского режима базы данных с помощью

    Среда SQL Server Management Studio

    Transact-SQL

Ограничения

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

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

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

  • Перед заданием параметра SINGLE_USER проверьте, чтобы параметру AUTO_UPDATE_STATISTICS_ASYNC было присвоено значение OFF. Если этот параметр имеет значение ON, то фоновый поток, используемый для обновления статистики, соединится с базой данных и доступ к базе данных в однопользовательском режиме будет невозможен. Дополнительные сведения см. в разделе Параметры ALTER DATABASE SET (Transact-SQL).

Безопасность

Разрешения

Необходимо разрешение ALTER на базу данных.

Установка однопользовательского режима базы данных
  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент SQL Server Database Engineи разверните его.

  2. Щелкните правой кнопкой мыши базу данных, которую нужно изменить, и выберите пункт Свойства.

  3. В диалоговом окне Свойства базы данных выберите страницу Параметры .

  4. Для параметра Ограничение доступа выберите Один.

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

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

Установка однопользовательского режима базы данных
  1. Установите соединение с компонентом Компонент Database Engine.

  2. На панели «Стандартная» нажмите Создать запрос.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере база данных устанавливается в режим SINGLE_USER для получения монопольного доступа. Затем состояние базы данных AdventureWorks2012 устанавливается в READ_ONLY и возвращается доступ к базе данных всем пользователям. Параметр прекращения WITH ROLLBACK IMMEDIATE указывается в первой инструкции ALTER DATABASE . Произойдет откат всех незавершенных транзакций, а любые другие соединения с базой данных AdventureWorks2012 будут немедленно разорваны.

USE master; GO ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE AdventureWorks2012 SET READ_ONLY; GO ALTER DATABASE AdventureWorks2012 SET MULTI_USER; GO

ALTER DATABASE (Transact-SQL)

technet.microsoft.com

Восстановление доступа к MS SQL серверу в однопользовательском режиме

  • Разместил TangaRUS
  • Опубликовано: 5 мая 2011 в 13:42 Обновлено: 5 мая 2011 в 13:44

В процессе установки MS Sharepoint 2010 создаёт свою БД таким образом, что локальные администраторы:

— не наделяются правами sysadmin в SQL Server— используется только Windows аутентификация— sa выключен и пароль его неизвестен

С использованием однопользовательского административного режима SQL Server 2008 это довольно просто обойти.

  1. Входим на сервер с правами локального администратора
  2. Останавливаем все инстансы SQL сервера и приложения которые с ним работают (и могут залогиниться на него раньше вас)
  3. Устанавливаем смешанный режим аутентификации SQL сервера.Для этого изменить в реестре параметр «HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL Server\Login mode» на 2.Это нужно делать именно для нужного инстанса.
  4. Запускаем SQL сервер в однопользовательском режиме.Открываем командную строку (cmd) с правами администратора (правой кнопкой — Run as Administrator).Переходим в каталог с исполняемыми файлами соответствующего инстанса SQL сервера, например: «C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn».Выполняем sqlservr.exe -m -f. (В моём случае понадобилось вручную указать путь к .mdf и .ldf, через ключи -d и -l соответвенно).Дожидаемся появления «SQL Server is now ready for client connect. Recovery is complete.»НЕ ЗАКРЫВАТЬ ОКНО ДО ЗАВЕРШЕНИЯ ВСЕЙ ПРОЦЕДУРЫ!
  5. Запускаем ещё одно окно cmd и выполняем:osql -EAlter login sa ENABLEGOAlter login sa WITH PASSWORD = «P@ssw0rd»GOEXITЕсли всё верно, после «GO» номер строки будет перескакивать на «1».
  6. Запускаем SQL Management Studio, подключаемся используя аутентификацию SQL сервера и установленный выше пароль для учётной записи «sa».
  7. Добавляем нужных пользователей и назначаем им права.
  8. Нажимаем Ctrl+C в первом окне cmd, соглашаемся с остановкой SQL сервера.
  9. Штатным порядком запускаем службы SQL.
  10. По желанию, отключаем обратно «sa» и меняем режим аутентификации

www.tangarus.ru

Восстановление MS SQL базы данных из состояния SUSPECT — kutsoff.ru

Рассмотрим несколько вариантов восстановления БД, в зависимости от того, насколько повреждены файлы БД зависит успешность того или иного метода. Все описанные способы были лично мной проверены на практике и все случаи восстановления, за исключением одного, были успешны. Используйте данное руководство на свой страх и риск, за совершенные вами действия ответственность несете, вы сами.Итак, во-первых останавливаем службу SQL Server и копируем файлы базы данных (*.mdf и *.ldf) в другую папку, чтобы можно было восстановить их в случае неудачи.Если у вас есть свежий актуальный бэкап, то дальше можете не читать, а просто восстановите БД из него, тем самым сэкономите драгоценное время, далее я приведу алгоритмы восстановления для разных версий SQL Server. Надеюсь вам это поможет, как в свое время помогло и мне.Для всех версий SQL Server подойдет следующий вариант: делаем Detach database(отсоединить базу данных), удаляем журнал транзакций(файл с расширением ldf) и делаем Attach database(присоединить базу данных). В мастере выбираем наш mdf файл и жмем ОК.

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

Присоединить один mdf файл без журнала транзакций, если у вас SQL 2005 и выше, можно командами

EXEC sp_attach_single_file_db @dbname='db_name', @physname='D:Dbdb_name.mdf'

EXEC sp_attach_single_file_db @dbname='db_name', @physname='D:Dbdb_name.mdf'

или

CREATE DATABASE db_name ON (filename = 'd:dbdb_name.mdf') FOR ATTACH_REBUILD_LOG

CREATE DATABASE db_name

    ON (filename = 'd:dbdb_name.mdf')

    FOR ATTACH_REBUILD_LOG

Если не получилось, то создаем новую базу данных с таким же именем, останавливаем сервер. Подменяем файл mdf файлом от нашей базы, стартуем службу SQL Server и открываем Query analyzer(SQL 2000) или Management studio(SQL 2005/2008) в зависимости от нашей версии сервера.Пишем следующее:

Use master go sp_configure 'allow updates', 1 reconfigure with override go

Use master

go

sp_configure 'allow updates', 1

reconfigure with override

go

Если у вас SQL 2000, то далее пишем:

update sysdatabases set status= 32768 where name = 'db_name' GO

update sysdatabases set status= 32768 where name = 'db_name'

GO

Если SQL 2005 или 2008, то пишем:

ALTER DATABASE db_name SET EMERGENCY, SINGLE_USER GO

ALTER DATABASE db_name SET EMERGENCY, SINGLE_USER

GO

где вместо db_name пишем имя своей БДЖмем F5. После этого наша БД должна быть видна в статусе EMERGENCY. Отлично, приступаем к восстановлению.Все что написали стираем, чтобы не смущало, и пишем.

Для SQL 2000:

DBCC REBUILD_LOG('db_name', 'Полный путь к новому файлу ldf') GO

DBCC REBUILD_LOG('db_name', 'Полный путь к новому файлу ldf')

GO

Жмем F5, если все нормально, сервер скажет: Warning: The log for database ‘db_name’ has been rebuilt.

Стираем и пишем:

Use master go sp_dboption 'db_name', 'single user', 'true' go USE db_name GO DBCC CHECKDB('db_name', REPAIR_REBUILD) go

Use master

go

sp_dboption 'db_name', 'single user', 'true'

go

USE db_name

GO

DBCC CHECKDB('db_name', REPAIR_REBUILD)

go

если DBCC не хочет выполняться, то вместо REPAIR_REBUILD нужно подставить REPAIR_ALLOW_DATA_LOSSЖмем F5, ждем некоторое время. Сервер вернет кучу сообщений. Если там будут содержаться ошибки, то лучше еще раз выполнить DBCC CHECKDB с параметром REPAIR_REBUILD, пока все ошибки не будут устранены.

Upd: как подсказал Anim в комментах ниже, если и в этом случае запрос не хочет выполняться, БД не хочет переходить в Single User Mode, то можно попробовать следующее:

use db_name alter database 'db_name' set SINGLE_USER with rollback immediate go USE db_name GO DBCC CHECKDB('db_name', REPAIR_REBUILD) go

use db_name

alter database 'db_name' set SINGLE_USER with rollback immediate

go

USE db_name

GO

DBCC CHECKDB('db_name', REPAIR_REBUILD)

go

Для SQL 2005/2008 действия несколько иные:

DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS) GO

DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS)

GO

Тут без вариантов. В SQL 2005 и выше нет инструкции REBUILD_LOG, вместо этого выполняется CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS.После того как сервер закончит выполнять запрос и вернет результат, меняем REPAIR_ALLOW_DATA_LOSS на REPAIR_REBUILD и выполняем запрос еще раз, это должно убрать оставшиеся ошибки в бд.После всего этого наша база становится в нормальное состояние и уже доступна для работы с ней, но только в однопользовательском режиме, поэтому завершаем наш процесс возвращением бд в многопользовательский режим.Пишем:Для SQL 2000:

Use master go sp_dboption 'db_name', 'single user', 'false' go

Use master

go

sp_dboption 'db_name', 'single user', 'false'

go

Для SQL 2005/2008:

alter database db_name set ONLINE, MULTI_USER GO

alter database db_name set ONLINE, MULTI_USER

GO

Все. База онлайн и готова к работе. Радуемся и не забываем делать бэкапы.Большинство информации в свое время было найдено на форуме sql.ru

kutsoff.ru

Запуск SQL Server в однопользовательском режиме

 

При определенных обстоятельствах экземпляр SQL Server нужно запустить в однопользовательском режиме (используется параметр запуска -m). Например, может понадобиться изменить параметры конфигурации сервера, восстановить поврежденную базу данных master или другую системную базу данных. Для обоих этих действий необходим запуск экземпляра SQL Server в однопользовательском режиме.

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

При запуске экземпляра SQL Server в однопользовательском режиме необходимо обратить внимание на следующее:

  • Только один пользователь может подключиться к серверу.

  • Процесс CHECKPOINT не выполняется. По умолчанию он автоматически выполняется при запуске.

 Примечание

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

Если экземпляр SQL Server запускается в однопользовательском режиме, среда SQL Server Management Studio может подключаться к SQL Server. Обозреватель объектов в среде Среда Management Studio может вызвать ошибку, так как для некоторых операций ему необходимо одновременно несколько соединений. Чтобы управлять SQL Server в однопользовательском режиме, выполняйте инструкции Transact-SQL, подключаясь только через редактор запросов в среде Среда Management Studio, или используйте программу sqlcmd.

При использовании параметра -m с sqlcmd или Среда Management Studio вы можете ограничить подключения к определенному клиентскому приложению. Например, -m"sqlcmd" разрешает только одно подключение, которое должно идентифицироваться как клиентская программа sqlcmd. Этот параметр следует использовать, когда SQL Server запускается в однопользовательском режиме, а единственное доступное соединение занято неизвестным клиентским приложением. Чтобы подключиться с помощью редактора запросов в Среда Management Studio, используйте -m"Microsoft SQL Server Management Studio - Query".

 Важно

Не используйте этот параметр как средство безопасности. Клиентское приложение предоставляет имя клиентского приложения и может указать ложное имя в составе строки подключения.

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

Для решения этой проблемы используется следующая процедура.

  1. Удалите параметр запуска –m из дополнительных свойств SQL Server.

  2. Переведите ресурс SQL Server в режим «вне сети».

  3. С текущего узла владельца этой группы выполните в командной строке следующую команду:net start MSSQLSERVER /m.

  4. Уточните у администратора кластера или с помощью консоли управления отказоустойчивым кластером, остается ли ресурс SQL Server в режиме «вне сети».

  5. Теперь подключитесь к SQL Server и выполните необходимую операцию: SQLCMD -E -S<имя_сервера>.

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

Запуск, остановка или приостановка службы агента SQL Server Диагностическое соединение для администраторов баз данных Программа sqlcmd CHECKPOINT (Transact-SQL) sp_configure (Transact-SQL) Параметры запуска службы Database Engine

technet.microsoft.com