0

I have a database on SQL Server and I want to keep it's mdf and ldf files in two different path. For doing this, I detacheh my database and copied the ldf file in the other drive, and then I attached the mdf file again. and then I use this code:

ALTER DATABASE mytbl
MODIFY FILE (NAME=mytbl, FILENAME= 'D:\mytbl.ldf');
GO

But it threw an error and couldn't change the ldf directory. What should I do for changing only ldf location?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Afsan Rezaei
  • 21
  • 7
  • *"But it threw an error"* What was said error? – Thom A Sep 18 '19 at 09:40
  • 1
    Have you had a look a the documention [Move User Databases - Planned Relocation Procedure](https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017#planned-relocation-procedure)? You shouldn't be detaching the database, you should be setting it to `OFFLINE`. – Thom A Sep 18 '19 at 09:41
  • @larnu The error says "This file doesn't exist" – Afsan Rezaei Sep 18 '19 at 09:50
  • Does the service account have access to `D:`? – Thom A Sep 18 '19 at 09:51
  • @Larnu's link should work for, but if you're are after an alternative it would be to take a backup and then restore that backup using "WITH MOVE" to put the files where you want them – uberbloke Sep 18 '19 at 09:58
  • you can follow this [Tutorials](https://www.sqlshack.com/move-sql-database-files-mdf-ldf-another-location/) for do that – Mahdi Rahimi Sep 18 '19 at 10:00
  • @larnu I don't know, How can I check this? – Afsan Rezaei Sep 18 '19 at 10:01
  • Right click the directory in File Explorer and see what permissions the service account has for that Directory. If you're not familiar with user permissions, you might be better talking to your Network Administrator. – Thom A Sep 18 '19 at 10:05
  • @MahdiRahimi thanks for sending the link. Finally,I did it ! – Afsan Rezaei Sep 18 '19 at 10:47

0 Answers0