A mobile database physical file with SQL gadgets

Source: Internet
Author: User
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.