sqlserver数据库移动数据库路径的脚本示例

2023年 4月 18日 35.3k 0

复制代码 代码如下: USE master GO DECLARE @DBName sysname, @DestPath varchar(256) DECLARE @DB table( name sysname, physical_name sysname) BEGIN TRY SELECT @DBName = 'TargetDatabaseName', --input database name @DestPath = 'D:\SqlData\

复制代码 代码如下:USE masterGO

DECLARE    @DBName sysname,    @DestPath varchar(256)DECLARE @DB table(    name sysname,    physical_name sysname)

BEGIN TRY

SELECT    @DBName = 'TargetDatabaseName',   --input database name    @DestPath = 'D:\SqlData\'         --input destination path

-- kill database processesDECLARE @SPID varchar(20)DECLARE curProcess CURSOR FOR

SELECT spidFROM sys.sysprocessesWHERE DB_NAME(dbid) = @DBName

OPEN curProcess    FETCH NEXT FROM curProcess INTO @SPID    WHILE @@FETCH_STATUS = 0    BEGIN            EXEC('KILL ' + @SPID)            FETCH NEXT FROM curProcess    ENDCLOSE curProcessDEALLOCATE curProcess

-- query physical nameINSERT @DB(    name,    physical_name)SELECT    A.name,    A.physical_nameFROM sys.master_files AINNER JOIN sys.databases B    ON A.database_id = B.database_id        AND B.name = @DBNameWHERE A.type <=1

--set offlineEXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE')

--move to dest pathDECLARE    @login_name sysname,    @physical_name sysname,    @temp_name varchar(256)DECLARE curMove CURSOR FORSELECT    name,    physical_nameFROM @DBOPEN curMove    FETCH NEXT FROM curMove INTO @login_name,@physical_name        WHILE @@FETCH_STATUS = 0        BEGIN            SET @temp_name = RIGHT(@physical_name,CHARINDEX('\',REVERSE(@physical_name)) - 1)            EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath + '"''')            EXEC('ALTER DATABASE ' + @DBName + ' MODIFY FILE ( NAME = ' + @login_name                    + ', FILENAME = ''' + @DestPath + @temp_name + ''')')            FETCH NEXT FROM curMove INTO @login_name,@physical_name        ENDCLOSE curMoveDEALLOCATE curMove

-- set onlineEXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE')

-- show resultSELECT    A.name,    A.physical_nameFROM sys.master_files AINNER JOIN sys.databases B    ON A.database_id = B.database_id        AND B.name = @DBNameEND TRYBEGIN CATCH    SELECT ERROR_MESSAGE() AS ErrorMessageEND CATCHGO

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论