Possible to alter the location of both the MDF and LDF in one go?

• jasona.work

  SSC Guru

  Points: 50083

  More actions

  July 11, 2014 at 1:16 pm

  #309892

  Hopefully this will be a theoratical question and not something I'll need to do frequently (or again!)

  Yesterday and today, I had to clean up a QA server from a mistake made in a restore operation. We use a third-party backup application, and when the backup admin, at the request of the more Dev oriented DBA, ran a restore, it moved all the DB files to a new location. Thankfully, this all happened in QA (and while I was on vacation,) so other then tying up my last two days, no harm done.

  But, to the question!

  To move a DBs file(s) to a new location, I used the following:

  ALTER DATABASE [NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE;

  ALTER DATABASE [NAME]

  MODIFY FILE ( NAME = NAME, FILENAME =

  "D:\Data\NAME.mdf")

  ALTER DATABASE [NAME]

  MODIFY FILE ( NAME = NAME_log, FILENAME =

  "L:\Logs\NAME_log.ldf")

  The question is, could I have combined the two ALTER DATABASE into one statement, maybe something like:

  ALTER DATABASE [NAME]

  MODIFY FILE ( NAME = NAME, FILENAME =

  "D:\Data\NAME.mdf"),

  FILE ( NAME = NAME_log, FILENAME =

  "L:\Logs\NAME_log.ldf")

  (Yes, I know, I've likely got the syntax wrong if it is possible)

  I can think of a couple possible reasons why that might not work, but if it would've worked, it would've been a lot less copy / pasting (it's a Sharepoint server this happened to, with LOTS of content DBs...)

  Thanks,

  Jason

• Perry Whittle

  SSC Guru

  Points: 233901

  More actions

  July 15, 2014 at 8:04 am

  #1729468

  It's one alter command per file when modfying, never been a bother for me

  you're only typing multiple alter statements, not as if it's a hardship 😉

  ALTER DATABASE somedb MODIFY FILE

  What you could have done though is this

  ALTER DATABASE [NAME]

  MODIFY FILE ( NAME = NAME, FILENAME =

  "D:\Data\NAME.mdf")

  ALTER DATABASE [NAME]

  MODIFY FILE ( NAME = NAME_log, FILENAME =

  "L:\Logs\NAME_log.ldf")

  Then when you're ready issue

  ALTER DATABASE [NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE;

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉