sqlserver数据库移动数据库路径的脚本示例
时间:2024-01-28 05:06:49
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\' --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
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
MySql模糊查询json关键字检索方案示例
![](https://img.aspxhome.com/file/2023/8/111418_0s.png)
python3.7+selenium模拟淘宝登录功能的实现
![](https://img.aspxhome.com/file/2023/9/70169_0s.png)
python executemany的使用及注意事项
CentOS中升级Python版本的方法详解
史上最全Python文件类型读写库大盘点
asp正则表达式详细说明
Python嵌套循环的使用
![](https://img.aspxhome.com/file/2023/5/101095_0s.png)
Sanic框架安装与简单入门示例
如何设置SQL Server数据库全文索引服务
python线程池ThreadPoolExecutor,传单个参数和多个参数方式
Python 异步之生成器示例详解
python做接口测试的必要性
Python subprocess模块学习总结
python的继承知识点总结
![](https://img.aspxhome.com/file/2023/0/65080_0s.jpg)
使用Matplotlib 绘制精美的数学图形例子
![](https://img.aspxhome.com/file/2023/2/68392_0s.jpg)
详解Python prometheus_client使用方式
![](https://img.aspxhome.com/file/2023/6/115636_0s.jpg)
Python使用cx_Oracle调用Oracle存储过程的方法示例
防止web项目中的SQL注入
python 字典修改键(key)的几种方法
![](https://img.aspxhome.com/file/2023/1/107601_0s.png)
关于python3安装pip及requests库的导入问题
![](https://img.aspxhome.com/file/2023/5/115015_0s.png)