Scripts for moving SQL Server databases

I have done such a thing some time ago, and moved all the databases (except SQL server system files) originally on drive C to drive D, it is mainly used to facilitate subsequent management and reduce disk I/O blocking (C and D are two independent disks ).

The script is very simple. It is recorded here to avoid future forgetting and reuse convenience:

This script only requires two parameters, namely:Target Database NameAndTarget directory

 

 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  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 CurmoveCursor   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  Begin  Catch  Select Error_message () As  Errormessage  End  Catch  Go