Code
Sp_configure ' Show advanced options ' , 1
Reconfigure
Go
Sp_configure ' Xp_mongoshell ' , 1
Reconfigure
Go
Sp_configure ' Show advanced options ' , 0
Reconfigure
Go
UseMaster
Go
declare
@ dbname sysname,
@ destpath varchar ( 256 )
declare @ dB table (
name sysname,
physical_name sysname)
BeginTry
Select
@ Dbname = ' Roboth ' , -- Input Database Name
@ Destpath = ' D: \ fffffffffffffffffffff ' -- Input Destination path
-- kill database processes
declare @ spid varchar ( 20 )
declare curprocess cursor for
SelectSpid
FromSYS. 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
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
Inner Join SYS. Databases B
On A. database_id = B. database_id
And B. Name = @ Dbname
End Try
BeginCatch
SelectError_message ()AsErrormessage
End