Tuesday, September 13, 2011

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.

No comments:

Post a Comment