Как в Mssql сделать новый файл файловой группы на новом диске H .

автор: автор автор
7 февраля, 2025

Задача в mssql добавить новый файл файловой группы на базах на новый диск H

Проверяем какие базы не имеют файлов на диске

SELECT
    DB_NAME(database_id) AS DatabaseName
FROM
    sys.master_files
WHERE
    database_id > 4 -- исключаем системные базы
GROUP BY
    database_id
HAVING
    SUM(CASE WHEN LEFT(physical_name, 1) = 'H' THEN 1 ELSE 0 END) = 0;

Создать команды для создания файла по всем базам.

-- Объявление переменных
DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);

-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE tempdb..#DatabaseList;

-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);

CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
    FreeSpaceMB DECIMAL(18, 2)
);

CREATE TABLE #DatabaseList (
    DatabaseName NVARCHAR(128)
);

-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Выполняем запрос для текущей базы
    SET @Sql = 'USE [' + @DbName + ']; ' +
               'INSERT INTO #DatabaseFG (DatabaseName, FG_name, data_space_id, type, is_default, is_autogrow_all_files) ' +
               'SELECT ''' + @DbName + ''' as DatabaseName, name as FG_name, data_space_id, type, is_default, is_autogrow_all_files FROM sys.filegroups; ' +
               'INSERT INTO #SysFiles (DatabaseName, FileName, FileSizeMB, SpaceUsedMB, FreeSpaceMB) ' +
               'SELECT ''' + @DbName + ''' AS DatabaseName, a.name AS FileName, ' +
               'CONVERT(DECIMAL(18, 2), ROUND(a.size / 128.000, 2)) AS FileSizeMB, ' +
               'CONVERT(DECIMAL(18, 2), ROUND(FILEPROPERTY(a.name, ''SpaceUsed'') / 128.000, 2)) AS SpaceUsedMB, ' +
               'CONVERT(DECIMAL(18, 2), ROUND((a.size - FILEPROPERTY(a.name, ''SpaceUsed'')) / 128.000, 2)) AS FreeSpaceMB ' +
               'FROM dbo.sysfiles a;';
    EXEC sp_executesql @Sql;

    FETCH NEXT FROM db_cursor INTO @DbName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Фильтрация баз без файлов на диске H:
WITH DatabaseWithHDriveFiles AS (
    SELECT DISTINCT DB_NAME(mf.database_id) AS DatabaseName
    FROM sys.master_files mf
    WHERE mf.physical_name LIKE 'H:%' -- Базы с файлами на диске H:
)
INSERT INTO #DatabaseList (DatabaseName)
SELECT DISTINCT DBF.DatabaseName
FROM #DatabaseFG AS DBF
LEFT JOIN DatabaseWithHDriveFiles DWH
    ON DBF.DatabaseName = DWH.DatabaseName
WHERE DWH.DatabaseName IS NULL -- Включаем только базы, у которых нет файлов на диске H:
  AND DBF.is_default = 1 -- Только группы файлов, являющиеся основными по умолчанию
  --AND DBF.FG_name = 'PRIMARY'; -- Только основные группы файлов

-- Переменная для накопления итогового скрипта
DECLARE @FinalScript NVARCHAR(MAX) = '';

-- Курсор для генерации скриптов файловых групп
DECLARE db_list_cursor CURSOR FOR
SELECT DISTINCT DatabaseName FROM #DatabaseList;

OPEN db_list_cursor;
FETCH NEXT FROM db_list_cursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql =
        'USE [master];' + CHAR(13) + CHAR(10) +
        'GO' + CHAR(13) + CHAR(10) +
        'ALTER DATABASE [' + @DbName + '] ADD FILE ( NAME = N''' + @DbName + '_DF5_FG_Current'', FILENAME = N''H:\MSSQL\Data\' + @DbName + '\' + @DbName + '_DF5_FG_Current.ndf'' , SIZE = 102400KB , FILEGROWTH = 51200KB ) TO FILEGROUP [' + @DbName + '_FG_Current];' + CHAR(13) + CHAR(10) +
        'GO' + CHAR(13) + CHAR(10);

    SET @FinalScript = @FinalScript + @Sql;
    PRINT @Sql;

    FETCH NEXT FROM db_list_cursor INTO @DbName;
END;

CLOSE db_list_cursor;
DEALLOCATE db_list_cursor;

-- Вывод итогового скрипта
 PRINT @FinalScript;
--SELECT @FinalScript AS FinalScript;

-- Удаление временных таблиц
DROP TABLE #DatabaseFG;
DROP TABLE #SysFiles;
DROP TABLE #DatabaseList;

Как в Mssql сделать новый файл файловой группы на новом диске H .

Similar Posts:

Добавить комментарий Отменить ответ