SQL Server 可更新订阅的发布和订阅的数据库文件迁移

--	SQL Server 可更新订阅的发布和订阅的数据库迁移

/*=======================================================================================================

*******************************************【发布服务器数据文件迁移】************************************

========================================================================================================*/

USE master 
GO

/*【1. 查看文件路径】*/
SELECT file_id,name,type_desc,physical_name,state_desc FROM sys.master_files WHERE database_id = DB_ID('dbName'); 

/*【2. 手动停止[sqlserver代理]，不要停止sqlserver服务！】*/

/*【3. 打开[复制监视器]查看复制相关代理是否停止】*/

/*【4. 设置数据库为单用户模式】*/
ALTER DATABASE dbName SET SINGLE_USER --WITH ROLLBACK IMMEDIATE
GO

/*【5. 数据库脱机】*/
ALTER DATABASE dbName SET OFFLINE 
GO

/*【6. 更改数据库逻辑文件名对应的物理文件名位置！】*/
ALTER DATABASE dbName MODIFY FILE ( NAME = dbName, FILENAME = "E:\DataBase\dbName\dbName.mdf")		--new path
ALTER DATABASE dbName MODIFY FILE ( NAME = dbName_log, FILENAME = "E:\DataBase\dbName\dbName_log.ldf")	--new path


/*【7.移动mdf和ldf文件到新的位置】*/
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

EXEC master..xp_cmdshell 'MOVE D:\DataBase\dbName\dbName.mdf  E:\DataBase\dbName\'		--move to a new path
EXEC master..xp_cmdshell 'MOVE D:\DataBase\dbName\dbName_log.ldf  E:\DataBase\dbName\'	--move to a new path
GO
/*
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
*/


<pre code_snippet_id="463183" snippet_file_name="blog_20140903_1_2705579" name="code" class="sql">/*【8. 数据库联机】*/
ALTER DATABASE dbName SET ONLINE 
GO

/*【9. 设置数据库为多用户模式】*/
ALTER DATABASE dbName SET MULTI_USERGO--注意:先不要启动发布数据库上的代理！等待订阅数据库迁移完成后在启动发布服务器的代理！!!

/*【10. 手动开启[sqlserver代理]】*/

/*【11 完成！打开[复制监视器]查看复制相关代理是否开启，网页上测试】*/
/*=======================================================================================================
**********************************************【订阅数据文件迁移】*****************************************
========================================================================================================*/
/*手动停止[sqlserver代理]，不要停止sqlserver服务！*/
SELECT file_id,name,type_desc,physical_name,state_desc FROM sys.master_files WHERE database_id = DB_ID('subDB'); 

ALTER DATABASE subDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE subDB SET OFFLINE 
GO
ALTER DATABASE subDB MODIFY FILE ( NAME = subDB, FILENAME = "E:\DataBase\subDB\subDB.mdf") 
ALTER DATABASE subDB MODIFY FILE ( NAME = subDB_log, FILENAME = "E:\Database\subDB\subDB_log.ldf") 
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC master..xp_cmdshell 'MOVE D:\DataBase\subDB\subDB.mdf E:\DataBase\subDB\'
EXEC master..xp_cmdshell 'MOVE D:\DataBase\subDB\subDB_log.ldf E:\DataBase\subDB\'
/*
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
*/

ALTER DATABASE subDB SET MULTI_USER
GO
ALTER DATABASE subDB SET ONLINE 
GO

-- 启动 [sqlserver代理]

DECLARE @Database NVARCHAR(100)
DECLARE @PhysicalPath NVARCHAR(100)

SET @Database = N'mydb'
SET @PhysicalPath = N'E:\DataBase\'

SET @PhysicalPath = CASE WHEN RIGHT(@PhysicalPath,1)<>'\' THEN @PhysicalPath+'\' ELSE @PhysicalPath END

SELECT 'ALTER DATABASE Platform MODIFY FILE ( NAME = "'+name+'", FILENAME = "'+@PhysicalPath
	+REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))-1))+'")'
FROM sys.master_files WHERE database_id = DB_ID(@Database); 

SELECT 'EXEC master..xp_cmdshell ''MOVE "'+physical_name+'" '+@PhysicalPath+''''
FROM sys.master_files WHERE database_id = DB_ID(@Database);