Задача:
Имеется база в Microsoft SQL Server. Необходимо перенести файлы базы на новое место.
Решение:
-
В среде Microsoft SQL Server Management Studio выполняем запрос:
Код: Выделить всё
ALTER DATABASE "Имя базы" SET OFFLINE;
-
Копируем (или переносим) файлы базы на новое место
-
В среде 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
Скрипт самостоятельно СКОПИРУЕТ файлы в указанные новые места. Файлы на старом месте не удаляются намеренно, для исключения потери баз. Их следует удалять самостоятельно, после того как убедитесь, что копирование прошло успешно и базы на новом месте работают нормально.
Скачать скрипт: