Moving System Databases files to new location (SQL Server 2008 R2)

Moving tempdb files to new location: (SQL Server 2008 R2)

·         Connect to server.

·         Run the query ‘select * from sysfiles’.

·         Get the current mdf/ldf file locations.

·         Run the command:

                      ALTER DATABASE tempdb

                      MODIFY FILE ( NAME ='tempdev',

                      FILENAME = 'New Location')

                      ALTER DATABASE tempdb

                      MODIFY FILE ( NAME ='templog',

                      FILENAME = ' New Location')

·         While moving 'tempdb' do not forget to mention tempdb file names also...

Eg: alter database tempdb modify file (name = 'tempdev',

filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERNEW\MSSQL\DATA\Tempdb\tempdev.mdf')

alter database tempdb modify file (name ='templog',

filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERNEW\MSSQL\DATA\Tempdb\templog.ldf')

·         Restart SQL Server Services.

·         Connect back to server and run again above query to find mdf/ldf file locations.

·         Also verify in the folder weather files are moved to new locations.

·         For moving 'msdb','ReportServer','reportservertempdb' and 'model' databases we need to move files physically after using  above given command and restart sql server services.

·         SQL Server will start even if the files of ‘msdb’, 'ReportServer','reportservertempdb' database are not moved fully. But those DB's will not be accessible.
Note: SQL Server will not start if master or model database files are not moved correctly.

Original Page and moving master db to new location.