Sql-server – Moving SQL MDF and LDF file

sql serverssms

Is there a way to move an MDF and LDF file from C drive to D drive? I've tried to run

ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE myDb SET OFFLINE

ALTER DATABASE myDb MODIFY FILE ( Name = myDb, Filename = 'D:\SQL Data Files\myDb.MDF')

ALTER DATABASE myDb SET ONLINE

ALTER DATABASE myDb SET MULTI_USER

but I have to manually move files from C drive to D:\SQL Data Files which is ok but is there a way to move files by SQL query itself, I'm not trying to move any System Databases just the databases that I have on my server.

Best Answer

Is there a way to move files by SQL query itself

Yes. Use BACKUP/RESTORE WITH MOVE instead of OFFLINE/ALTER/File Copy/ONLINE.

eg

use master 
go
use foo
alter database foo set single_user with rollback immediate
backup database foo to disk='c:\temp\foo.bak' with init
--restore filelistonly from disk='c:\temp\foo.bak' 
use master
restore database foo from disk='c:\temp\foo.bak'
  with move 'foo' to 'd:\mssql\data\foo.mdf',
       move 'foo_log' to 'd:\mssql\data\foo_log.ldf'

Note that when you RESTORE over a database SQL Server deletes the old database files.

Related Question