Сборник полезных скриптов: T-SQL, PowerShell и CMD для администратора

 Этот SQL-запрос выполняет резервное копирование и восстановление базы данных в Microsoft SQL Server. Он включает в себя несколько ключевых шагов:

1. Объявление переменных: Переменные используются для хранения имен баз данных, путей к файлам резервных копий и команд SQL, необходимых для выполнения операций.

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

3. Создание базы данных, если она не существует: Проверяется наличие базы данных назначения, и если она отсутствует, создается новая база данных.

4. Создание резервной копии: Выполняется резервное копирование исходной базы данных в указанный файл.

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

6. Восстановление базы данных: База данных назначения восстанавливается из файла резервной копии, с указанием перемещения файлов данных и логов.

7. Переключение базы данных обратно в многопользовательский режим: После восстановления база данных переводится обратно в многопользовательский режим.

8. Удаление файла резервной копии: Проверяется наличие файла резервной копии, и если он существует, файл удаляется.

9. Удаление записи о резервной копии из msdb: Из системной базы данных msdb удаляется запись о выполненной резервной копии, если она существует.

Дополнительные операции:

2. Уменьшение данных журнала (SHRINK): Процедура уменьшения размера журнала транзакций для освобождения неиспользуемого пространства.

3. Уменьшение данных журнала всех баз MSSQL: Выполнение операции SHRINK для всех баз данных на сервере.

4. Переименование базы данных: Изменение имени существующей базы данных.

5. Сжатие базы данных и лог файла: Уменьшение размера базы данных и лог файла, а также установка максимального размера лог файла.

6. Перевод всех баз в режим SIMPLE: Изменение режима восстановления всех баз данных на SIMPLE, что упрощает управление журналом транзакций, с последующим сжатием.

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

-- Объявление переменных
DECLARE @from NVARCHAR(MAX);
DECLARE @from_log NVARCHAR(MAX);
DECLARE @to NVARCHAR(MAX);
DECLARE @to_mdf NVARCHAR(MAX);
DECLARE @to_log NVARCHAR(MAX);
DECLARE @backup_result NVARCHAR(MAX);
DECLARE @backup_folder NVARCHAR(MAX);
DECLARE @BackupSetID INT;
DECLARE @alterSingle NVARCHAR(MAX);
DECLARE @alterMulti NVARCHAR(MAX);
DECLARE @restoreCmd NVARCHAR(MAX);

-- Установка значений переменных
SET @from = N'ИмяБазыОткуда'; -- что бэкапим
SET @to = N'ИмяБазыКуда'; -- куда восстанавливаем
SET @from_log = @from + '_log'; -- имя лог файла источника
SET @backup_folder = N'D:\Base\MSSQL14.MSSQLSERVER\MSSQL\Backup\'; -- каталог бэкапа
SET @backup_result = @backup_folder + @from + '.bak'; -- имя бэкап файла
SET @to_mdf = @backup_folder + @to + '.mdf'; -- имя файла базы приемника
SET @to_log = @backup_folder + @to + '_log.ldf'; -- имя лог файла базы приемника
SET @alterSingle = N'ALTER DATABASE ' + QUOTENAME(@to) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
SET @alterMulti = N'ALTER DATABASE ' + QUOTENAME(@to) + ' SET MULTI_USER;';

-- Создаем базу данных если она не найдена
IF NOT EXISTS (SELECT name FROM master.sys.databases WHERE name = @to)
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'CREATE DATABASE ' + QUOTENAME(@to);
    EXEC sp_executesql @sql;
END

-- Создание файла бэкапа
BACKUP DATABASE @from TO DISK = @backup_result;

-- Изменение на одиночный режим базы приемника для избежания ошибки соединения с базой
EXEC sp_executesql @alterSingle;

-- Восстановление базы приемника
SET @restoreCmd = 'RESTORE DATABASE ' + QUOTENAME(@to) + ' FROM DISK = ''' + @backup_result + ''' WITH MOVE ''' + @from + ''' TO ''' + @to_mdf + ''', MOVE ''' + @from_log + ''' TO ''' + @to_log + ''', REPLACE, RECOVERY';
EXEC sp_executesql @restoreCmd;

-- Изменение базы приемника на многопользовательский режим
EXEC sp_executesql @alterMulti;

-- Проверка существования файла перед удалением
IF EXISTS (SELECT * FROM sys.master_files WHERE physical_name = @backup_result)
BEGIN
    -- Удаление файла бекапа с диска
    EXEC master.dbo.xp_delete_file 0, @backup_result;
END
ELSE
BEGIN
    PRINT 'Файл бэкапа не найден: ' + @backup_result;
END

-- Получение ID из msdb и удаление записи о файле бекапа из msdb
SELECT @BackupSetID = backup_set_id FROM msdb.dbo.backupset AS history WHERE database_name = @from AND recovery_model = 'SIMPLE';
IF ISNUMERIC(@BackupSetID) = 1
BEGIN
    DECLARE @BackupSetIDInt INT = CAST(@BackupSetID AS INT);
    EXEC msdb.dbo.sp_delete_backuphistory @BackupSetIDInt;
END
ELSE
BEGIN
    PRINT 'Переменная @BackupSetID не содержит числовое значение.';
END

Этот SQL-запрос выполняет изменение режима восстановления базы данных и уменьшение размера журнала транзакций в Microsoft SQL Server. Вот что делает каждый шаг:

1. Изменение режима восстановления на SIMPLE:

   ALTER DATABASE [ИмяБазы]
   SET RECOVERY SIMPLE
   GO
   

   CopyInsert

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

2. Уменьшение размера файла журнала транзакций:

   DBCC SHRINKFILE ([ИмяБазы_log], 1)
   GO
   

    

   • Уменьшает размер файла журнала транзакций до минимально возможного размера (в данном случае 1 МБ). Это освобождает неиспользуемое пространство в файле журнала.

3. Изменение режима восстановления обратно на FULL:

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

Применение:

• Когда использовать: Этот процесс полезен, когда необходимо временно уменьшить размер журнала транзакций, например, перед выполнением больших операций, которые могут значительно увеличить журнал.
• Предостережения: После изменения режима восстановления на SIMPLE и обратно на FULL, необходимо выполнить полное резервное копирование базы данных, чтобы обеспечить возможность восстановления с использованием журнала транзакций.

ALTER DATABASE ['ИмяБазы']
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([ИмяБазы_log], 1)
GO
ALTER DATABASE [ИмяБазы]
SET RECOVERY FULL

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

1. Объявление переменных:

   DECLARE @DBName varchar(255);
   DECLARE @SQLCommand nvarchar(500);
   

   Переменные @DBName и @SQLCommand используются для хранения имени текущей базы данных и команды SQL, которую нужно выполнить.

2. Создание курсора для перебора всех баз данных

3. Курсор DB_Cursor инициализируется для перебора всех баз данных, которые находятся в состоянии ONLINE.

4. Открытие курсора и начало цикла:

   OPEN DB_Cursor;  
   FETCH NEXT FROM DB_Cursor INTO @DBName;
   

   Курсор открывается, и первая база данных извлекается в переменную @DBName.

5. Цикл обработки каждой базы данных:

   WHILE @@FETCH_STATUS = 0  
   BEGIN  
       SET @SQLCommand = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
                          DBCC SHRINKDATABASE([' + @DBName + ']);  
                          ALTER DATABASE [' + @DBName + '] SET MULTI_USER;';
       EXEC (@SQLCommand);  
       FETCH NEXT FROM DB_Cursor INTO @DBName;  
   END;
   

   Для каждой базы данных выполняется:

   • Перевод базы данных в одиночный режим (SINGLE_USER) с немедленным откатом всех активных транзакций. Это необходимо для освобождения всех соединений и предотвращения конфликтов.
   • Уменьшение размера базы данных с помощью команды DBCC SHRINKDATABASE, которая освобождает неиспользуемое пространство.
   • Возвращение базы данных в многопользовательский режим (MULTI_USER), чтобы снова разрешить доступ нескольким пользователям.

    

6. Закрытие и освобождение курсора:

   CLOSE DB_Cursor;  
   DEALLOCATE DB_Cursor;
   

   Курсор закрывается и освобождается, что завершает процесс.

Применение:

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

DECLARE @DBName varchar(255);
DECLARE @SQLCommand nvarchar(500);

-- Создаем курсор для перебора всех баз данных
DECLARE DB_Cursor CURSOR FOR
SELECT name FROM master.sys.databases WHERE state = 0 -- Состояние ONLINE

OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCommand = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
DBCC SHRINKDATABASE([' + @DBName + ']); 
ALTER DATABASE [' + @DBName + '] SET MULTI_USER;';
EXEC (@SQLCommand);
FETCH NEXT FROM DB_Cursor INTO @DBName;
END;

CLOSE DB_Cursor;
DEALLOCATE DB_Cursor;

Этот SQL-скрипт выполняет три основные операции с базой данных в Microsoft SQL Server:

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

ALTER DATABASE dev_zup_pilot SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

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

Переименование базы данных:

ALTER DATABASE dev_zup_pilot MODIFY NAME = zup_pilot;

Изменяет имя базы данных с dev_zup_pilot на zup_pilot. Это может быть полезно для приведения имен баз данных в соответствие с новыми стандартами именования или для отражения изменений в их назначении.

Переключение базы данных обратно в многопользовательский режим:

ALTER DATABASE zup_pilot SET MULTI_USER;

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

Применение:

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

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

-- Перевод базы данных в однопользовательский режим.
ALTER DATABASE dev_zup_pilot SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Переименование базы данных.
ALTER DATABASE dev_zup_pilot MODIFY NAME = zup_pilot;

-- Переключение базы данных обратно в многопользовательский режим.
ALTER DATABASE zup_pilot SET MULTI_USER;

Этот SQL-скрипт выполняет операции по управлению файлами данных и журналов транзакций для всех пользовательских баз данных, которые находятся в состоянии ONLINE, в Microsoft SQL Server. Он исключает системные базы данных (master, tempdb, model, msdb). Вот что делает каждый шаг:

1. Объявление переменных:

   DECLARE @dbname NVARCHAR(128);
   DECLARE @logname NVARCHAR(128);
   DECLARE @datafile NVARCHAR(128);
   DECLARE @sql NVARCHAR(MAX);
   

   CopyInsert

   • Переменные используются для хранения имен баз данных, лог-файлов, файлов данных и SQL-запросов.

2. Объявление курсора:

   Получение имен файлов: Извлекаются имена лог-файла и файла данных.

   • • Очистка журнала транзакций: Выполняется команда BACKUP LOG ... WITH TRUNCATE_ONLY, которая очищает журнал транзакций. (Примечание: эта команда устарела и не рекомендуется для использования в новых версиях SQL Server.)
     • Сжатие лог-файла: Уменьшает размер лог-файла до минимально возможного.
     • Сжатие файла данных: Уменьшает размер файла данных до минимально возможного.
     • Установка максимального размера лог-файла: Ограничивает максимальный размер лог-файла до 100 МБ.

3. Для каждой базы данных выполняются следующие действия:

4.  

5. Закрытие и освобождение курсора:

   CLOSE db_cursor;
   DEALLOCATE db_cursor;
   

   Курсор закрывается и освобождается, завершая процесс.

Применение:

• Когда использовать: Этот скрипт может быть полезен для управления дисковым пространством, особенно если необходимо уменьшить размер файлов данных и журналов транзакций для всех пользовательских баз данных.
• Предостережения: Использование WITH TRUNCATE_ONLY может привести к потере возможности восстановления базы данных до определенного момента времени, так как журнал транзакций будет очищен. Этот скрипт следует использовать с осторожностью и только в тех случаях, когда это действительно необходимо. Убедитесь, что у вас есть актуальные полные резервные копии перед выполнением таких операций.