SQLserver移动数据库文件

? ?在移动之前会报错：

SQL Server阻止了对组件xp_cmdshell过程的解决方案
错误描述：SQL Server阻止了对组件‘xp_cmdshell’的过程‘sys.xp_cmdshell’的访问。因为此组件已作为此服务嚣安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure启用‘xp_cmdshell’。有关启用‘xp_cmdshell’的详细信息，请参阅sQL帮助文件。

可以在Master下执行

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

USE master

exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure 'xp_cmdshell',1
reconfigure
go
GO
DECLARE
    @DBName sysname,@DestPath varchar(256)
DECLARE @DB table(
    name sysname,physical_name sysname)

BEGIN TRY
SELECT
    @DBName = 'dbname',--input database name
    @DestPath = 'e:db'         --input destination path

-- kill database processes
DECLARE @SPID varchar(20)
DECLARE curProcess CURSOR FOR
SELECT spid
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = @DBName
OPEN curProcess
    FETCH NEXT FROM curProcess INTO @SPID
    WHILE @@FETCH_STATUS = 0
    BEGIN
            EXEC('KILL ' + @SPID)
            FETCH NEXT FROM curProcess
    END
CLOSE curProcess
DEALLOCATE curProcess
-- query physical name
INSERT @DB(
    name,physical_name)
SELECT
    A.name,A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
    ON A.database_id = B.database_id
        AND B.name = @DBName
WHERE A.type <=1
--set offline
EXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE')
--move to dest path
DECLARE
    @login_name sysname,@physical_name sysname,@temp_name varchar(256)
DECLARE curMove CURSOR FOR
SELECT
    name,physical_name
FROM @DB
OPEN 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
        END
CLOSE curMove
DEALLOCATE curMove
-- set online
EXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE')
-- show result
SELECT
    A.name,A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
    ON A.database_id = B.database_id
        AND B.name = @DBName
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

（编辑：李大同）

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