I set this up to be modular and reusable, because I like reusing things. This doesn’t actually move your system databases. It just outputs a change script and a revert script. You still have to run the generated script, then log on to your server and restart the services yourself. In my example, I’m moving TempDB from C (ew) onto better storage.
declare @dbname sysname,
@oldpath varchar(255),
@newpath varchar(255)
set @dbname='tempdb'
set @oldpath='C:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\DATA' --no trailing slash
set @newpath='H:\HappyStorage' --no trailing slash
/* Generates your change script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + replace(physical_name,@oldpath,@newpath) + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);
/* Generates a revert script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + physical_name + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);
/* Tells you where the files are now */
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);
GO
Sample output would include, in the top window:
alter database tempdb modify file (name=tempdev, filename = 'H:\HappyStorage\Data\tempdb.mdf');
alter database tempdb modify file (name=templog, filename = 'H:\HappyStorage\Log\templog.ldf');
Copy and paste that into a new window, and run it if you’re sure it’s right.
You can, of course, use this for MSDB or any other database you want to move.
