How to move SQL database files to another location

Moving database files to another location

The New_location of db first we need to create  a folder created on a separate drive (in this specific case, we will change from a default C to E drive on a  machine) with sufficient disk space for  database files. Specified folder must be created first, in order to be used as a new location set steps..... here we will learn using 

attach& detach db  process  inducing offline/online steps......

---How to Create database

create database AptechMum

on primary

(name='AptechMum', filename='D:\Administrator\Downloads\Video\apt.mdf') 

log on 

(name='AptechMum_Log', filename='D:\Administrator\Downloads\Video\apt.ldf') 

sp_helpdb 'aptechmum'

---move the file another drive

 --- step 1

alter database AptechMum modify file

(name='AptechMum',filename='D:\Aptech_mum\apt.mdf')

go

alter database AptechMum modify file

(name='AptechMum_Log',filename='D:\Aptech_mum\apt.ldf')

--step 2

sp_helpdb 'AptechMum'

--step 3 set database offline mode

alter database AptechMum set offline with  rollback immediate

----now copy the datafile & paste it another file then 

--step 3 set database online mode

alter database AptechMum set online 

--chk databse status 

select DATABASEPROPERTYEX('AptechMum','Status') as Status

---how to add new data file in existing database 

-- before this we will discuss how to create filegroup

alter database AptechMum add filegroup ExamUP

--- add new datafile in exam up flegroup

alter database AptechMum add file

(Name='ExamUp_Lucknow',filename='D:\Aptech_mum\ExamUp_Lucknow.ndf')

to filegroup ExamUP

sp_helpdb 'AptechMum'

or

use AptechMum

sp_helpfile

--------set growith zero or stop auto filegrowth 

alter database AptechMum modify file 

(name='ExamUp_Lucknow',filegrowth=0)

sp_helpfile

-------

--------set  size 

alter database AptechMum modify file 

(name='ExamUp_Lucknow',size=2)

sp_helpfile

alter database AptechMum modify file 

(name='ExamUp_Lucknow',filegrowth=1024 KB)

sp_helpfile

---change the file logical  name 

alter database AptechMum modify file 

(name='ExamUp_Lucknow',newname='ExamUp_Lucknow_Basti')

sp_helpfile

USE master;  

GO  

EXEC sp_detach_db @dbname = 'AptechMum';  

--now rename the file  name  manualy to go tht place  ExamUp_Lucknow to ExamUp_Lucknow_Basti

-- now attach the database

EXEC sp_attach_db @dbname = 'AptechMum';  

exec sp_attach_db @dbname='AptechMum',@filename1='D:\Aptech_mum\apt.mdf'

,@filename2= 'D:\Aptech_mum\ExamUp_Lucknow_Basti.ndf'

,@filename3='D:\Aptech_mum\apt.ldf'

go

use AptechMum

sp_helpfile

select DATABASEPROPERTYEX ('AptechMum','Status')