1. Собираем список файлов
USE master SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N’tempDB’); На выходе получаем список текущих файлов.
На выходе получаем список текущих файлов.
tempdev F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE templog F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE temp2 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE temp3 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE temp4 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE temp5 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp5.ndf ONLINE temp6 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp6.ndf ONLINE temp7 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp7.ndf ONLINE temp8 F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp8.ndf ONLINE
2. Удаляем файлы БД Используя регулярные выражения превращаем выгрузку в
ALTER DATABASE tempdb REMOVE FILE templog; ALTER DATABASE tempdb REMOVE FILE tempdev; ALTER DATABASE tempdb REMOVE FILE temp2; ALTER DATABASE tempdb REMOVE FILE temp3; ALTER DATABASE tempdb REMOVE FILE temp4; ALTER DATABASE tempdb REMOVE FILE temp5; ALTER DATABASE tempdb REMOVE FILE temp6; ALTER DATABASE tempdb REMOVE FILE temp7; ALTER DATABASE tempdb REMOVE FILE temp8;
3. Добавляем новые файлы tempDB
ALTER DATABASE tempdb ADD FILE (NAME = tempdev, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = templog, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp2, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp3, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp4, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp5, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp5.ndf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp6, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp6.ndf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp7, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp7.ndf', SIZE = 128, FILEGROWTH = 128); GO ALTER DATABASE tempdb ADD FILE (NAME = temp8, FILENAME = 'T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\temp8.ndf', SIZE = 128, FILEGROWTH = 128); GO
Заменяем где T:\MSSQL14.MSSQLSERVER\MSSQL\DATA\ — нужная буква диска и путь
Просмотров: 1 697