Sql Server Moving System Databases

Today i am going to discuss  how can we move the system database files ( data file - mdf  & log file  ndf ) to a different location with in the same machine. There will be some exceptions which will be discussed at the bottom

Different scenarios where we need to move the files are :

1) Planned relocation - ex:  you installed it on c drive & realized that those files should not be there on OS drive :)
2) Relocation for scheduled disk maintainance
3) Failure recovery - hardware recovery
  could be many more ..

Steps :

1) Identify the files that are used by a  database & you want to move  :

2) Create the files in the new location  using command for each file :
   use master
go
alter database tempdb modify file (name='tempdev', filename='c:\tempDB.MDF', size = 1mb)
go

3) Stop the sql server engine either from sql server management studio (or)  from sql server management console ( sqlservermanager10.msc   in run command  for sql server 2008 )

4)  copy the files from old location to new location.

5) restart the sql server .  From the restart time the sql server will use the files from the new location.

6) To verify if sql server  is using the files from  new location , you can run the query to find the location :
    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

Exceptions :
1)  For Tempdb , we need not restore the files to new location as for every sql server restart tempdb files will be initialized . Even if we don't restore the files, still sql server will create new files in the new location.
2)  Resource database can't be moved
 3) For master database , we have to change the start up parameter to point to new location
    sql server configuration manager --> sql server services --> right click --> properties --> advanced --> startup
 This has to be done before we restart the service.

4) for msdb database, make sure you check the service broker is enabled , if database mail is configured.
Database Mail uses msdb database.

SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';

If there is a Hardware failure , then moving files follows a seperate logic :

1) Stop the sql server instance

2) Start the instance in master only recovery mode by typing the cmd from command prompt :
      

NET START MSSQLSERVER /f /T3608   ( for default instance )     
    NET START MSSQL$instancename /f /T3608  ( for named instance )

3) for each file, run the alter database command as mentioned above .

4) Stop the instance  (  NET STOP MSSQLSERVER ) 

5) move the files to new location 

6) start the instance  ( NET START MSSQLSERVER )

7) check if service is using new location or not as mentioned above.

Happy Reading.. Hope this helps..