MS SQL Server. Перенос файлов базы на новое место.

Все об администрировании Windows NT/2000/2003/2008/2012 Server (в т.ч. R2) серверов. Групповые политики, домены, безопасность и пр. То, чего не найдешь в бескрайних просторах Интернета. Решения тех проблем, которые не решаются типовыми ответами, которые можно получить в техподдержке Майкрософта - а именно: переустановить продукт или купить какой-ть другой лицензионный диск.


Модератор: UncleFather

Аватара пользователя
UncleFather
Site Admin
Сообщения: 1503
Зарегистрирован: 17 авг 2004 16:20, Вт
Контактная информация:

MS SQL Server. Перенос файлов базы на новое место.

Сообщение UncleFather »

Задача:

Имеется база в Microsoft SQL Server. Необходимо перенести файлы базы на новое место.


Решение:

  1. В среде Microsoft SQL Server Management Studio выполняем запрос:

    Код: Выделить всё

    ALTER DATABASE "Имя базы" SET OFFLINE;
  2. Копируем (или переносим) файлы базы на новое место

  3. В среде Microsoft SQL Server Management Studio выполняем запрос:

    Код: Выделить всё

    ALTER DATABASE "Имя базы" MODIFY FILE (NAME = "Имя базы", FILENAME = 'Новый путь\Имя базы.mdf');
    ALTER DATABASE "Имя базы" MODIFY FILE (NAME = "Имя базы_log", FILENAME = 'Новый путь\Имя базы_log.LDF');
    ALTER DATABASE "Имя базы" SET ONLINE;

Решение для всех пользовательских баз на MS SQL Server:

Для того, чтобы не возникло конфликтов файлов с одинаковыми именами, скрипт выполняет предварительную настройку файлов баз данных:

  • Изменяет логические имена файлов БД в соответствии с именем базы;

  • Изменяет указанное в системной базе данных расположение файлов обрабатываемой базы данных;

  • Копирует файлы обрабатываемой базы данных на новое место, в соответствии со сделанной в предыдущем пункте списка записью в системной БД.

В скрипте прописываем новые пути для файлов баз @newpathrows и файлов журналов @newpathlogs и выполняем скрипт:

Код скрипта

Код: Выделить всё

USE MASTER
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE WITH OVERRIDE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
 
DECLARE
	@isql VARCHAR(2000),
	@dbname VARCHAR(64),
	@logfile VARCHAR(400),
	@rowsorlog VARCHAR(5),
	@filetype VARCHAR(5)
 
DECLARE c1 CURSOR SCROLL FOR
SELECT  d.name, mf.name, mf.type_desc AS logfile
FROM sys.master_files mf
	INNER JOIN sys.databases d
	ON mf.database_id = d.database_id
WHERE d.name NOT IN ('master','model','msdb','tempdb') 
	AND (mf.type_desc = 'LOG' OR mf.type_desc = 'ROWS')
OPEN c1
FETCH NEXT FROM c1 INTO @dbname, @logfile, @filetype
WHILE @@fetch_status <> -1
BEGIN
	SET @rowsorlog = '_log';
	IF @filetype = 'ROWS'
		SET @rowsorlog = '';
	IF @logfile <> @dbname + @rowsorlog
	BEGIN
		SELECT @isql = 'ALTER DATABASE "' + @dbname + '" MODIFY FILE (NAME = "' + @logfile + '", NEWNAME = "' + @dbname + @rowsorlog + '");';
		PRINT @isql;
		EXEC(@isql);
	END;
	FETCH NEXT FROM c1 INTO @dbname, @logfile, @filetype
END;

FETCH FIRST FROM c1 INTO @dbname, @logfile, @filetype
WHILE @@fetch_status <> -1
BEGIN
	IF @filetype = 'ROWS'
		EXEC('ALTER DATABASE "' + @dbname + '" SET OFFLINE WITH ROLLBACK IMMEDIATE');
	FETCH NEXT FROM c1 INTO @dbname, @logfile, @filetype
END;
CLOSE c1
DEALLOCATE c1
GO
 
DECLARE
	@isql VARCHAR(2000),
	@dbname VARCHAR(64),
	@logfile VARCHAR(400),
	@physname VARCHAR(255),
	@newpathrows VARCHAR(255),
	@newpathlogs VARCHAR(255),
	@newpath VARCHAR(255),
	@fileext VARCHAR(5),
	@filetype VARCHAR(5)
 
SET @newpathrows = N'E:\SQLBase\';
SET @newpathlogs = N'F:\!TmpSQLLogs\';
 
DECLARE c1 CURSOR SCROLL FOR
SELECT  d.name, mf.name, mf.physical_name, mf.type_desc AS logfile
FROM sys.master_files mf
	INNER JOIN sys.databases d
	ON mf.database_id = d.database_id
WHERE d.name NOT IN ('master','model','msdb','tempdb') 
	AND (mf.type_desc = 'LOG' OR mf.type_desc = 'ROWS')
OPEN c1
FETCH NEXT FROM c1 INTO @dbname, @logfile, @physname, @filetype
While @@fetch_status <> -1
BEGIN
	SET @newpath = @newpathlogs;
	SET @fileext = '.ldf';
	IF @filetype = 'ROWS'
	BEGIN
		SET @newpath = @newpathrows;
		SET @fileext = '.mdf';
	END;
	IF @physname <> @newpath + @logfile + @fileext
	BEGIN
		SELECT @isql = 'ALTER DATABASE "' + @dbname + '" MODIFY FILE (NAME = "' + @logfile + '", FILENAME = "' + @newpath + @logfile + @fileext + '");';
		PRINT @isql;
		EXEC(@isql);
		SELECT @isql = 'ECHO F|xcopy "' + @physname + '" "' + @newpath + @logfile + @fileext + '" /Y';
		PRINT @isql;
		EXEC xp_cmdshell @isql;
	END;
FETCH NEXT FROM c1 INTO @dbname, @logfile, @physname, @filetype
END;
 
FETCH FIRST FROM c1 INTO @dbname, @logfile, @physname, @filetype
WHILE @@fetch_status <> -1
BEGIN
	IF @filetype = 'ROWS'
		EXEC('ALTER DATABASE "' + @dbname + '" SET ONLINE');
	FETCH NEXT FROM c1 INTO @dbname, @logfile, @physname, @filetype
END;
CLOSE c1
DEALLOCATE c1

Скрипт самостоятельно СКОПИРУЕТ файлы в указанные новые места. Файлы на старом месте не удаляются намеренно, для исключения потери баз. Их следует удалять самостоятельно, после того как убедитесь, что копирование прошло успешно и базы на новом месте работают нормально.

Скачать скрипт:

MovingBaseFiles.rar
(2 КБ) 4 скачивания

Alexander A. Manaeff©

Понравилась статья? Будем крайне признательны за репосты в соцсетях! Материально поддержать проект можно здесь

Мои странички:
ВКонтакте
Одноклассники
Youtube
Facebook
Instagram

Изображение
Изображение
Изображение
Изображение