TSQL - How to move all database files to new location, script to move files.

Post date: Jan 23, 2014 9:41:14 AM

If you want to move the physical files of your database you will need to follow a few steps.

Don't trust this script, always review any scripts you find online.

If you are moving files for multiple databases simply adjust the WHERE clause of the script to include all of them.

1. Put the database in offline mode
2. Move or copy the logs and database physical files to the new location
   1. Yes, this need to be done manually by copying files between disks.
   2. Make sure that sql server have access to the file in the new location. Check the security rights of the file to make sure. This especially if the file was copied rather than moved.
3. Alter the script below to point the logLocation and dataLocation to the folder where your files now exist
4. Run this script which will create scripts to alter the database file location
5. Run the generated script
6. Put the database in online mode

USE master;GODECLARE @logLocation VARCHAR(4000) = 'I:\log\';DECLARE @dataLocation VARCHAR(4000) = 'J:\dbdata\';SELECT 'ALTER DATABASE '+db_name(database_id)+' MODIFY FILE ( NAME = '+name+', FILENAME = '''+case type_desc when 'log' THEN @logLocation + physicalFile when 'rows' then @datalocation + physicalFile end+''');'FROM sys.master_filescross APPLY( SELECT SUBSTRING(physical_name, LEN(physical_name) - CHARINDEX('\',REVERSE(physical_name)) + 2, CHARINDEX('\',REVERSE(physical_name)))) files(physicalFile)WHERE database_id = DB_ID(N'AdventureWorks2012');GO