Log Shipping – NORECOVERY vs. STANDBY mode

The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

1. Determine the logical file names of the tempdb database and their current location on disk.

  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
  4: GO
  5: 

       2.   Change the location of each file by using ALTER DATABASE.

  1: USE master;
  2: GO
  3: ALTER DATABASE tempdb 
  4: MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
  5: GO
  6: ALTER DATABASE  tempdb 
  7: MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
  8: GO
  9: 

      3.   Stop and restart the instance of SQL Server.

      4.  Verify the file change.

  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
 

     5.  Delete the tempdb.mdf and templog.ldf files from their original location