Rob-350472 (9/13/2012)

---

I'm looking to move the database file location from one drive to another on my machine for about 20-30 databases.

Besides a copy/paste of the files from location A to location B and then creating all the databases again and deleting the existing ones, is there any other option?

The file names and database names would be identical, just the path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLEXP\MSSQL\DATA
becomes
R:\SQL Monthly Snapshots

I'm guessing I might have to edit the permissions of that folder too to allow Management Studio to write to it?

SELECT 'ALTER DATABASE ' + QUOTENAME(DB_NAME(database_id)) + 
' MODIFY FILE(NAME=' + name + ', FILENAME=N''' + 
REPLACE(physical_name, 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLEXP\MSSQL\DATA\', 
'R:\SQL Monthly Snapshots\') + ''')'
FROM sys.master_files
WHERE database_id > 4

Rob-350472 (9/13/2012)

---

Need to wait till I can actually close Management Studio in the first place (and SQLserver process) to move the files first of all though!

Thanks :)

Rob-350472 (9/13/2012)

---

Ah I see, run the command, right click on DB in question, tasks --> take offline.

Copy files in explorer from source to destination

right click --> 'bring online' (at a guess!)

Confirm it works then delete the source files.

Even better!

Rob-350472 (9/13/2012)

---

perhaps I should think of going:

shrink --> backup routine --> move...

John Mitchell-245523 (9/13/2012)

---

I would recommend backing up the databases first, in case the worst happens, which it has done for me before. Given that you have taken a backup, you may want to consider doing the move by RESTOREing WITH MOVE,