欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-03 17:50:22
复制代码 代码如下:use mastergo declare    @dbname sysname,    @...

复制代码 代码如下:

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