0

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.

4
  • 2
    AFAIK, this always has to be done manually. – Peter B Mar 6 at 14:27
  • In this case, @PeterB I want to mode MDF files on the client-side they don't have any technical knowledge or what so ever. – Ahmed Mar 6 at 14:30
  • 2
    No, you basically have to move the files yourself, it's a one-time operation. You could detatch and reatach the database specifying new locations. As an aside, data and log files really should never ever ever... share the same drive and definitely never ever reside on C or whatever the OS instllation is on! – sTTu Mar 6 at 14:42
  • you might be able to use xp_cmdshell to run commands but it is disabled by default....You could of course write code inside your application to do this.... – Mitch Wheat Mar 6 at 14:42
2

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.

4
  • second part where you restore the database is throwing an error Msg 3159, Level 16, State 1, Line 8 The tail of the log for the database "foo" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. Msg 3013, Level 16, State 1, Line 8 RESTORE DATABASE is terminating abnormally. – Ahmed Mar 6 at 18:00
  • Downside is you need enough space for the whole DB as opposed to enough space for the largest single file. It is rather stupid that there is no built in method for this. – Charlieface Mar 6 at 21:37
  • @Ahmed you will get that warning if you are restoring from a backup of a different database. – David Browne - Microsoft Mar 6 at 21:49
  • @david bronwe im running the script as it is, withiut any changes, but as you advised ive created backup of each database and while restoring the data base I've changed paths for the mdf ldf files. – Ahmed Mar 7 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.