SQLServer 本地移动发布/订阅/分发数据库文件并更改逻辑名称和物

--------------------------------------------------------------------------------------------------
--  发布库TestDB（停止日志读取器代理、队列读取器代理，禁止用户访问）
--------------------------------------------------------------------------------------------------
USE master
GO
SELECT file_id,name,type_desc,physical_name,state_desc 
FROM sys.master_files WHERE database_id = DB_ID('TestDB'); 
GO
ALTER DATABASE TestDB SET SINGLE_USER --WITH ROLLBACK IMMEDIATE  
GO
ALTER DATABASE TestDB MODIFY FILE ( NAME = TestDB,NEWNAME= 'NewFileName',FILENAME = "E:DatabaseFileTestDBNewFileName.MDF")
ALTER DATABASE TestDB MODIFY FILE ( NAME = TestDB_log,NEWNAME= 'NewFileName_log',FILENAME = "E:DatabaseFileTestDBNewFileName_log.LDF")
GO
ALTER DATABASE TestDB SET OFFLINE
GO
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE
GO
EXEC master..xp_cmdshell 'MOVE "E:DatabaseFileTestDBTestDB.MDF" E:DatabaseFileTestDB'
EXEC master..xp_cmdshell 'MOVE "E:DatabaseFileTestDBTestDB_log.LDF" E:DatabaseFileTestDB'
GO
EXEC master..xp_cmdshell 'ren "E:DatabaseFileTestDBTestDB.MDF" "NewFileName.mdf"'
EXEC master..xp_cmdshell 'ren "E:DatabaseFileTestDBTestDB_log.LDF" "NewFileName_log.ldf"'
GO
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE
GO
EXEC sp_configure 'show advanced options',0
RECONFIGURE
GO
ALTER DATABASE TestDB SET ONLINE
GO
ALTER DATABASE TestDB SET MULTI_USER
GO

--------------------------------------------------------------------------------------------------
--  订阅库TestDBSub （停止分发代理、队列读取器代理，禁止用户访问）
--------------------------------------------------------------------------------------------------
USE master
GO
SELECT file_id,state_desc 
FROM sys.master_files WHERE database_id = DB_ID('TestDBSub'); 
GO
ALTER DATABASE TestDBSub SET SINGLE_USER --WITH ROLLBACK IMMEDIATE  
GO
ALTER DATABASE TestDBSub MODIFY FILE ( NAME = TestDBSub,FILENAME = "E:DatabaseFileTestDBSubNewFileName.mdf")
ALTER DATABASE TestDBSub MODIFY FILE ( NAME = TestDBSub_log,FILENAME = "E:DatabaseFileTestDBSubNewFileName_log.ldf")
GO
ALTER DATABASE TestDBSub SET OFFLINE
GO
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC master..xp_cmdshell 'MOVE "E:DatabaseFileTestDBSubTestDBSub.mdf" E:DatabaseFileTestDBSub'
EXEC master..xp_cmdshell 'MOVE "E:DatabaseFileTestDBSubTestDBSub_log.ldf" E:DatabaseFileTestDBSub'
GO
EXEC master..xp_cmdshell 'ren "E:DatabaseFileTestDBSubTestDBSub.mdf" "NewFileName.mdf"'
EXEC master..xp_cmdshell 'ren "E:DatabaseFileTestDBSubTestDBSub_log.ldf" "NewFileName_log.ldf"'
GO
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE
GO
ALTER DATABASE TestDBSub SET ONLINE   
GO
ALTER DATABASE TestDBSub SET MULTI_USER
GO

--------------------------------------------------------------------------------------------------
--  分发库distribution（停止分发代理、日志读取器代理、队列读取器代理，禁止用户访问）
--------------------------------------------------------------------------------------------------
USE master
GO
SELECT file_id,state_desc 
FROM sys.master_files WHERE database_id = DB_ID('distribution');   
GO
ALTER DATABASE distribution SET SINGLE_USER --WITH ROLLBACK IMMEDIATE  
GO
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution,FILENAME = "E:DatabaseFileNewFileName.MDF")
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log,FILENAME = "E:DatabaseFileNewFileName_log.LDF")
GO
ALTER DATABASE distribution SET OFFLINE
GO
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC master..xp_cmdshell 'MOVE "E:DatabaseFiledistribution.MDF" E:DatabaseFile'
EXEC master..xp_cmdshell 'MOVE "E:DatabaseFiledistribution.LDF" E:DatabaseFile'
GO
EXEC master..xp_cmdshell 'ren "E:DatabaseFiledistribution.MDF" "NewFileName.mdf"'
EXEC master..xp_cmdshell 'ren "E:DatabaseFiledistribution.LDF" "NewFileName_log.ldf"'
GO
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE
GO
ALTER DATABASE distribution SET ONLINE
GO
ALTER DATABASE distribution SET MULTI_USER
GO

-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!