среда, 19 октября 2022 г.

Перенос БД tempdab на новое место



-- Новый каталог для базы tempdb
DECLARE @Path as NVARCHAR(400) = 'E:\SQL_Temp'
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @SQLString NVARCHAR(400)

USE master;

DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR
(
SELECT
name,
physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
)
OPEN fnc;
FETCH fnc INTO @logicalName, @physicalName;
WHILE @@FETCH_STATUS=0

BEGIN
SET @SQLString = '
ALTER DATABASE tempdb
MODIFY FILE (NAME = ''' + @logicalName
+ ''', FILENAME = ''' + @Path + '\'
+ REVERSE(SUBSTRING(REVERSE(@physicalName), 1, CHARINDEX('\', REVERSE(@physicalName))-1))
+ ''');'
PRINT @SQLString
EXEC sp_executesql @SQLString

FETCH fnc INTO @logicalName, @physicalName;

END;

CLOSE fnc;

DEALLOCATE fnc;