SQLserver移动数据库文件

74 阅读 0 评论 49 点赞

我是靠谱客的博主孝顺纸飞机，这篇文章主要介绍SQLserver移动数据库文件，现在分享给大家，希望可以做个参考。

在移动之前会报错：

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

可以在Master下执行

复制代码

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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