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

来源：互联网发布：淘宝买家签收规则编辑：程序博客网时间：2024/06/05 05:02
----------------------------------------------------------------------------------------------------  发布库TestDB（停止日志读取器代理、队列读取器代理，禁止用户访问）--------------------------------------------------------------------------------------------------USE masterGOSELECT file_id,name,type_desc,physical_name,state_desc FROM sys.master_files WHERE database_id = DB_ID('TestDB'); GOALTER DATABASE TestDB SET SINGLE_USER --WITH ROLLBACK IMMEDIATE  GOALTER DATABASE TestDB MODIFY FILE ( NAME = TestDB, NEWNAME= 'NewFileName', FILENAME = "E:\DatabaseFile\TestDB\NewFileName.MDF")ALTER DATABASE TestDB MODIFY FILE ( NAME = TestDB_log, NEWNAME= 'NewFileName_log', FILENAME = "E:\DatabaseFile\TestDB\NewFileName_log.LDF")GOALTER DATABASE TestDB SET OFFLINEGOEXEC sp_configure 'show advanced options',1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell',1RECONFIGUREGOEXEC master..xp_cmdshell 'MOVE "E:\DatabaseFile\TestDB\TestDB.MDF" E:\DatabaseFile\TestDB\'EXEC master..xp_cmdshell 'MOVE "E:\DatabaseFile\TestDB\TestDB_log.LDF" E:\DatabaseFile\TestDB\'GOEXEC master..xp_cmdshell 'ren "E:\DatabaseFile\TestDB\TestDB.MDF" "NewFileName.mdf"'EXEC master..xp_cmdshell 'ren "E:\DatabaseFile\TestDB\TestDB_log.LDF" "NewFileName_log.ldf"'GOEXEC sp_configure 'xp_cmdshell',0RECONFIGUREGOEXEC sp_configure 'show advanced options',0RECONFIGUREGOALTER DATABASE TestDB SET ONLINEGOALTER DATABASE TestDB SET MULTI_USERGO----------------------------------------------------------------------------------------------------  订阅库TestDBSub （停止分发代理、队列读取器代理，禁止用户访问）--------------------------------------------------------------------------------------------------USE masterGOSELECT file_id,name,type_desc,physical_name,state_desc FROM sys.master_files WHERE database_id = DB_ID('TestDBSub'); GOALTER DATABASE TestDBSub SET SINGLE_USER --WITH ROLLBACK IMMEDIATE  GOALTER DATABASE TestDBSub MODIFY FILE ( NAME = TestDBSub, NEWNAME= 'NewFileName', FILENAME = "E:\DatabaseFile\TestDBSub\NewFileName.mdf")ALTER DATABASE TestDBSub MODIFY FILE ( NAME = TestDBSub_log, NEWNAME= 'NewFileName_log', FILENAME = "E:\DatabaseFile\TestDBSub\NewFileName_log.ldf")GOALTER DATABASE TestDBSub SET OFFLINEGOEXEC sp_configure 'show advanced options',1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell',1RECONFIGUREGOEXEC master..xp_cmdshell 'MOVE "E:\DatabaseFile\TestDBSub\TestDBSub.mdf" E:\DatabaseFile\TestDBSub\'EXEC master..xp_cmdshell 'MOVE "E:\DatabaseFile\TestDBSub\TestDBSub_log.ldf" E:\DatabaseFile\TestDBSub\'GOEXEC master..xp_cmdshell 'ren "E:\DatabaseFile\TestDBSub\TestDBSub.mdf" "NewFileName.mdf"'EXEC master..xp_cmdshell 'ren "E:\DatabaseFile\TestDBSub\TestDBSub_log.ldf" "NewFileName_log.ldf"'GOEXEC sp_configure 'xp_cmdshell',0RECONFIGUREGOEXEC sp_configure 'show advanced options',0RECONFIGUREGOALTER DATABASE TestDBSub SET ONLINE   GOALTER DATABASE TestDBSub SET MULTI_USERGO----------------------------------------------------------------------------------------------------  分发库distribution（停止分发代理、日志读取器代理、队列读取器代理，禁止用户访问）--------------------------------------------------------------------------------------------------USE masterGOSELECT file_id,name,type_desc,physical_name,state_desc FROM sys.master_files WHERE database_id = DB_ID('distribution');   GOALTER DATABASE distribution SET SINGLE_USER --WITH ROLLBACK IMMEDIATE  GOALTER DATABASE distribution MODIFY FILE ( NAME = distribution, NEWNAME= 'NewFileName', FILENAME = "E:\DatabaseFile\NewFileName.MDF")ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log, NEWNAME= 'NewFileName_log', FILENAME = "E:\DatabaseFile\NewFileName_log.LDF")GOALTER DATABASE distribution SET OFFLINEGOEXEC sp_configure 'show advanced options',1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell',1RECONFIGUREGOEXEC master..xp_cmdshell 'MOVE "E:\DatabaseFile\distribution.MDF" E:\DatabaseFile\'EXEC master..xp_cmdshell 'MOVE "E:\DatabaseFile\distribution.LDF" E:\DatabaseFile\'GOEXEC master..xp_cmdshell 'ren "E:\DatabaseFile\distribution.MDF" "NewFileName.mdf"'EXEC master..xp_cmdshell 'ren "E:\DatabaseFile\distribution.LDF" "NewFileName_log.ldf"'GOEXEC sp_configure 'xp_cmdshell',0RECONFIGUREGOEXEC sp_configure 'show advanced options',0RECONFIGUREGOALTER DATABASE distribution SET ONLINEGOALTER DATABASE distribution SET MULTI_USERGO--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
几个数据库的更改脚本都一样！~
0 0