Moving database files using Offline and Online method

Moving database files using Offline and Online method

Create new database sales
Create database sales

Check current path
sp_helpdb sales
ex:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sales.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sales_log.ldf


Create folder and grant read/write permissions to service account
ex. C:\qadartest1

Modify the file path
use master
go

alter database Sales modify file
(name='Sales',filename='C:\qadartest1\Sales.mdf')
go
alter database Sales modify file
(name='Sales_log',filename='C:\qadartest1\Sales_log.ldf')
go

Take database offline
use master
go

alter database Sales set offline with rollback immediate

Move the files into new folder(s)
Bring online
alter database Sales set online

Check new path
sp_helpdb sales