.gif)
For Moving User Database Files to the New Location On the Same Server in MS SQL Server You Can use Database backup and restore which is the best method . But, when you need to move databases to the new location on the same server, backup and restore creates too much overhead. The below Steps will do the same job with as little downtime as possible.
-- Step1 the
below query will make database offline and break all active connections
alter database YOURDATABASENAME
set offline
with rollback immediate
go
-- Step 2 the
below query will detach database
use master
go
sp_detach_db 'YOURDATABASENAME'
go
-- Step 3 the
below query will Show the current data
and log file locations
use
YOURDATABASENAME
go
sp_helpfile
go
-- Step 4 copy
data and log files to the new location
-- Step 5 the
below query will reattach database Confirm you specify correct paths to .mdf and .ldf files)
use master
go
sp_attach_db 'YOURDATABASENAME','D:\SqlData\YOURDATABASENAME.mdf','D:\SqlLogs\
YOURDATABASENAME_log.ldf'
YOURDATABASENAME_log.ldf'
go
-- Step 6 verify
that the database is using the new files location
use
YOURDATABASENAME
go
sp_helpfile
